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_DataRecordID{"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":[{"EMAIL1{"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":[{"EMAIL2 Desired outcome RecordidPRIMARY_ADDR_CITYPRIMARY_ADDR_COUNTRYPRIMARY_ADDR_LINE1PRIMARY_ADDR_LINE2PRIMARY_ADDR_POSTAL_CODE1Test SAONEFR123 Test Street EMEA694002Test SAONEFR123 Test AvenueEMEA69400 Looking forward to your assistance Many thanks Masond3

@Masond3 I meant that the approach I've taken scales: * Use the Json Parse Tool * Text-to-Columns to break apart values by '.' * Filter to rows of the same structure * Identify the header, value and group by columns * Set the Data types of the group by columns (so row ids are number) * Cross-Tab (or otherwise transform) * 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

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

Common Use Cases

Accepted answers

OllieClarke

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

Use the Json Parse Tool
Text-to-Columns to break apart values by '.'
Filter to rows of the same structure
Identify the header, value and group by columns
Set the Data types of the group by columns (so row ids are number)
Cross-Tab (or otherwise transform)
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

All comments

binu_acs

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

json_parse_17092024.yxmd

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

json.yxmd

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

json.yxmd

Bhavyapaliwal20

Please check this workflow .

Screenshot 2024-09-17 193624.png

json_file_bhavya.yxmd

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??

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...

json.yxmd

Masond3

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

Quick Links

This months top contributors

mceleavey 383

AkimasaKajitani 382

mbarone 337

Hollingsworth 335

LanisC 335