Json data read in Alteryx Desktop

Question

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 IDEMP_COMMON_FULL_NAMEPEOPLE_EMAILPEOPLE_MANAGER_NAME2180Kochi LimotNULLDex Albert2183Nix Jerryola.pola@test.comOla Pola

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

Thanks

Mayank Patel

danilang · Answer

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 studentDan

JSON Parse.yxmd

minvestis · Answer

Expected output:EMP IDEMP_COMMON_FULL_NAMEPEOPLE_EMAILPEOPLE_MANAGER_NAME2180Kochi LimotNULLDex Albert2183Nix Jerrynix.jerry@test.comOla Pola