JSON Parse Tool: The better way!

[mustafa]

I've been dealing with JSON since day one, and to be honest it isn't the best experience I've had.

Converting a hierarchical schema into a tabular one is't a straight forward process, but doing that everyday the old way is time and processing consuming.

What I'm proposing is a tool that can read JSON as input, then display a structural skeleton for the user, or the user can provide such skeleton for the tool, say let's say we have the following input:

{"menu": [{
  "id": "File",
  "popup": {
    "menuitem": [
      {"value": "New", "action": "CreateNewDoc", "icons": ["SAVE", "FLOPPY"]},
      {"value": "Open", "action": "OpenDoc"},
      {"value": "Close", "action": "CloseDoc", "conditions": [11,8,4]}
    ]
  }
},
{
  "id": "Edit",
  "popup": {
    "menuitem": [
      {"value": "Cut", "action": "TextCut", icons: ["CUT", "SCISSORS"]},
      {"value": "Copy", "action": "OpenDoc"},
      {"value": "Paste", "action": "CloseDoc", "conditions": [5,17]}
    ]
  }
}
],
"error": false
}

now to parse this into a table of menuitems we need to use:

• JSON Parse: convert JSON into one long key:value table
• TextToColumns: split key into multiple columns
• Filter: make sure we only get one level from the tree
• CrossTab: Convert it back into a column based key values.

All of this will give us the most primitive table we can have as:

2	5	action	conditions	icons	value
0	0	CreateNewDoc		SAVE,FLOPPY	New
0	1	OpenDoc			Open
0	2	CloseDoc	11,8,4		Close
1	0	TextCut		CUT,SCISSORS	Cut
1	1	OpenDoc			Copy
1	2	CloseDoc	5,17		Paste

and now if we want to have the parent menu id along side with the menuitems, we will do that again as:

• Filter: for parent values only
• CrossTab: for parent values into a table
• Join: to join Parents with Sub items and add the Parent.Id

Now all of this is done with Concatenating of child items, as cross tab will allow us to only do Concat/First/Last for items with the same grouping values.

And now if we want to process children, count them, or extract their data into another table, we have to add more Filters, more CrossTab and more Joining to get parent IDs for future linking.

So what's I'm proposing?

I'm thinking of a Tool with an interface that give me the ability to choose:

• Target Branch: which is the main table to be extracted from the branches, in this case it would be menu->popup->menuitem.
• Parent Values: what values to be appended from parents of the previous table, just like menu->Id and others if exist.
• Children Data types: selecting the proper and expected data type for children instead of using strings or the existing different columns way.
• Children Arrays Process: what to do with children branches? either stopping their process and return them as is (Stringify), exclude or do other process like count.

the tool may extract the structure or let the user input such config as the following:

Or Input the Structure as a YAML formatted config or any other way.

This will allow the user to have a quick native tool that does what he wants as it should, and user can use it as much as he want for children and nested values. you just Stringify and repeat and only parse what you need every time.

I hope you consider this for me to replace tens of macros and tools into single tools such so.

Thanks for your help and time and all the best!

[JoeS]

This is a great suggestion and would be lovely to see, however, I think due to the inherent nature of JSON and it's flexibility it would be incredible difficult to achieve.

One of the first parts that is difficult (to a degree) is that the JSON itself needs to be fully ingested in order to build the UI of the tool. Which isn't the standard Alteryx way of using metadata for tool configuration.

The next (and main) part is due to the complete flexibility of JSON, your nested file may work well for that, but another system, use case may be entirely different.

YAML however is a great suggestion. 

This is also a reply from my development background, I am no longer there, so feel I can now switch heads and say I agree.

It would be lovely to see something like this!

As a side note, it'd be good to see this suggestion in the ideas part of the community here:

https://community.alteryx.com/t5/Alteryx-Designer-Ideas/idb-p/product-ideas

It'd defo get a star from me!

[mustafa]

thanks @JoeS for your support and replay.

Post is moved now and you can star it 🌟

for the structure, you can always fallback into serialization of complex branches for the user to repeat the process as much as it needs.

[pringleb]

It would be helpful if this is just used by the Alteryx Designer application when the developer is making the workflow.  It would be great for it to have a tool that the developer can use to determine how to parse the file and then have Alteryx add the appropriate tools for when the workflow runs.