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:
Advertisement
Answer
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';