JSON_Name has multiple levels ...need to break it down to each group

Question

Hi All,

Thanks for your time - I pull data from an URL using the download and JSON parse...I get the JSON file structure in such a way that it includes kind of parent and child members

for example

product id,  product name-> These 2 belong to 1 group then the 2nd group is like applications like application Id, name, which will show for that product then the 3rd group is product related information who is owner etc... I have to

like an example

JSON_Name
productApplications.0.productIdentifier

productApplications.0.applications.0.applicationIdentifier

productApplications.20.productMembers.0.workerFullName

after the JSON parse if i know the number to columns to split using text columns i can specify like 3 or 4 but in this case how do specify the columns

then use the cross tab so that i get all the columns - I have attached some dataset ...any adivse please

JSON_Name.xlsx

BrandonB · Answer

Hi @Raj_007

You will want to follow this pattern:

JSON Parsing.yxmd

Raj_007 · Answer

Hi , I have attached the sample of the column

BrandonB · Answer

@Raj_007 normally you will use a text to columns tool with a period delimiter to split JSON Name into the number of columns needed. Then when performing the crosstab you will group by the numeric column which serves as an ID, use the last column as the column headers and JSON Valuestring as the values.

As @ArtApa mentioned, it is easiest if you can provide the output so others on the community can mock up a workflow for you.