时间戳

首页 > 解决方案 > 使用 SQL Server for JSON PATH 的动态 ROOT

json - 使用 SQL Server for JSON PATH 的动态 ROOT

问题描述

我有一个要呈现给 Json 对象的关键字表：

ID  Keyword     Text    Value
4   Category    A       10
5   Category    B       20
1   Season      Winter  1
2   Season      Spring  2
3   Season      Summer  3
9   Season      Fall    4
6   UnitType    Ft      Feet
7   UnitType    Set     Set
8   UnitType    $   Dollar

将每个类别分组到它自己的对象中。

我已经能够创建这个：

{"Keywords":[
{"Keyword":"Category","Values":[{"Value":"10","Text":"A"},{"Value":"20","Text":"B"}]},
{"Keyword":"Season","Values":[{"Value":"1","Text":"Winter"},{"Value":"2","Text":"Spring"},{"Value":"3","Text":"Summer"},{"Value":"4","Text":"Fall"}]},
{"Keyword":"UnitType","Values":[{"Value":"Dollar","Text":"$"},{"Value":"Feet","Text":"Ft"},{"Value":"Set","Text":"Set"}]}
]}

使用：

SELECT T.Keyword AS 'Keyword', 
(SELECT [subT].[Value] AS 'Value', [subT].[Text] AS 'Text' 
FROM tblKeywords subT WHERE subT.Keyword=T.Keyword 
ORDER BY [subT].[Value]
FOR JSON PATH) AS 'Values'
FROM tblKeywords T
GROUP BY T.Keyword
FOR JSON PATH, ROOT('Keywords')

但我希望能够将子查询的 ROOT 设置为关键字本身。这甚至可能吗？

期望的结果是：

{"Keywords":[
{"Category":[{"Value":"10","Text":"A"},{"Value":"20","Text":"B"}]},
{"Season":[{"Value":"1","Text":"Winter"},{"Value":"2","Text":"Spring"},{"Value":"3","Text":"Summer"},{"Value":"4","Text":"Fall"}]},
{"UnitType":[{"Value":"Dollar","Text":"$"},{"Value":"Feet","Text":"Ft"},{"Value":"Set","Text":"Set"}]}
]}

标签： jsonsql-server

解决方案

初始数据：

DROP TABLE IF EXISTS #tblKeywords;
CREATE TABLE #tblKeywords (ID INT, Keyword NVARCHAR(255), [Text] NVARCHAR(255), Value NVARCHAR(255));
INSERT INTO #tblKeywords(ID,Keyword,Text,[Value])VALUES
     (4,'Category','A','10')
    ,(5,'Category','B','20')
    ,(1,'Season','Winter','1')
    ,(2,'Season','Spring','2')
    ,(3,'Season','Summer','3')
    ,(9,'Season','Fall','4')
    ,(6,'UnitType','Ft','Feet')
    ,(7,'UnitType','Set','Set')
    ,(8,'UnitType','$','Dollar')
;

我知道，动态 SQL，但是：

DECLARE @DynSql NVARCHAR(MAX) = (
    SELECT 'SELECT ' + STUFF((
            SELECT DISTINCT N',(SELECT [subT].[Value] AS [Value], [subT].[Text] AS [Text] 
            FROM #tblKeywords subT 
            WHERE subT.Keyword = ''' + t.Keyword + N''' 
            ORDER BY [subT].[Value]
            FOR JSON PATH
            ) AS [' + t.Keyword + N']' + CHAR(13) + '   '
            FROM #tblKeywords t
            FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,1,'')
        + 'FOR JSON PATH, ROOT(''Keywords'')'
)
;
--PRINT @DynSql;
EXEC (@DynSql);

推荐阅读