sql server - How to loop through JSON array to insert rows in SQL Table using TSQL?

Question

I have following query where I would like to insert data from JSON:

INSERT INTO EncExt (
  reference
   ,system
   ,code
   ,display
)

SELECT
  JSON_VALUE(record, '$.id') AS reference

, JSON_VALUE(record, '$.extension[0].url') AS system
, JSON_VALUE(record, '$.extension[0].valueIdentifier.system') AS code
, JSON_VALUE(record, '$.extension[0].valueIdentifier.value') AS display

FROM [latest]

Now I would like to have loop kind of structure so that I can insert second element 1 then third lement [2] and so on..

   , JSON_VALUE(record, '$.extension[1].url') AS system
    , JSON_VALUE(record, '$.extension[1].valueIdentifier.system') AS code
    , JSON_VALUE(record, '$.extension[1].valueIdentifier.value') AS display


   , JSON_VALUE(record, '$.extension[2].url') AS system
    , JSON_VALUE(record, '$.extension[2].valueIdentifier.system') AS code
    , JSON_VALUE(record, '$.extension[2].valueIdentifier.value') AS display

And so on...

JSON data stored in the SQL table where the column name is record:

{
   "resourceType":"Enc",
   "id":"xnDKICp-xP6-120744",
   "meta":{
      "versionId":"1605118405049",
      "lastUpdated":"2020-11-11T18:13:25.049Z"
   },
   "extension":[
      {
         "url":"ENCTR",
         "valueIdentifier":{
            "system":"RGN_CD",
            "value":"RGN_CD"
         }
      },
      {
         "url":"AMBLTR_ENCTR",
         "valueIdentifier":{
            "system":"SYS_CD",
            "value":"SYS_CD"
         }
      }
   ],
   "identifier":[
      {
         "system":"ENCTR/SK",
         "value":"ENCTR_SK"
      }
   ]
}

Image of record column where JSON objects stored:

Answer 1

Here is how you can parse your JSON to be inserted into your table:

DECLARE @Source varchar(MAX) = '{"resourceType":"Enc","id":"xnDKICp-xP6-120744","meta":{"versionId":"1605118405049","lastUpdated":"2020-11-11T18:13:25.049Z"},"extension":[{"url":"ENCTR","valueIdentifier":{"system":"RGN_CD","value":"RGN_CD"}},{"url":"AMBLTR_ENCTR","valueIdentifier":{"system":"SYS_CD","value":"SYS_CD"}}],"identifier":[{"system":"ENCTR/SK","value":"ENCTR_SK"}]}';

SELECT
    resourceType,
    id,
    versionId,
    lastUpdated,
    [url],
    vi_system,
    vi_value,
    i_system,
    i_value
FROM OPENJSON ( @Source ) WITH (
    resourceType varchar(50) '$.resourceType',
    id varchar(50) '$.id',
    meta nvarchar(MAX) '$.meta' AS JSON,
    extension nvarchar(MAX) AS JSON,
    identifier nvarchar(MAX) AS JSON
)
OUTER APPLY OPENJSON ( meta ) WITH (
    versionId varchar(50) '$.versionId',
    lastUpdated datetime '$.lastUpdated'
) AS Meta
OUTER APPLY OPENJSON ( extension ) WITH (
    [url] varchar(50) '$.url',
    valueIdentifier nvarchar(MAX) '$.valueIdentifier' AS JSON
) AS Extension
OUTER APPLY OPENJSON ( valueIdentifier ) WITH (
    [vi_system] varchar(50) '$.system',
    [vi_value] varchar(50) '$.value'
) AS ValueIdentifier
OUTER APPLY OPENJSON ( identifier ) WITH (
    [i_system] varchar(50) '$.system',
    [i_value] varchar(50) '$.value'
) AS Identifier;

Returns

+--------------+--------------------+---------------+-------------------------+--------------+-----------+----------+----------+----------+
| resourceType |         id         |   versionId   |       lastUpdated       |     url      | vi_system | vi_value | i_system | i_value  |
+--------------+--------------------+---------------+-------------------------+--------------+-----------+----------+----------+----------+
| Enc          | xnDKICp-xP6-120744 | 1605118405049 | 2020-11-11 18:13:25.050 | ENCTR        | RGN_CD    | RGN_CD   | ENCTR/SK | ENCTR_SK |
| Enc          | xnDKICp-xP6-120744 | 1605118405049 | 2020-11-11 18:13:25.050 | AMBLTR_ENCTR | SYS_CD    | SYS_CD   | ENCTR/SK | ENCTR_SK |
+--------------+--------------------+---------------+-------------------------+--------------+-----------+----------+----------+----------+

Note that some column values will be duplicated given meta, extension, and identifier contain arrays.

UPDATE

To run against a table column do the following:

DECLARE @Source varchar(MAX) = '{"resourceType":"Enc","id":"xnDKICp-xP6-120744","meta":{"versionId":"1605118405049","lastUpdated":"2020-11-11T18:13:25.049Z"},"extension":[{"url":"ENCTR","valueIdentifier":{"system":"RGN_CD","value":"RGN_CD"}},{"url":"AMBLTR_ENCTR","valueIdentifier":{"system":"SYS_CD","value":"SYS_CD"}}],"identifier":[{"system":"ENCTR/SK","value":"ENCTR_SK"}]}';
DECLARE @JsonData table ( record nvarchar(MAX) );
INSERT INTO @JsonData VALUES ( @Source );

SELECT
    ParsedJson.*
FROM @JsonData AS jd
OUTER APPLY (

    SELECT
        resourceType, id, versionId, lastUpdated, [url], vi_system, vi_value, i_system, i_value
    FROM OPENJSON ( jd.record ) WITH (
        resourceType varchar(50) '$.resourceType',
        id varchar(50) '$.id',
        meta nvarchar(MAX) '$.meta' AS JSON,
        extension nvarchar(MAX) AS JSON,
        identifier nvarchar(MAX) AS JSON
    )
    OUTER APPLY OPENJSON ( meta ) WITH (
        versionId varchar(50) '$.versionId',
        lastUpdated datetime '$.lastUpdated'
    ) AS Meta
    OUTER APPLY OPENJSON ( extension ) WITH (
        [url] varchar(50) '$.url',
        valueIdentifier nvarchar(MAX) '$.valueIdentifier' AS JSON
    ) AS Extension
    OUTER APPLY OPENJSON ( valueIdentifier ) WITH (
        [vi_system] varchar(50) '$.system',
        [vi_value] varchar(50) '$.value'
    ) AS ValueIdentifier
    OUTER APPLY OPENJSON ( identifier ) WITH (
        [i_system] varchar(50) '$.system',
        [i_value] varchar(50) '$.value'
    ) AS Identifier

) AS ParsedJson;

INSERT EXAMPLE

INSERT INTO EncExt (
    [reference], [system], [code], [display]
)
SELECT
    x.[id], x.[url], x.[vi_system], x.[vi_value]
FROM latest
OUTER APPLY (

    SELECT
        resourceType, id, versionId, lastUpdated, [url], vi_system, vi_value, i_system, i_value
    FROM OPENJSON ( latest.record ) WITH (
        resourceType varchar(50) '$.resourceType',
        id varchar(50) '$.id',
        meta nvarchar(MAX) '$.meta' AS JSON,
        extension nvarchar(MAX) AS JSON,
        identifier nvarchar(MAX) AS JSON
    )
    OUTER APPLY OPENJSON ( meta ) WITH (
        versionId varchar(50) '$.versionId',
        lastUpdated datetime '$.lastUpdated'
    ) AS Meta
    OUTER APPLY OPENJSON ( extension ) WITH (
        [url] varchar(50) '$.url',
        valueIdentifier nvarchar(MAX) '$.valueIdentifier' AS JSON
    ) AS Extension
    OUTER APPLY OPENJSON ( valueIdentifier ) WITH (
        [vi_system] varchar(50) '$.system',
        [vi_value] varchar(50) '$.value'
    ) AS ValueIdentifier
    OUTER APPLY OPENJSON ( identifier ) WITH (
        [i_system] varchar(50) '$.system',
        [i_value] varchar(50) '$.value'
    ) AS Identifier

) AS x
WHERE latest.column_name = 'some_value';