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

CodePudding user response：

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

Demo on db<>fiddle here