Combine JSON objects from multiple rows into a single object

Question

CREATE TABLE t(Id int, typee nvarchar(50), jsonStr nvarchar(max));
INSERT INTO t(Id, typee, jsonStr) VALUES
(3786, 'APV', '{"1":1,"3":3,"4":24,"5":95}'),
(3786, 'VN', '{"1":3,"5":25}');

-- Expected result
-- {"APV": {"1":1,"3":3,"4":24,"5":95}, "VN":{"1":3,"5":25} }

SELECT Id,(
    SELECT CASE WHEN typee = 'VN'  THEN jsonStr END AS [VN]
         , CASE WHEN typee = 'VO'  THEN jsonStr END AS [VO]
         , CASE WHEN typee = 'APV' THEN jsonStr END AS [APV]
    FROM t AS x
    WHERE x.Id = t.Id
    FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER
) AS TEST1

FROM t
GROUP BY Id

DB<>Fiddle

I would like to get the output like:

{
  "APV": {
    "1": 1,
    "3": 3,
    "4": 24,
    "5": 95
  },
  "VN": {
    "1": 3,
    "5": 25
  }
}

Salman Arshad · Accepted Answer · 2019-11-28 11:34:52Z

2

FOR JSON will treat a string as a string even if it represents valid JSON. You need to use JSON_QUERY to convert a JSON string to an actual JSON object. MIN is required to combine multiple rows to one:

SELECT Id, (
  SELECT JSON_QUERY(MIN(CASE WHEN typee = 'APV' THEN jsonStr END)) AS [APV]
       , JSON_QUERY(MIN(CASE WHEN typee = 'VN'  THEN jsonStr END)) AS [VN]
       , JSON_QUERY(MIN(CASE WHEN typee = 'VO'  THEN jsonStr END)) AS [VO]
  FROM t AS x
  WHERE x.id = t.id
  FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER
)
FROM t
GROUP BY Id

Demo on db<>fiddle

answered Nov 28, 2019 at 11:34

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

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

Comments

Thom A · Accepted Answer · 2019-11-28 11:39:08Z

1

The problem is that you are storing JSON and then formatting returning it further as JSON. You would need to store non-JSON data to do it the way you want. Therefore it seems easier to treat it as a string:

SELECT t.id,
       '{' + STRING_AGG( '"' +t.typee + '": ' + t.jsonStr,',') WITHIN GROUP (ORDER BY typee) + '}'
FROM t
GROUP BY t.id;

Using FOR XML PATH:

SELECT t1.id,
       '{' + STUFF((SELECT ',"' + t2.typee + '": ' + t2.jsonStr
                    FROM t t2
                    WHERE t2.Id = t1.id
                    FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,1,'') + '}'
FROM t t1
GROUP BY t1.id;

edited Nov 28, 2019 at 11:39

answered Nov 28, 2019 at 11:03

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

5 Comments

Arooran Over a year ago

But I can not use STRING_AGG. because of my SQL Server 2016.

Thom A Over a year ago

Yes, thought it was 2016 it was added, but that was STRING_SPLIT; not finished my coffee yet. You'll need to use the STUFF and FOR XML PATH method. (will add shortly anyway)

Thom A Over a year ago

Added FOR XML PATH method, @Arooran .

Arooran Over a year ago

I will use XML Path. Good solution as well. +1

Arooran Over a year ago

STUFF function second parameter should be 1 not 2

Collectives™ on Stack Overflow

Combine JSON objects from multiple rows into a single object

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related