Challenges with JSON output from Roxie query

Fri Aug 16, 2013 7:48 pm

our cluster isn't open to the world, so I won't post an endpoint, but I'll describe the problem:

We are deploying queries to Roxie to be consumed by visualization libraries and the like by our associates. We are going to build an interface to facilitate interaction with these summary datasets. For utility and efficiency, we are going to use the JSON output. However, I've run into a few challenges with the output as it exists. (EXAMPLE JSON BELOW)

they are:
1. UNSIGNEDx fields are being converted to character strings with quotes in the output
2. There are extra levels of hierarchy that appear extraneous in the output causing JSON.parse(<json_string>) to create an invalid object.

Please advise if there are changes I can make to alter the output or if there is more information required.

Returned from Roxie

Code: Select all: { "rumsearchntileResponse": { "Results": { "Result_1": { "Row": [ { "cpc": "SD", "pagecat": "Content Delivery", "zone": "APAC", "country": "AU", "ym": "201307", "wk": "1372550400", "dy": "1372651200", "hr": "1372651200", "minute": "1372651200", "ntile": 5, "metricname": "pgi", "value": 802.0, "__internal_fpos__": "0" }, { "cpc": "SD", "pagecat": "Content Delivery", "zone": "APAC", "country": "AU", "ym": "201307", "wk": "1372550400", "dy": "1372651200", "hr": "1372651200", "minute": "1372651200", "ntile": 5, "metricname": "pgl", "value": 3284.0, "__internal_fpos__": "0" }, { "cpc": "SD", "pagecat": "Content Delivery", "zone": "APAC", "country": "AU", "ym": "201307", "wk": "1372550400", "dy": "1372651200", "hr": "1372651200", "minute": "1372651200", "ntile": 5, "metricname": "ttfb", "value": 47.0, "__internal_fpos__": "0" } ] } } } }

Expected output:

Code: Select all: { "Result_1": [ { "cpc": "SD", "pagecat": "Content Delivery", "zone": "APAC", "country": "AU", "ym": 201307, "wk": 1372550400, "dy": 1372651200, "hr": 1372651200, "minute": 1372651200, "ntile": 5, "metricname": "pgi", "value": 802.0, "__internal_fpos__": "0" }, { "cpc": "SD", "pagecat": "Content Delivery", "zone": "APAC", "country": "AU", "ym": 201307, "wk": 1372550400, "dy": 1372651200, "hr": 1372651200, "minute": 1372651200, "ntile": 5, "metricname": "pgl", "value": 3284.0, "__internal_fpos__": "0" }, { "cpc": "SD", "pagecat": "Content Delivery", "zone": "APAC", "country": "AU", "ym": 201307, "wk": 1372550400, "dy": 1372651200, "hr": 1372651200, "minute": 1372651200, "ntile": 5, "metricname": "ttfb", "value": 47.0, "__internal_fpos__": "0" } ] }

Sun Aug 18, 2013 1:18 pm

I recently hit a similar issue (in my case it was parsing the XML result set from a WU - also for a mapping to a viz library). Until I parse the schema correctly and "know" that a field is supposed to be numeric I just popped this into my JavaScript:

Code: Select all: } else if (!isNaN(parseInt(item[mappings[key]]))) { retVal[key] = parseInt(item[mappings[key]]); } else { retVal[key] = item[mappings[key]]; }

In the worst case it will convert some string numerics to numerics, but with JS's auto casting, it will just get converted back to a string as needs be.

(Just a workaround),

Gordon.

Mon Aug 19, 2013 1:40 am

thank you, gsmith. That is a workaround for one half of the problem. The bigger difficulty is the overly nested nature of the JSON output. The object that is created after parsing the JSON is simply not usable.

I think this is a defect - it is technically "valid JSON" but it's not _useful_ JSON. It almost looks like it's taking the same processing as the XML output but then missing some steps.

Mon Aug 19, 2013 7:04 am

While I am not disagreeing with your comments, it is fairly easy to "flatten" the JSON structure (this code is off the top of my head):

Code: Select all: var flattenJSON = function (parent, obj, objectsToRemove) { for (key in obj) { flattenJSON(obj, obj[key], objectsToRemove); if (objectsToRemove.indexOf(key) >= 0) { // Need to test if parent already has property of the same name (and is not null). parent.key = obj[key]; delete obj[key]; } } }

And then to call:

Code: Select all: flattenJSON(null, rumsearchntileResponse.Results, ["Row"]);

Mon Aug 19, 2013 2:17 pm

FYI, I've created two issues to track this:

https://track.hpccsystems.com/browse/HPCC-9874
https://track.hpccsystems.com/browse/HPCC-9875