Last time, we worked on the retriever that takes a GQL query and returns the result set. This time, let's take a look at the data format of the result set.

For the GQL query below

SELECT

  number[Number]

  , short_description [Title]

  , dv_state [State]

  , caller_id.email [Caller Email]

  , caller_id.manager.email [Manager Email]

  , dv_cmdb_ci [CI]

  , cmdb_ci

FROM incident

WHERE active=truepriority<2EQORDERBYpriorityORDERBYDESCnumber

LIMIT 5,10

the retriever returns the 10 records in JSON shown below (from Incident demo data):

{"records":[{"CI":"EXCH-SD-05","Caller Email":"jerrod.bennett@example.com","Manager Email":"","Number":"INC0000050","State":"Active","Title":"Can't access Exchange server - is it down?","cmdb_ci":"281190e3c0a8000b003f593aa3f20ca6"},{"CI":"","Caller Email":"employee@example.com","Manager Email":"","Number":"INC0000031","State":"Active","Title":"When can we get off Remedy? UI is killing us","cmdb_ci":""},{"CI":"IBM-T42-DLG","Caller Email":"don.goodliffe@example.com","Manager Email":"","Number":"INC0000025","State":"Active","Title":"I need more memory","cmdb_ci":"469facd7a9fe1981015830c43428ca2d"},{"CI":"","Caller Email":"taylor.vreeland@example.com","Manager Email":"","Number":"INC0000018","State":"Active","Title":"Sales forecast spreadsheet is READ ONLY","cmdb_ci":""},{"CI":"","Caller Email":"employee@example.com","Manager Email":"","Number":"INC0000017","State":"Awaiting User Info","Title":"How do I create a sub-folder","cmdb_ci":""},{"CI":"","Caller Email":"bow.ruggeri@example.com","Manager Email":"","Number":"INC0000016","State":"Active","Title":"Rain is leaking on main DNS Server","cmdb_ci":""},{"CI":"Saints and Sinners Bingo","Caller Email":"fred.luddy@example.com","Manager Email":"","Number":"INC0000015","State":"Active","Title":"I can't launch my VPN client since the last software update","cmdb_ci":"46c7318aa9fe198100c76003f0bc82e9"},{"CI":"","Caller Email":"employee@example.com","Manager Email":"","Number":"INC0000007","State":"Awaiting User Info","Title":"Need access to sales DB for the West","cmdb_ci":""},{"CI":"","Caller Email":"employee@example.com","Manager Email":"","Number":"INC0000003","State":"Active","Title":"Wireless access is down in my area","cmdb_ci":""},{"CI":"FileServerFloor2","Caller Email":"","Manager Email":"","Number":"INC0000002","State":"Awaiting Problem","Title":"Unable to get to network file shares","cmdb_ci":"b0c25d1bc0a800090168be1bfcdcd759"}]}

There are two things to note in this JSON output:

1. When we get a result set from a SQL query, the columns are ordered. That is, if a SQL statement's SELECT clause lists column_1, column_2, etc., we expect the result set to show those columns in the same order. In the above JSON output, the columns are listed as object properties, which are not ordered by definition in JavaScript.
2. I like working with JSON primarily for two reasons: a) its format is native to JavaScript so I can readily work with it in JavaScript; b) it's not as verbose as XML, in most cases. What we see above, however, is verbose because the column headings (object property keys) are repeated for every row. As the row count increases, there will be more redundant information.

To address both of these points, I'd like to use JavaScript arrays, instead of objects, to represent each row, calling it JSAN (JavaScript Array Notation). This is still based on JSON, but instead of returning object key-value pairs, we'll use an array of values, which may be of any type and are ordered by definition in JavaScript. The column headings will be in its own array, separate from the data records. The savings in bytes, even just for 10 records, is pronounced in the same output below, in JSAN (1,282 vs. 1,909 characters):

{"labels":["Number","Title","State","Caller Email","Manager Email","CI","cmdb_ci"], "records":[["INC0000050","Can't access Exchange server - is it down?","Active","jerrod.bennett@example.com","","EXCH-SD-05","281190e3c0a8000b003f593aa3f20ca6"],["INC0000031","When can we get off Remedy? UI is killing us","Active","employee@example.com","","",""],["INC0000025","I need more memory","Active","don.goodliffe@example.com","","IBM-T42-DLG","469facd7a9fe1981015830c43428ca2d"],["INC0000018","Sales forecast spreadsheet is READ ONLY","Active","taylor.vreeland@example.com","","",""],["INC0000017","How do I create a sub-folder","Awaiting User Info","employee@example.com","","",""],["INC0000016","Rain is leaking on main DNS Server","Active","bow.ruggeri@example.com","","",""],["INC0000015","I can't launch my VPN client since the last software update","Active","fred.luddy@example.com","","Saints and Sinners Bingo","46c7318aa9fe198100c76003f0bc82e9"],["INC0000007","Need access to sales DB for the West","Awaiting User Info","employee@example.com","","",""],["INC0000003","Wireless access is down in my area","Active","employee@example.com","","",""],["INC0000002","Unable to get to network file shares","Awaiting Problem","","","FileServerFloor2","b0c25d1bc0a800090168be1bfcdcd759"]]}

JSAN is different than CSV (comma-separated values) in that JSAN is still based on JSON, so no special processing (encoding/decoding) is necessary; we can still use the same JSON encode/decode methods, which are now ubiquitous in both client (JSON.stringify(), JSON.parse()) and server-side ( JSON.encode(), JSON.decode()) JavaScript. JSAN may also contain metadata to provide additional information about the data. For example, we can include not just column headings, but field names as well as data types. Since the data elements (rows and columns) are ordered, it now becomes possible in some cases to generate a hash, such as MD5, or a CRC32 checksum based on the encoded data; this may be useful if an additional layer of data integrity is required, such as for critical systems in life sciences, aerospace, military, or nuclear sectors (this may not work if a column contains unordered data like objects since they can be encoded in any arbitrary order).

JSAN would look like this for 2 records of 3 columns (although arrays are 0-based, I'm showing the first column as "column_1" below):

{"fields":["column_1_field","column_2_field","column_3_field"]

,"labels":["column_1_heading","column_2_heading","column_3_heading"],

"records":[["row_1_column_1","row_1_column_2","row_1_column_3"]

,["row_2_column_1","row_2_column_2","row_2_column_3"]]}

To incorporate this, let's modify the GQL class in the Script Include from last time slightly as below (only the changed sections are shown):

GQL.prototype = {

  initialize: function(format) {

      this.format = format || 'JSAN';   // { JSAN | JSON }

  },

};

GQL.prototype.query = function(gql, format) {

  format = format || this.format;

  var isJSON = /JSON/i.test(format);

  return { fields: fields, labels: labels, records: records };

  function getRow() {

      var row = isJSON ? {} : [];

      for (var i = 0; i < fields.length; i++) {

          var field = fields[i], isDV = /(|.)dv_/.test(field);

          var value = isDV ? gr.getDisplayValue(field.replace('dv_', '')) || '' : gr.getElement(field).toString();

          isJSON ? row[labels[i]] = value : row.push(value);   // default to JSAN

      }

      return row;

  }

};

Line 3 allows us to optionally set the default format for the instance at the time of instantiation; if not set, it defaults to JSAN.

Line 9 allows us to optionally set the format at the time of calling the query() method; if not set, it defaults to the instance default.

Line 12 adds the additional metadata (fields and labels) to the returned data.

Line 19 adds to the row either an object key-value pair if JSON or an array element if JSAN.

Next time, we'll look at the processor and see how the returned data from the GQL class are consumed; we'll also see how JSAN makes it easy to encode the result sets into HTML and CSV formats.

Please feel free to connect, follow, post feedback / questions / comments, share, like, bookmark, endorse.

John Chun, PhD PMP