Handeling Nested Json files

[MartWClimber]

I''m building a workflow that contains json data. this data is structured and all but for every json file the nested node can change form for example 1 line to 10+ lines.

looking at the example workflow Alteryx provides I see that the are using the exact lay-out as I got in my data.

{
    "firstName": "John",
    "lastName": "Smith",
    "age": 25,
    "address": {
        "streetAddress": "21 2nd Street",
        "city": "New York",
        "state": "NY",
        "postalCode": "10021"
    },
    "phoneNumber": [
        {
            "type": "home",
            "number": "212 555-1234"
        },
        {
            "type": "fax",
            "number": "646 555-4567"
        }
    ]
}

For now I get the following output (just like the example shows)

JSON_Name	JSON_ValueString
firstName	John
lastName	Smith
age	25
address.streetAddress	21 2nd Street
address.city	New York
address.state	NY
address.postalCode	10021
phoneNumber.0.type	home
phoneNumber.0.number	212 555-1234
phoneNumber.1.type	fax
phoneNumber.1.number	646 555-4567

But i'm wondering If I can create an output more like this (see below) that is dynamic in if there is 1 line only 1 row of data and if there are 10+ lines then 10+ row's of data are there. 

firstName	lastName	age	address.streetAddress	address.city	address.state	address.postalCode	phoneNumber.Type	phoneNumber.Number
John	Smith	25	21 2nd Street	New York	NY	10021	home	212 555-1234
John	Smith	25	21 2nd Street	New York	NY	10021	fax	646 555-4567

can this be done with the json parse tool or do I need to use something like a crosstab? and can it be done dynamic?

[Felipe_Ribeir0]

Hi @MartWClimber 

One way of doing this, please check the attached workflow.

If the provided answer helped you to solve the problem/was correct, please accept it as a solution :)

Thanks.

[Felipe_Ribeir0]

Hi @MartWClimber 

It worked?

[MartWClimber]

it sort of worked.

the only problem I'm facing now the data is in json format. (not an text input tool) so when I open the json file in Alteryx it auto creates 2 fields when loading a json file.

is there a way to keep the raw json file intact?

[Felipe_Ribeir0 · Accepted solution]

Hi @MartWClimber 

Please see if this works for you. If not, please share some json files here.

[Raj_007]

Hi Felipe, This solutions looks better when we know something with nested... but in my scenario i have lot of nested and looking for a better way to flatten it

[Felipe_Ribeir0]

Hi @Raj_007 

Unfortunately yes, with this approach the workflow must be specific to handle the JSON in question.

However, it is possible to have a generic script that handles any valid nested JSON.

• Using python's pyspark library
  
• Using R

With chatgpt you can generate the codes and then integrate them into your workflows.

[Raj_007]

Hi Felipe, i have this thread with the how the JSON looks like... so it might be very difficult to get this in Alteryx?