How to convert json data to records and insert into a using in sql server

Question

I am new to sqlserver. I want to convert json data into records and insert into a table. I have json data like

    {  
   "HEADER":[  
      {  
         "seq_id":343,
         "max_processed_scn":649044274,
         "time_processed":"13-MAR-19 09.03.23.081055 PM",
         "status":"COMPLETED"
      },
      {  
         "seq_id":344,
         "max_processed_scn":649214903,
         "time_processed":"15-MAR-19 12.49.31.606172 PM",
         "status":"COMPLETED"
      }
   ],
   "DETAIL":[  
      {  
         "seq_id":11215,
         "event_id":1,
         "caller_seq":343,
         "event_description":"BEGIN.."
      },
      {  
         "seq_id":11216,
         "event_id":2,
         "caller_seq":343,
         "event_description":"STARTING"
      },
      {  
         "seq_id":11217,
         "event_id":2,
         "caller_seq":343,
         "event_description":"STARTED "
      } "event_description":"TOTAL"

   ]
   }

so the array "header" I want to insert into one table and the other array "Detail" into another.Can anyone help me on achieving this. Thanks in advance

so I need detail table data like

seq_id  max_processed_scn        time_processed               status
343      649044274          13-MAR-19 09.03.23.081055 PM    COMPLETED
344      649214903          15-MAR-19 12.49.31.606172 PM    COMPLETED

I used this query :

   DECLARE @json NVARCHAR(MAX)
SET @json =  
N'[{  
   "HEADER":[  
      {  
         "seq_id":343,
         "max_processed_scn":649044274,
         "time_processed":"13-MAR-19 09.03.23.081055 PM",
         "status":"COMPLETED"
      },
      {  
         "seq_id":344,
         "max_processed_scn":649214903,
         "time_processed":"15-MAR-19 12.49.31.606172 PM",
         "status":"COMPLETED"
      }
   ],
   "DETAIL":[  
      {  
         "seq_id":11215,
         "event_id":1,
         "caller_seq":343,
         "event_description":"BEGIN.."
      },
      {  
         "seq_id":11216,
         "event_id":2,
         "caller_seq":343,
         "event_description":"STARTING"
      },
      {  
         "seq_id":11217,
         "event_id":2,
         "caller_seq":343,
         "event_description":"STARTED "
      } "event_description":"TOTAL"

   ]
   }
 ]'
SELECT *  
FROM OPENJSON(@json, '$.HEADER')  
  WITH (seq_id INT, max_processed_scn INT, time_processed NVARCHAR(100), [status] NVARCHAR(100))

I assume you're using SQL Server 2016/2017? Showing what you want your data to look like in SQL Server will help. Also, what have you tried so far? — Thom A
– Thom A ♦, Commented Mar 15, 2019 at 10:38
If you have SQL Server 2016+, this is trivially achieved with OPENJSON (e.g. SELECT * FROM OPENJSON(@json, '$.HEADER') WITH (seq_id INT, max_processed_scn INT, time_processed NVARCHAR(100), [status] NVARCHAR(100)) and another one for DETAIL). If you don't, it's almost not worth doing in T-SQL; client code is much better equipped. — Jeroen Mostert
– Jeroen Mostert, Commented Mar 15, 2019 at 10:41
Show your attempt in your post @pythonCoder. That code is unreadable there. I've added the SQL Server 2017 tag for you, as that's really important here. — Thom A
– Thom A ♦, Commented Mar 15, 2019 at 10:46
@pythonCoder add what you tried and what the problem is in your question, not the comments — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 15, 2019 at 10:47
@pythonCoder btw the JSON string you posted has no HEADERS attribute. It's an object that contains HEADER and DETAIL properties. Those contain arrays. At the very least you need to fix the names and access the array elements — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 15, 2019 at 10:50

Mukesh Arora · Accepted Answer · 2019-03-15 10:53:07Z

You can use below queries in sql server 2016 onwards to read the Json data and insert in required tables

Sample Data

declare @json varchar(max) =' {  
   "HEADER":[  
      {  
         "seq_id":343,
         "max_processed_scn":649044274,
         "time_processed":"13-MAR-19 09.03.23.081055 PM",
         "status":"COMPLETED"
      },
      {  
         "seq_id":344,
         "max_processed_scn":649214903,
         "time_processed":"15-MAR-19 12.49.31.606172 PM",
         "status":"COMPLETED"
      }
   ],
   "DETAIL":[  
      {  
         "seq_id":11215,
         "event_id":1,
         "caller_seq":343,
         "event_description":"BEGIN.."
      },
      {  
         "seq_id":11216,
         "event_id":2,
         "caller_seq":343,
         "event_description":"STARTING"
      },
      {  
         "seq_id":11217,
         "event_id":2,
         "caller_seq":343,
         "event_description":"STARTED "
      } 

   ]
   }
   '

Query to select Header data

  SELECT * FROM OPENJSON(@json, '$.HEADER') WITH (seq_id INT, max_processed_scn INT, time_processed NVARCHAR(50), [status] NVARCHAR(50))

Query to select Detail data

SELECT * FROM OPENJSON(@json, '$.DETAIL') WITH (seq_id INT, event_id INT, caller_seq int, event_description NVARCHAR(255))

Collectives™ on Stack Overflow

How to convert json data to records and insert into a using in sql server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related