Having Issues with extracting records from complex JSON

ABAGRI — Mon, 25 Apr 2022 13:26:55 GMT

Hi Team,

we are using delta live tables to ingest data from Kafka.

the JSON file we receive is a complex JSON structure and we are trying to explode the file into its necessary columns and transactions,

Thank you

please see attached sample file

{ "TableName": "SCADAAlarms", "Site": "UK", "Result": [ { "ID": "2517529557685533332_EM_MIX2.ALM.10", "Plant": "R4P", "SequenceNumber": 6, "CreationTimeUTC": "2022-04-07T15:23:51.4466667", "AlarmID": "EM_MIX2.ALM.50", "AlarmMessage": "Mixer 2 Utilities Not Running Sequence Stopped", "AlarmClass": "MIXHI2222", "GeneratedTime": "2022-04-07T15:23:51.4466667", "AcknowledgedTime": null, "ResetTime": null, "GeneratedLoggedBy": "MASTER_PTM0_RP", "AcknowledgedLoggedBy": null, "ResetLoggedBy": null },{ "ID": "2517529557685533332_EM_MIX2.ALM.50", "Plant": "R4P", "SequenceNumber": 7, "CreationTimeUTC": "2022-04-07T15:23:51.4466667", "AlarmID": "EM_MIX2.ALM.50", "AlarmMessage": "Mixer 2 Utilities Not Running Sequence Stopped", "AlarmClass": "MIXHI2222", "GeneratedTime": null, "AcknowledgedTime": "2022-04-07T15:24:51.4466667", "ResetTime": null, "GeneratedLoggedBy": null, "AcknowledgedLoggedBy": "MASTER_PTM0_RP", "ResetLoggedBy": null },{ "ID": "2517529557685533332_EM_MIX2.ALM.50", "Plant": "R4P", "SequenceNumber": 8, "CreationTimeUTC": "2022-04-07T15:23:51.4466667", "AlarmID": "EM_MIX2.ALM.50", "AlarmMessage": "Mixer 2 Utilities Not Running Sequence Stopped", "AlarmClass": "MIXHI2222", "GeneratedTime": null, "AcknowledgedTime": null, "ResetTime": "2022-04-07T15:25:51.4466667", "GeneratedLoggedBy": null, "AcknowledgedLoggedBy": null, "ResetLoggedBy": "MASTER_PTM0_RP" } ] }

Re: Having Issues with extracting records from complex JSON

jose_gonzalez — Thu, 02 Jun 2022 00:03:25 GMT

Hi @Lantis Pillay,

Are you getting an error or what is the issue you are facing? Please share what you have done so far, this will help us to undertand better what is the issue.

Re: Having Issues with extracting records from complex JSON

User16753725469 — Fri, 10 Jun 2022 12:41:42 GMT

Hi @Lantis Pillay

Could you please try to parse JSON records in the below way

topic Having Issues with extracting records from complex JSON in Data Engineering

Having Issues with extracting records from complex JSON

Re: Having Issues with extracting records from complex JSON

Re: Having Issues with extracting records from complex JSON