Json data read in Alteryx Desktop

[minvestis]

Hi,

I need help to read following JSON format to convert into table format.

Example :1

{"key":{"PEOPLE": "2180"},"coreEntityKey":{"EMP":{"id": "2180"}},"attributes":[{"key": "PEOPLE_MANAGER_NAME","rawValue": "Dex, Albert","value": "Dex, Albert"},{"key": "PEOPLE_EMAIL"},{"key": "EMP_COMMON_FULL_NAME","rawValue": "Kochi, Limot","value": "Kochi, Limot"}],"children":[],"summaryListDisplay":[],"rootEntity": "PEOPLE","customProperties":{}}

Example : 2

{"key":{"PEOPLE": "2183"},"coreEntityKey":{"EMP":{"id": "2183"}},"attributes":[{"key": "PEOPLE_MANAGER_NAME","rawValue": "Ola, Pola","value": "Ola, Pola"},{"key": "PEOPLE_EMAIL","rawValue": "nix.jerry@test.com","value": "nix.jerry@test.com"},{"key": "EMP_COMMON_FULL_NAME","rawValue": "Nix, Jerry","value":"Nix, Jerry"}],"children":[],"summaryListDisplay":[],"rootEntity": "PEOPLE","customProperties":{}}

Expected output:

EMP ID	EMP_COMMON_FULL_NAME	PEOPLE_EMAIL	PEOPLE_MANAGER_NAME
2180	Kochi Limot	NULL	Dex Albert
2183	Nix Jerry	ola.pola@test.com	Ola Pola

Please advise, how to achieve above output? I tried using JSON but I am not getting expected output.

Thanks

Mayank Patel

[minvestis]

Expected output:

EMP ID	EMP_COMMON_FULL_NAME	PEOPLE_EMAIL	PEOPLE_MANAGER_NAME
2180	Kochi Limot	NULL	Dex Albert
2183	Nix Jerry	nix.jerry@test.com	Ola Pola

[danilang]

Hi @minvestis 

The JSON Parse tool is your friend here.  It splits the json into name/value pairs.  From there its a matter of splitting the attribute names using a Regex tool and a couple of crosstabs to get your output

The names still have the comma in them, but removing them is left as an exercise for the student

Dan