Fri Aug 17, 2018 11:07 pm
Login Register Lost Password? Contact Us


Challenges with JSON output from Roxie query

Topics specific to using ECL from a Data Analyst standpoint

Fri Aug 16, 2013 7:48 pm Change Time Zone

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"
                        }
                     ]
}
benhastings
 
Posts: 6
Joined: Fri Aug 16, 2013 7:20 pm

Sun Aug 18, 2013 1:18 pm Change Time Zone

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.
gsmith
 
Posts: 275
Joined: Thu May 12, 2011 9:40 am

Mon Aug 19, 2013 1:40 am Change Time Zone

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.
benhastings
 
Posts: 6
Joined: Fri Aug 16, 2013 7:20 pm

Mon Aug 19, 2013 7:04 am Change Time Zone

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"]);
gsmith
 
Posts: 275
Joined: Thu May 12, 2011 9:40 am

Mon Aug 19, 2013 2:17 pm Change Time Zone

anthony.fishbeck
 
Posts: 47
Joined: Wed Jan 30, 2013 10:18 pm


Return to ECL for Analysts

Who is online

Users browsing this forum: No registered users and 1 guest

cron