How to handle Json Nested Structures

[Masond3]

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 

ID

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

Active

N

1

2

2024-12-11

1252

Test L.L.C.

New York

NY

94104

Y

9999

8-2698

688

801-688

1252

Google

SAN FRANCISCO

CA

94189

N

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"
          ]
        }
      }
    ]
  }
}

[Raj]

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

[Raj]

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

[Masond3]

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

[Raj]

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

[Raj · Accepted solution]

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

mark done if solved.

[Masond3]

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

[Raj]

@Masond3 change the size of data to 9999999999.

[Masond3]

@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 

[Raj]

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

happy to help.