Parsing a nested JSON file into multiple tables in SQL

Hi,

We have a JSON that has so many nodes, and for a pattern, the first three rows would have items for the first table, the second five rows would be for a diff table and the list goes on for like 400 tables.

Is there a way that alteryx could separate out this JSON file and map it to each table pased on a particular field?

For eg, in my case: MOITYPE?

Thanks,

example.txt

Workflow

Output

Tips and Tricks

Parse

Transformation

Help

Accepted answers

jwalder

The reason I recommend text to columns is it gives you nested arrays as indexes. e.g. "root.0.table.0.nestedtable.0.column". There are 3 nested arrays in that structure. Anyway, using your example, if it has to be completely dynamic, it gets more interesting, but if you know your table name than a simple filter and transpose/cross-tab combo should do it. Updated attached with the input.txt below.

simplejsonparse.yxzp

jwalder

Regarding being dynamic, the attached example uses a batch macro to dynamically write each table to its own output .csv. You should be able to modify that macro to output to a table instead.

simplejsonparse.yxzp

All comments

jwalder

If you use the JSON tool, it gives you a JSON name with the "tree" separated by periods and when an array occurs, an index of the array being represented by this name/value. You can use the text-to-columns tool on the name with a period for the delimiter and then filter rows out appropriately to separate your table structures. The array indexes can be then used in the group by function of the cross tab tool and the most right JSON name field as the title and the JSON value as the value. Those index values keep their values as you go deeper in the tree so if the structure as arrays within arrays within arrays, then you have a "key" to get you the relationship to other "tables" higher in the structure.

vidhya

Thank you! based on your inputs, i did try a few things and somehow i guess am missing few more!

If possible could you modify my workflow so that i understand your solution better:) Thank you again

JSON Hierarchy.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703