Easiest way to parse (deep)JSON array into records......

Discussion created by ak_crux on Aug 20, 2018
Latest reply on Dec 22, 2018 by fabianf

I want to start this discussion by thanking all the speakers at DevCon2018 who spoke on JSON (and related topics such as APIs).  Going into DevCon I had only heard these terms before - never seen them in action. Thanks to the great lineup (Bob Bowers, Chris Ippolite and his team to name a few), I now am able to retrieve JSON from all sorts of API's which has opened up a whole new world of possibilities.


My first few JSON's were very flat with just a root level.  Once you get the hang of it, these are easy to parse into records using the JSONGetElement function (count the keys then run a script loop).


The real world application is far more complex.....


I have encountered a JSON that has an array at the 3rd level down from root which has varying numbers of keys at each repetition (sometimes none - but there is still a key, it just has a null value). This deep data this happens to be some of the most important info in the JSON.  The way I planned to retrieve this involved the following:


  1. Viewing the JSON data in something like the JSON Awesome Google Chrome plugin (really is awesome) to get all of the Key Names.
  2. Breaking this up into series of nested loops to count the number of keys at each level, and then loop through those levels to get the data.


Before starting I wanted to see if the method described above is really the best way to deal with parsing JSON into records, or if there was some other trick to make this easier.  For example, is there a way to get the entire set of Key Names and Quantities in one shot as a return delimited list?  This data could then be manipulated and fed into the JSONGetElement function at the appropriate time of a loop/script to only get the needed data.