Creating JSON

Question

Hi

I understand the concept of transposing values into name and value to use the json builder tool, but how do you create child elements like you would in an xml generation tool process so that parent shows as per the highlighted section and the bits under it are shown as child elements?  (edited as I realised I hadn't finished my sentence!)

I am sure it is something obvious, but your help would nevertheless be appreciated.

Thanks

danilang · Answer

Hi @Manoj_k

Update:  I've been playing around with the JSON build tool and it does seem to be able to build arbitrary json files that are correctly formatted.  The caveat is that you have to use the same structure as the JSON Parse tool outputs.

The structure of the resulting JSON files is completely determined by the JSON_Name field.  The root element is menu and it has three elements id, value both string attributes and popup which is a collection with three elements menutitem.0, menuitem.1 and menuitem.2. The part that defines popup as a collection is the inclusion of the numbered items 0,1, and 2.  There is a second collection level in element menu.popup.menuitem.2.subMenu.

So if you can manipulate your large flat data table into this structure, you can send it through the JSON Build tool.  This issue is getting the correct naming convention in JSON_Name field with each additional nesting level represented by a new "." including collection iteration numbers as required.  This is definitely possible using a Multi-Row tool with grouping for each level starting at the lowest with careful sorting between them.  I feel that using this technique will result in a workflow that is just as complex as the formula/summary technique listed in my original post to build the json as a string.  Both of them have deal with each level individually so the number of tools used should be very similar.

Dan

apathetichell · Answer

As @danilang points out - for collection - you have to nest the json of the collections inside the fields to further nest in your outer json level.  have it follow the

[

{

[

{

},

{

}

]},{}...,{}] structure...

danilang · Answer

Hi @ck2024

Neither the JSON build tool, nor the JSON Create CReW macro can build JSON files with arbitrary complexity.(Not true but not easy.  See my following post)  The reason for this is that Alteryx tables are flat and JSON files are hierarchical.  Once you get more than one level deep, you need to be able to specify which fields in the table correspond to which level in the JSON and also how to group them into coherent collections, if used.

For any complex JSON, I always roll my own.  Use formula tools to build elements in the proper format and then summarize tools to build up collections.  In the summarize tools, use the Start Separator and End fields to add the proper opening, intermediate and closing charaters "{", "[", "]" etc.

Dan