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<>小提琴
我想得到这样的输出:
{
"APV": {
"1": 1,
"3": 3,
"4": 24,
"5": 95
},
"VN": {
"1": 3,
"5": 25
}
}
FOR JSON
会将字符串视为字符串,即使它表示有效的 JSON。您需要使用JSON_QUERY
将 JSON 字符串转换为实际的 JSON 对象。MIN
需要将多行合并为一行:
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
在数据库<>小提琴上演示
问题是您正在存储 JSON,然后将其进一步格式化为 JSON。您需要存储非 JSON 数据才能按照您想要的方式执行此操作。因此,将其视为字符串似乎更容易:
SELECT t.id,
'{' + STRING_AGG( '"' +t.typee + '": ' + t.jsonStr,',') WITHIN GROUP (ORDER BY typee) + '}'
FROM t
GROUP BY t.id;
使用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;