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