topic Re: How to handle Json Nested Structures in Alteryx Designer Desktop Discussions

How to handle Json Nested Structures

Masond3 — Tue, 12 Nov 2024 13:47:18 GMT

Hello Team,

I hope this message finds you well.

I have an input file that includes an two columns, ID and "DownloadData," (which contains JSON response).

I've attempted to parse the data in the "DownloadData" column, but I'm only able to retrieve a portion of the data. Whilst inspecting the Json it appears, the JSON includes nested arrays and objects, such as ind_other_names and ind_ia_current_employments. and i am a little stuck on how to break these out.

The Desired out put is to generate 2 Columns
ID
Json_Name
Json_String

Once i have broken it all out into , i would cross tab to get it back to a single row

Excepted outcome

ind_source_id

ind_firstname

ind_middlename

ind_lastname

ind_other_names

ind_bc_scope

ind_ia_scope

ind_ia_disclosure_fl

ind_approved_finra_registration_count

ind_employments_count

ind_industry_cal_date_iapd

firm_id

firm_name

branch_city

branch_state

branch_zip

ia_only

firm_bd_sec_number

firm_bd_full_sec_number

firm_ia_sec_number

firm_ia_full_sec_number

firm_id

firm_name

branch_city

branch_state

branch_zip

ia_only

firm_bd_sec_number

firm_bd_full_sec_number

firm_ia_sec_number

firm_ia_full_sec_number

ABC

11111

Test

ROBERT

Test

Test Test

Active

2024-12-11

1252

Test L.L.C.

New York

94104

9999

8-2698

688

801-688

1252

Google

SAN FRANCISCO

94189

2698

8-2698

688

801-688

Looking forward to your help

JSon Response in DownloadData

{"hits": {"total": 1, "hits": [{"_type": "_doc", "_source": {"ind_source_id": "11111", "ind_firstname": "Test", "ind_middlename": "ROBERT", "ind_lastname": "Test", "ind_other_names": ["Test Test"], "ind_bc_scope": "Active", "ind_ia_scope": "Active", "ind_ia_disclosure_fl": "N", "ind_approved_finra_registration_count": 1, "ind_employments_count": 2, "ind_industry_cal_date_iapd": "2024-12-11", "ind_ia_current_employments": [{"firm_id": "1252", "firm_name": "Test L.L.C.", "branch_city": "New York", "branch_state": "NY", "branch_zip": "94104", "ia_only": "Y", "firm_bd_sec_number": "9999", "firm_bd_full_sec_number": "8-2698", "firm_ia_sec_number": "688", "firm_ia_full_sec_number": "801-688"}, {"firm_id": "1252", "firm_name": "Google", "branch_city": "SAN FRANCISCO", "branch_state": "CA", "branch_zip": "94189", "ia_only": "N", "firm_bd_sec_number": "2698", "firm_bd_full_sec_number": "8-2698", "firm_ia_sec_number": "688", "firm_ia_full_sec_number": "801-688"}]}, "highlight": {"ind_source_id": ["<em>245855</em>"]}}]}}

Json Response Print Pretty

{ "hits": { "total": 1, "hits": [ { "_type": "_doc", "_source": { "ind_source_id": "11111", "ind_firstname": "Test", "ind_middlename": "ROBERT", "ind_lastname": "Test", "ind_other_names": [ "Test Test" ], "ind_bc_scope": "Active", "ind_ia_scope": "Active", "ind_ia_disclosure_fl": "N", "ind_approved_finra_registration_count": 1, "ind_employments_count": 2, "ind_industry_cal_date_iapd": "2024-12-11", "ind_ia_current_employments": [ { "firm_id": "1252", "firm_name": "Test L.L.C.", "branch_city": "New York", "branch_state": "NY", "branch_zip": "94104", "ia_only": "Y", "firm_bd_sec_number": "9999", "firm_bd_full_sec_number": "8-2698", "firm_ia_sec_number": "688", "firm_ia_full_sec_number": "801-688" }, { "firm_id": "1252", "firm_name": "Google", "branch_city": "SAN FRANCISCO", "branch_state": "CA", "branch_zip": "94189", "ia_only": "N", "firm_bd_sec_number": "2698", "firm_bd_full_sec_number": "8-2698", "firm_ia_sec_number": "688", "firm_ia_full_sec_number": "801-688" } ] }, "highlight": { "ind_source_id": [ "\u003Cem\u003E245855\u003C/em\u003E" ] } } ] } }

Re: How to handle Json Nested Structures

Raj — Tue, 12 Nov 2024 13:29:01 GMT

@Masond3
add the expected output.
will be in better position to help.

Re: How to handle Json Nested Structures

Raj — Tue, 12 Nov 2024 13:37:04 GMT

@Masond3
find attached a starter for solution this will help.
Mark done if solved.

Re: How to handle Json Nested Structures

Masond3 — Tue, 12 Nov 2024 13:39:14 GMT

@Raj Just uploaded expected outcome. (Hope that helps)

Re: How to handle Json Nested Structures

Raj — Tue, 12 Nov 2024 13:53:23 GMT

@Masond3
you can not have 2 columns with same name
also please take a look at the attached solution above.
that will definitely help.

Re: How to handle Json Nested Structures

Raj — Tue, 12 Nov 2024 13:58:28 GMT

@Masond3
find the updated
you can easily join this and get the result.

mark done if solved.

Re: How to handle Json Nested Structures

Masond3 — Tue, 12 Nov 2024 14:10:30 GMT

Hi @Raj Thank you for providing your insights. So i have just rechecked my flow

I am saving my input as a CSV.
DownoadData type = V_String & Size 254

When i am viewing this field within alteryx, all i can see is this :

{"hits": {"total": 1, "hits": [{"_type": "_doc", "_source": {"ind_source_id": "11111", "ind_firstname": "Test", "ind_middlename": "ROBERT", "ind_lastname": "Test", "ind_other_names": ["Test Test"], "ind_bc_scope": "Active", "ind_ia_scope": "Active", "in

When i add my input on to your flow, all i get is following fields exposed. Where your filter is hits.hits.0._source.ind_ia_current_employments. nothing is comming out of the true leg, the below comes out of the false leg.

hits.total	1
hits.hits.0._type	_doc
hits.hits.0._source.ind_source_id	11111
hits.hits.0._source.ind_firstname	Test
hits.hits.0._source.ind_middlename	ROBERT
hits.hits.0._source.ind_lastname	Test
hits.hits.0._source.ind_other_names.0	Test Test
hits.hits.0._source.ind_bc_scope	Active
hits.hits.0._source.ind_ia_scope	Active

Re: How to handle Json Nested Structures

Raj — Tue, 12 Nov 2024 14:27:39 GMT

@Masond3 change the size of data to 9999999999.

Re: How to handle Json Nested Structures

Masond3 — Tue, 12 Nov 2024 14:31:58 GMT

@Raj - As soon as you said that i did it and it worked. Thanks for the nudge, sometimes i need people like you to bounce the ideas off of

Re: How to handle Json Nested Structures

Raj — Tue, 12 Nov 2024 14:33:15 GMT

@Masond3
if this is solved
mark the solutions as done.

happy to help.