Transform json into table rows using sql

Question

My initial table looks like this (values of object are dynamic so it's not always the same structure):

id  tags 
1   {"tag1": "value1"} 
2   {"tagA": "valueA", "tagB": "valueB"}

And I want to transform it into this:

id tag   value 
1  tag1  value1 
2  tagA  valueA 
2  tagB  valueB

Could anyone help me with the transformation? I tried using OPENJSON like this but it only works if I filter by id (plus I'm not getting the id column in the result):

DECLARE @json NVARCHAR(MAX)
SET  @json  =  (SELECT tags from auxTagsResources where instanceId = 1)
SELECT \[key\] as tagName, value as tagValue FROM OPENJSON(@json);

Result:

tagName tagValue
tag1    value1

I've added the sql-server tag to your question because it was pretty clear from the code, but in the future please don't forget to tag your rdbms unless you're looking for an rdbms-agnostic solution. — Zohar Peled, Dec 15 '21 at 11:32

LukStorms · Accepted Answer · 2021-12-15T11:25:31.360

You can apply to the json.

create table auxTagsResources (
 id int identity primary key, 
 instanceId int not null,
 tags nvarchar(max)
);

insert into auxTagsResources (instanceId, tags) values
  (1, N'{"tag1": "value1"}') 
, (1, N'{"tagA": "valueA", "tagB": "valueB"}');

SELECT tag.id, j.[key] as tag, j.[value] 
FROM auxTagsResources tag
CROSS APPLY OPENJSON(tag.tags) j
WHERE tag.instanceId = 1

id	tag	value
1	tag1	value1
2	tagA	valueA
2	tagB	valueB

Demo on db<>fiddle here

Transform json into table rows using sql

1 Answers1