Merge mutiple rows into single row with json column

Question

I want to merge the rows into a single row with JSON column in SQL Server

Source table:

id	studyid	eye	measurements	subjectref	type	source	dateOn
1	1s	left	{"a":1,"b":2}	1reference	k	abc	10/12/2022
2	1s	left	{"c":"1a"}	1reference	o	abc	10/12/2022
3	1s	left	{"d":2}	1reference	or	abc	10/12/2022
4	2s	left	{"a":1,"b":2}	1reference	k	abc	01/11/2022

Desired output:

studyId	eye	measurements	subjectref	source	dateOn
1s	left	{"a":1,"b":2,"c":"1a","d":2}	1reference	abc	10/12/2022
2s	left	{"a":1,"b:2"}	1reference	abc	01/11/2022

Can you please help with this?

Martin Smith · Accepted Answer · 2023-02-01 16:12:23Z

1

If (as discussed in your deleted previous question) the JSON is simple as shown and there is no possibility of needing to merge different rows having the same key you can just do this with string concatenation. DB Fiddle

The TRIM removes the opening and closing braces, from each measurements document. The ones in each group get concatenated together with a comma and then opening and closing braces are appended onto the result.

SELECT   studyId,
         eye,
         measurements = '{' + STRING_AGG(TRIM('{}' FROM measurements), ',') + '}',
         subjectRef,
         source,
         dateOn
FROM     YourTable
GROUP BY studyId,
         eye,
         subjectRef,
         source,
         dateOn

answered Feb 1, 2023 at 16:12

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

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

1 Comment

cva Over a year ago

Thanks Smith. Can you please share any reference/idea that if possibility of same key is there in multiple rows having same json key, expected result is we can override the key any of the row json property value of the same key.

Collectives™ on Stack Overflow

Merge mutiple rows into single row with json column

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