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

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

TestData.xlsx

Datasets

Parse

Data Investigation

Accepted answers

AncientPandaman

@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

Screenshot 2025-09-08 105922.png

example.yxmd

All comments

Raj_007

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

AncientPandaman

@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

Screenshot 2025-09-08 105922.png

example.yxmd

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

JSON_Flatten_Test_Workflow.yxmd

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703