JSON with different groups...best way to get this flattened

[Raj_007]

Hi, Thanks for your time. I have this dataset in JSON - using the JSON parse tool to get the data. But the groups inside the JSON are not consistent.. what is the best way to get these groups from JSON so that it shown as flattened...
There is anAPI Id for which there are 2 sets of revisions which again includes some specifications in each revision - need some help breaking these groups for each API Id

for ex: after flatten

API Id123        revision 1 related fields       specification1 related fields

API Id123        revision2  related fields       specification2 related fields 

API Id 456

API Id 456

[Raj_007]

Hi, any guidace...pls let me know if this is not clear

[PangHC · Accepted solution]

@Raj_007 
1. using multi-row to identify the api.
2. using text-column to identify the specification etc.

3. using filter and crosstab differently to ease manage.

4. using join tool to merge the data back

additional: good to use dynamic rename to add back the "revision." as prefix for more clear vision

[abacon]

@Raj_007 Here is a more dynamic and simpler way to get the data broken out. Whenever I make an api call, this is the framework I use.

1. Split the columns out

2. Group on the needed records

3. Cross tab into a table.

4. beautify the output.

This will work with most api calls you make, just needs to be customized a little more to each specific one.

Bacon

[Raj_007]

Hi PangHC, Thank you so much for your time. This is really helpful..

[Raj_007]

Hi Abacon, Thanks a lot for your time. This thread helped me understand on should i break this into individual groups or get the grouping done using the 1 dataset..

Really appreciate your time