SQL - openjson to extract key from json

Question

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

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

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

Resultset is not json, I am storing data in table and need to replace key with one of the property value of the json — Sajal
– Sajal, Commented Aug 14, 2018 at 16:17

Lukasz Szozda · Accepted Answer · 2018-08-14 16:38:47Z

6

You could use JSON_VALUE:

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

DBFiddle Demo

edited Aug 14, 2018 at 16:38

answered Aug 14, 2018 at 16:23

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

1 Comment

Gottfried Lesigang Over a year ago

No need for the sub-select, this SELECT JSON_VALUE([value],'$.Container'), [value] FROM OPENJSON(@json) is enough...

Collectives™ on Stack Overflow

SQL - openjson to extract key from json

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related