Challenges with JSON output from Roxie query
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
Expected output:
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"
}
]
}
- benhastings
- Posts: 6
- Joined: Fri Aug 16, 2013 7:20 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:
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.
- 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.
- gsmith
- Posts: 278
- Joined: Thu May 12, 2011 9: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.
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.
- benhastings
- Posts: 6
- Joined: Fri Aug 16, 2013 7:20 pm
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):
And then to call:
- 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"]);
- gsmith
- Posts: 278
- Joined: Thu May 12, 2011 9:40 am
FYI, I've created two issues to track this:
https://track.hpccsystems.com/browse/HPCC-9874
https://track.hpccsystems.com/browse/HPCC-9875
https://track.hpccsystems.com/browse/HPCC-9874
https://track.hpccsystems.com/browse/HPCC-9875
- anthony.fishbeck
- Posts: 57
- Joined: Wed Jan 30, 2013 10:18 pm
5 posts
• Page 1 of 1
Who is online
Users browsing this forum: No registered users and 1 guest