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