CODEWITHSUNDEEP
sqljsonsql-servert-sqlparsing
Sajal
Sajal

Reputation: 4401

SQL - openjson to extract key from json

Below @json contains 3 data object within an array. After using OPENJSON to extract these objects to a Table variable, please see the output attached.

DECLARE @json NVARCHAR(MAX);  
SET @json = N'[{"Container":"MSKU2913236","Seal":"ML-TH4773979","Size":"20","Temperature":"-20","TareWeight":"3.132","CreatedDate":"02-02-2018 00:00:00","Comment":null,"NetWeight":"21.445","TempRec#":null},{"Container":"MSKU3432702","Seal":"ML-TH4773972","Size":"20","Temperature":"-40","TareWeight":"2.872","CreatedDate":"02-02-2018 00:00:00","Comment":null,"NetWeight":"23.932","TempRec#":"TR12345"},{"Container":"MSKU4043053","Seal":"ML-TH4773973","Size":"20","Temperature":"-20","TareWeight":"2.995","CreatedDate":"02-02-2018 00:00:00","Comment":null,"NetWeight":"22.4","TempRec#":null}]';

DECLARE @ContainerTable TABLE(
    [Key] NVARCHAR(100),
    [Data] NVARCHAR(MAX)
);

INSERT INTO @ContainerTable
SELECT [key], [value] FROM OPENJSON(@json)

SELECT * FROM @ContainerTable

Output

enter image description here

Objective is to replace the Key column values with the Container property value from json in the Data column for all 3 rows.

Expected Output

enter image description here

Note: Expected output is hard coded and it only shows the one row but same is required for all rows.

Upvotes: 6

Views: 9053

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

You could use JSON_VALUE:

INSERT INTO @ContainerTable([Key], [Data])
SELECT JSON_VALUE([value],'$.Container'), [value]
FROM OPENJSON(@json);

DBFiddle Demo

Upvotes: 6

Related Questions