Data360 Analyze

I am trying to parse multiple JSON files thaat have teh following basic format:

Metadata (record)

Column Definitions  (array of column definitions)

Rows (sample attached)  (array of rows)

As per previous posts I understand that JSON support is limited for objects containing multiple structures and that I need to work around this. The primary info I am interested in is the actual data rows.

I am looking for more details on how to work around this - a working example using another JSON object could help. or more details on how to manually define the structures

Attached files

json.json

Here is a possible solution:

Firstly, you can add a new output pin to the JSON Data node called "rows"

This will have the effect of outputting the data rows elements as a set of records

some of the field names have period '.' characters - this will cause issues later on in the processing so a Transform node and Change Metadata node are used to replace the period characters with underscores.

An element id is then added to identify each element in a datarow.

A Transform node then used to construct a dummy field name that incorporates the element id 

This dummy field name is then used within a Pivot Data to Name node to transpose the 'rows_dataRow' values.

A Modify Fields node can then be used to remove any unwanted fields.

The example data flow is attached. You will require Analyze v.3.6.0 or higher to import it.

Attached files

Handling_Nested_JSON - 21 May 2020.lna

Thank you Adrian - I have created this as a library node and tested it against two scenarios - it works for both. Appreciate it