Merge mutiple rows into single row with json column-CodePudding

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?

CodePudding user response：

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