Group by and Concatenate Json rows with SQL

0
votes

I have a table with multiple rows/records with a different json value. I would like to group by and concatenate those into a final single row, as part of a select query

Id   Col
1    {'a': 1}        
1    {'b': 2} 
2    {'c': 3} 
2    {'d': 4} 
2    {'e': 5}

Desired Output

Id   Col
1    {'a': 1, 'b': 2}        
2    {'c': 3, 'd': 4, 'e': 5}
2
sqlpostgresql
specify where is the table hosted - Nathan Nasser
tag the database you are using - eshirvana

2 Answers

0
votes

if you are using sql-server,try the following:

CREATE table #temp( Id int,  Col varchar(100))
Insert into #temp values
(1,'{''a'': 1}')      
,(1,'{''b'': 2}')
,(2,'{''c'': 3}')
,(2,'{''d'': 4}')
,(2,'{''e'': 5}')

SELECT DISTINCT id,
          '{' 
         + STUFF((
                  SELECT ',' + Replace(Replace(col,'{',''),'}','')
                  FROM #temp as t1
                  WHERE t1.id = t2.id
                  FOR XML PATH('')
                  ), 1, 1, '')  
         +'}' AS col 
FROM #temp as t2

drop table #temp
0
votes
SELECT id, CONCAT('{',STRING_AGG(REPLACE(REPLACE(Col,'{',''),'}',''),','),'}') AS COL
FROM table
GROUP BY id

demo in db<>fiddle