Thu Jul 18, 2019 11:14 am
Login Register Lost Password? Contact Us


Trouble loading JSON Dataset

Questions around writing code and queries

Fri May 17, 2019 6:56 pm Change Time Zone

Hi there,

This is the first time I'm working with json. I am having troubles trying to load a json dataset file. I uploaded a file into the ECL Watch and now I am trying to load the file into a dataset to clean the data layout and then perform some queries using it.

The json file does not have a root, so it is one register per line. Something like the example below:
Code: Select all
{"id":"QXAEGFB4oI", "name":"Emerald Chinese Restaurant", "attributes":{"RestaurantsReservations":"True", "GoodForMeal":"{'dessert': False, 'latenight': False}}, "hours":{"Monday":"9:0-0:0","Tuesday":"9:0-0:0"}


I read the documentation and I didn't just really did not understand how I could work with the datasets inside other datasets, like the attributes and the hours that is inside this file.
The 'attributes' field is an array of strings, that each array element is an attribute (name, value), and the 'hours' field is an array of strings of business hours (day, hours).

I was thinking about using the XPATH, but there are different attributes names depending of the record.
I'm just asking for a direction of how to proceed with this kind of data.

Thank you,
André.
andre.martins
 
Posts: 2
Joined: Thu Apr 18, 2019 2:17 pm

Fri May 17, 2019 8:29 pm Change Time Zone

André,

Nested child datasets are covered in the Advanced ECL (Part 1) online course, and XML/JSON in Advanced ECL (Part 2). I think you'll find those helpful (http://learn.lexisnexis.com/hpcc).

HTH,

Richard
rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1462
Joined: Wed Oct 26, 2011 7:40 pm

Mon May 20, 2019 5:59 pm Change Time Zone

Richard,

I already did and revisited these courses looking for a way to solve this problem. But no success.

I'm stuck because I have to load the childs datasets as name/value, check the following records.
Example:
EXPORT AttributeRecord := RECORD
STRING name;
STRING value;
END;

EXPORT BusinessRecord := RECORD
STRING business_id {XPATH('business_id')};
STRING name {XPATH('name')};
DATASET(AttributeRecord) Attributes {XPATH('attributes')};
END;


I did'nt find a way to work with this attributes that has N different types, since XPATH will only take the value of one attribute.
andre.martins
 
Posts: 2
Joined: Thu Apr 18, 2019 2:17 pm

Wed May 22, 2019 1:51 pm Change Time Zone

andre,

Name/value for the attributes won't work because some of the "values" are themselves nested child datasets. I suggest that you either need to fully define all the possible nested child datasets in the RECORD structure, or abandon the effort to define it as a JSON DATASET and simply treat the JSON data as a single STRING field that you can then PARSE to extract the specific information you need.

HTH,

Richard
rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1462
Joined: Wed Oct 26, 2011 7:40 pm


Return to Programming

Who is online

Users browsing this forum: No registered users and 0 guests

cron