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:

Why do you want to loop at all here? You're using SQL, a set based language. What does your actual JSON look like? Why not treat it like a dataset and use OPENJSON? — Thom A
– Thom A ♦, Commented Jan 12, 2021 at 20:07
I think you should have a look here stackoverflow.com/questions/38635845/… — Alen.Toma
– Alen.Toma, Commented Jan 12, 2021 at 20:33
Please don't SHOUT at us in the question, @DineshMadhup , we can read your "inside voice" just fine. — Thom A
– Thom A ♦, Commented Jan 12, 2021 at 20:38
JSON data is in SQL table stored in column named record. I added JSON data in question. — Dinesh Madhup
– Dinesh Madhup, Commented Jan 12, 2021 at 20:54

critical_error · Accepted Answer · 2021-01-14 00:08:25Z

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';

edited Jan 14, 2021 at 0:08

answered Jan 12, 2021 at 21:32

critical_error

6,8263 gold badges16 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Dinesh Madhup Over a year ago

All Right. But I have JSON data stored in SQL table with column name is "record". And each row contains JSON objects.

critical_error Over a year ago

@DineshMadhup See the update to my example.

Dinesh Madhup Over a year ago

Thanks for the update. This way we can flatten one JSON object. But I have column named "record" in SQL database where thousands of rows are stored. Each rows represent one JSON object. For example. If I run query SELECT record FROM myTable; It returns 1000 rows and each rows represent one JSON object.

critical_error Over a year ago

I'm not sure I'm following you @DineshMadhup. This will process each record row's JSON of your SELECT into a resultset that contains all of the parsed data. As a quick aside, parsing 1000 rows at once will be a heavy request on the server. Are you saying you want to combine all your rows into a single JSON object and parse that?

Dinesh Madhup Over a year ago

Simply I am trying to see is like a loop which scan each rows from [latest] table and column name "record" and parse json. The parsed data get inserted into EncExt table.

|

Collectives™ on Stack Overflow

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

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related