JSON Parse - to cross tab format

[Masond3]

Hi Team, 

Hope all is well. 

I have some Json data, that i would like to parse and and get into a readable format ( as shown in the desired outcome) 

Unfortunately i don't know all the header names, but hoping it can be dynamic enough to pick up and align the data accordingly. 

Input 

JSon_Data	RecordID
{"ADDRESS_LIST":[{"PRIMARY_ADDR_CITY":"Test  SAONE","PRIMARY_ADDR_COUNTRY":"FR","PRIMARY_ADDR_LINE1":"123 Test Street  Desmoulins","PRIMARY_ADDR_LINE2":"EMEA","PRIMARY_ADDR_POSTAL_CODE":"69400","PRIMARY_ADDR_TYPE":"HOME"}],"EMAIL_LIST":[{"EMAIL	1
{"ADDRESS_LIST":[{"PRIMARY_ADDR_CITY":"Test SAONE","PRIMARY_ADDR_COUNTRY":"FR","PRIMARY_ADDR_LINE1":"123 Test Avenue Desmoulins","PRIMARY_ADDR_LINE2":"EMEA","PRIMARY_ADDR_POSTAL_CODE":"69400","PRIMARY_ADDR_TYPE":"HOME"}],"EMAIL_LIST":[{"EMAIL	2

Desired outcome 

Recordid	PRIMARY_ADDR_CITY	PRIMARY_ADDR_COUNTRY	PRIMARY_ADDR_LINE1	PRIMARY_ADDR_LINE2	PRIMARY_ADDR_POSTAL_CODE
1	Test SAONE	FR	123 Test Street	EMEA	69400
2	Test SAONE	FR	123 Test Avenue	EMEA	69400

Looking forward to your assistance 

Many thanks 

Masond3

[OllieClarke]

@Masond3 This is gonna get frustrating for everyone if you drip-feed json snippets and try and get help to parse them...

The approach I showed above still works here, you just need to filter the data and deal with the different structures of it independently; then pull them back together.

The phone numbers need an extra transformation step, I've shown one approach which is pretty dynamic (assuming all this info would be in PHONE_LIST), but it might be easier to take a less dynamic approach if this is the only exception that needs cleaning

Hopefully the approach shown here is enough for you to apply to the data in its entirety...

[Masond3]

I believe I would need to differentiate between the two by using the type, even though they share the same JSON name.

[Masond3]

@OllieClarkeI agree, it's frustrating for me as well. Without seeing the full JSON response, it's difficult to formulate a precise question. Regarding your solution, does "other structure types" mean I would need to compile that list and include it in the input file? If so, that's not feasible when dealing with thousands of rows. 

[OllieClarke · Accepted solution]

@Masond3 I meant that the approach I've taken scales:

1. Use the Json Parse Tool
2. Text-to-Columns to break apart values by '.'
3. Filter to rows of the same structure
4. Identify the header, value and group by columns
5. Set the Data types of the group by columns (so row ids are number)
6. Cross-Tab (or otherwise transform)
7. Combine with the other sections

Sometimes it's easier to do step 3 before step 2, but any workflow parsing json is going to be this set of steps

[Bhavyapaliwal20]

Hii

I noticed one thing in you flow why are you use this long process to doing this. i attached one workflow try this i think this is good for you solution.