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)) 

Answer 1

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))