Having Issues with extracting records from complex JSON

[ABAGRI]

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

[jose_gonzalez]

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.

[User16753725469 · Accepted solution]

Hi @Lantis Pillay​ 

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