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

[binuacs]

@Masond3 one way of doing this with the json parse tool

[OllieClarke]

Hey @Masond3 
This is my approach to parsing Json

After the json parse tool, you break JSON_Name into its constituent values based on '.' (this will vary depending on your json, and may even vary within your json if it's nested).

Then I rename, drop and retype fields (you sometimes need to split your data before this step, as JSON_Name2 could be a row indicator for some of your data, but a header for others)

Then cross tab, grouping by all numeric fields

Hope that helps,

Ollie

[Masond3]

@OllieClarke  hank you for your prompt response and the detailed comments embedded in the workflow. After running your query on my dataset, I noticed that some entries in the json_name field do not contain a period (".").

Example 

ADDRESS_LIST.0.PRIMARY_ADDR_CITY

ADDRESS_LIST.0.PRIMARY_ADDR_COUNTRY

GROUP_ASSOCIATION_ORG_NAME

RECORD_TYPE

if i want, GROUP_ASSOCIATION_ORG_NAME & RECORD_TYPEto be grouped together, whats the best way to achieve this ? 

Many thanks 

[OllieClarke]

Hi @Masond3 

I was working off the json you shared (which wasn't complete). This is one of those examples where we need to separate our json.

Based just on the original input and what you added above, here's how I would deal with that. This approach scales though, so once you've worked out many layers of json you have, you'd filter to each layer, cross tab it out, then join them all together at the end

Hope that helps,

Ollie

[Bhavyapaliwal20]

Please check this workflow .

 

[Masond3]

@Bhavyapaliwal20  @OllieClarke 

Thank you for providing your solutions.

While analyzing more data, I believe I have identified the problem.

Initially, I observed that the json_name field does not contain a period ("."). This observation still holds true.

However, upon further investigation, I also noticed that there can be multiple attributes for the same record ID, which often have the value "ALT" in the json_valuestring. This is indicated in json_name as ".1".

This seems to only appear for the following two types: Email_Address and Phone_Number.

For example,

Record id	JSON_Name	Json_valueString
1	PHONE_LIST.0.PHONE_NUMBER	222.222.2222
1	PHONE_LIST.0.PHONE_TYPE	Phone
1	PHONE_LIST.1.PHONE_NUMBER	111.111.1111
1	PHONE_LIST.1.PHONE_TYPE	ALT

The desired output is the same 

Record id	PHONE_NUMBER	PHONE_NUMBER_ALT
1	222.222.2222	111.111.1111

[Bhavyapaliwal20]

 I Am not getting . You want this output aur want to want exactly.

[Masond3]

@Bhavyapaliwal20  

I would like to cross-tabulate all the JSON names and values to create a single row. The JSON names should be placed across the top as column headers, and the corresponding values should be populated under the appropriate JSON names.

For example, I have two entries for phone numbers: one is the primary phone number, and the other is an alternative phone number. This distinction is indicated by the node tree structure, where PHONE_LIST.0.PHONE_NUMBER represents the primary phone number and PHONE_LIST.1.PHONE_NUMBER represents the alternative phone number.

To differentiate between the two values:

• PHONE_LIST.0.PHONE_NUMBER Type = Phone
• PHONE_LIST.1.PHONE_NUMBER Type = Alt

[Bhavyapaliwal20]

do you want change column names??