Convert JSON to Table using T-SQL

Question

Here is the JSON Input

{
   "agentID":"WJ",
   "recordType":"AHL",
   "recordReference":{
      "stationCode":"ATL",
      "airlineCode":"XS",
      "recordId":"10001"
   },
   "entries":[
      {
         "bag":{
            "bagType":"22",
            "bagSize":"S",
            "category":"1"
         },
         "seqNo":"1",
         "noOfBagsGiven":"2"
      },
      {
         "bag":{
            "bagType":"23",
            "bagSize":"L",
            "category":"1"
         },
         "seqNo":"2",
         "noOfBagsGiven":"5",
         "dateBagsGiven":"2019-09-18"
      }
   ]
}

And here is the output I am hoping to get (Tabular or de-limited)

I do not know how to loop through the child entries and here is what I have so far

DECLARE @JSON    VARCHAR(MAX) =
'{
   "agentID":"WJ",
   "recordType":"AHL",
   "recordReference":{
      "stationCode":"ATL",
      "airlineCode":"XS",
      "recordId":"10001"
   },
   "entries":[
      {
         "bag":{
            "bagType":"22",
            "bagSize":"S",
            "category":"1"
         },
         "seqNo":"1",
         "noOfBagsGiven":"2"
      },
      {
         "bag":{
            "bagType":"23",
            "bagSize":"L",
            "category":"1"
         },
         "seqNo":"2",
         "noOfBagsGiven":"5",
         "dateBagsGiven":"2019-09-18"
      }
   ]
}';

SELECT *
FROM   OPENJSON(@json) WITH(agent_id        VARCHAR(2)  '$.agentID'
                           ,record_type     VARCHAR(4)  '$.recordType'
                           ,station_code    VARCHAR(4)  '$.recordReference.stationCode'
                           ,airline_code    VARCHAR(4)  '$.recordReference.airlineCode'
                           ,record_id       INT         '$.recordReference.recordId'
                           ,bag_type        INT         '$.entries.bag.bagType'
                           ,bag_size        VARCHAR(2)  '$.entries.bag.bagSize'
                           ,bag_category    INT         '$.entries.bag.bagCategory'
                           ,date_bags_given DATE        '$.entries.bag.dateBagsGiven'
                           );

Answer 1

Another possible approach is to use JSON_VALUE() (to extract a scalar value from a JSON string) and OPENJSON() (to parse the JSON string and get results as table):

SELECT 
   JSON_VALUE(@JSON,'$.agentID') AS agentID,
   JSON_VALUE(@JSON,'$.recordType') AS recordType,
   JSON_VALUE(@JSON,'$.recordReference.stationCode') AS stationCode,
   JSON_VALUE(@JSON,'$.recordReference.airlineCode') AS airlineCode,
   JSON_VALUE(@JSON,'$.recordReference.recordId') AS recordId,
   j.*
FROM OPENJSON(@JSON, '$.entries') WITH (
   bagType VARCHAR(10) '$.bag.bagType',
   bagSize VARCHAR(10) '$.bag.bagSize',
   category VARCHAR(10) '$.bag.category',
   seqNo VARCHAR(10) '$.seqNo',
   noOfBagsGiven VARCHAR(10) '$.noOfBagsGiven',
   dateBagsGiven VARCHAR(10) '$.dateBagsGiven'
) AS j