cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
cancel
Showing results for 
Search instead for 
Did you mean: 

Having Issues with extracting records from complex JSON

ABAGRI
New Contributor II

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

1 ACCEPTED SOLUTION

Accepted Solutions

User16753725469
Contributor II

Hi @Lantis Pillay​ 

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

Screenshot 2022-06-10 at 6.09.03 PM

View solution in original post

2 REPLIES 2

jose_gonzalez
Moderator
Moderator

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
Contributor II

Hi @Lantis Pillay​ 

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

Screenshot 2022-06-10 at 6.09.03 PM

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.