Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Parsing a nested JSON file into multiple tables in SQL

vidhya
6 - Meteoroid

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,

11 REPLIES 11
jwalder
10 - Fireball

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
6 - Meteoroid

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

jwalder
10 - Fireball

I don't have the input for that workflow, but using your example.txt from above, see attached as example.

vidhya
6 - Meteoroid

Hi,

 

Thanks again, But i get the same output as yours with my workflow as well using example.txt!

Somehow tats not what is expected rite!

 

So here goes the input, and the expected output. This is what am trying to achieve with alteryx! 

Somehow branch out and make different tables from the Same JSON based on a particular JSON column! 

 

 

 

 

jwalder
10 - Fireball

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.

vidhya
6 - Meteoroid

yea the text to columns is super helpful! But the only issue here is i have around 400 + tables with different structures and one million rows overall! 

Is there any other " dynamic way" ( as you suggested) to automatically take the JSON value as the table name? and put it in the respective tables  as in align the nodes and the structure?

 

Thank you again:D 

jwalder
10 - Fireball

Typically I see JSON structures more like this.

vidhya
6 - Meteoroid

Thats nice! But again it does have "Contains" . Imagine including 400 + table names:( 

 

I am somehow thinking if the alteryx can by itself populate the table name?

Because the structure seems standard the first row would be table name and the rows after that the column names! 

 

Do you think it would be possible?

 

vidhya
6 - Meteoroid

Also as per this Workflow, Few rows are missing in the output:( i guess they get removed somewhere! 

 

Labels