Missed the Q4 Fall Release Product Update? Watch the on-demand webinar for more info on the latest in Designer 24.2, Auto Insights Magic Reports, and more!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Handeling Nested Json files

MartWClimber
9 - Comet

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_NameJSON_ValueString
firstNameJohn
lastNameSmith
age25
address.streetAddress21 2nd Street
address.cityNew York
address.stateNY
address.postalCode10021
phoneNumber.0.typehome
phoneNumber.0.number212 555-1234
phoneNumber.1.typefax
phoneNumber.1.number646 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. 

firstNamelastNameageaddress.streetAddressaddress.cityaddress.stateaddress.postalCodephoneNumber.TypephoneNumber.Number
JohnSmith2521 2nd StreetNew YorkNY10021home212 555-1234
JohnSmith2521 2nd StreetNew YorkNY10021fax646 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?

4 REPLIES 4
Felipe_Ribeir0
16 - Nebula

Hi @MartWClimber 

 

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

Felipe_Ribeir0_0-1665519125913.png

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

 

Thanks.

 

Felipe_Ribeir0
16 - Nebula

Hi @MartWClimber 

 

It worked?

MartWClimber
9 - Comet

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
16 - Nebula

Hi @MartWClimber 

 

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

 

Felipe_Ribeir0_0-1666014582123.png

Felipe_Ribeir0_1-1666014687521.png

 

 

Labels
Top Solution Authors