topic Re: Not able to parse JSON object in Data Quality

Not able to parse JSON object

Rama_Sunkara — Fri, 15 Nov 2024 23:45:14 GMT

Hi,

I am new to Talend. Need help to parse below json object. I am not able to parse below JSON object.

{"host":{"host":"MRK-SITE1_MX64","name":"MRK-SITE1_MX64"},"groups":["MRK/Discovered Hosts","MRK/SageNet Solutions Lab (SPOG)"],"applications":["MRK Raw Items"],"itemid":5000789,"name":"MRK Appliance Uplink statuses","clock":1615149688,"ns":7987118,"value":"[{\"networkId\":\"N_AP378907\",\"serial\":\"KPW-ABT-33456\",\"model\":\"BX98\",\"lastReportedAt\":\"2021-03-07T20:41:00Z\",\"uplinks\":[{\"interface\":\"Link1\",\"status\":\"active\",\"ip\":\"148.72.102.467\",\"gateway\":\"148.72.102.468\",\"publicIp\":\"148.72.102.467\",\"primaryDns\":\"8.8.8.8\",\"secondaryDns\":\"1.1.1.1\",\"ipAssignedBy\":\"static\"},{\"interface\":\"Link2\",\"status\":\"ready\",\"ip\":\"192.168.66.105\",\"gateway\":\"192.174.19.1\",\"publicIp\":\"166.166.86.193\",\"primaryDns\":\"192.174.19.6\",\"secondaryDns\":\"192.174.19.1\",\"ipAssignedBy\":\"dhcp\"}]}]","type":4}

Thank you,

Rama

Re: Not able to parse JSON object

Anonymous — Sat, 11 Sep 2021 00:32:19 GMT

Try to create a Json metadata and follows the wizards to extract the data you want.

Let me know if you have any troubles.

Regards

Shong

Re: Not able to parse JSON object

Rama_Sunkara — Sat, 11 Sep 2021 16:57:21 GMT

Hi Shong,

Thank you very much for your help.

Able to parse elements host, host.name, groups, applications, itemid, name, clock, ns. Not able to parse child elements of value. Value is coming as single string. How can I parse child elements of Value.

Value is containing "\" and double quotes ("/"")at starting and ending.

I need to parse sub elements of value and I need to store in database.

"value":"[{\"networkId\":\"N_AP378907\",\"serial\":\"KPW-ABT-33456\",\"model\":\"BX98\",\"lastReportedAt\":\"2021-03-07T20:41:00Z\",\"uplinks\":[{\"interface\":\"Link1\",\"status\":\"active\",\"ip\":\"148.72.102.467\",\"gateway\":\"148.72.102.468\",\"publicIp\":\"148.72.102.467\",\"primaryDns\":\"8.8.8.8\",\"secondaryDns\":\"1.1.1.1\",\"ipAssignedBy\":\"static\"},{\"interface\":\"Link2\",\"status\":\"ready\",\"ip\":\"192.168.66.105\",\"gateway\":\"192.174.19.1\",\"publicIp\":\"166.166.86.193\",\"primaryDns\":\"192.174.19.6\",\"secondaryDns\":\"192.174.19.1\",\"ipAssignedBy\":\"dhcp\"}]}]"

Thank you,

Rama

Re: Not able to parse JSON object

Anonymous — Mon, 13 Sep 2021 01:17:02 GMT

First, extract the string of Value element, and then use another tExtractJasonFields to parse the string.

Re: Not able to parse JSON object

Rama_Sunkara — Mon, 13 Sep 2021 12:37:59 GMT

Thank you very much Shong.