json - 带有 SQL Server 的动态 ROOT 用于 JSON PATH

Question

我有一个要呈现给 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"}]}
]}

Answer 1

另一种可能的方法是使用 FOR JSON 和字符串操作的组合，但没有动态语句:

输入:

CREATE TABLE #tblKeywords (
   Id int, 
   [Keyword] nvarchar(50), 
   [Text] nvarchar(50), 
   [Value] nvarchar(50)
);
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')

T-SQL:

SELECT CONCAT(
   N'{"Keywords":[',
   STUFF(
      (
      SELECT DISTINCT CONCAT(N',{"', k.[Keyword], '":', c.[Json], N'}')
      FROM #tblKeywords k
      CROSS APPLY (
         SELECT [Value], [Text]
         FROM #tblKeywords
         WHERE [Keyword] = k.[Keyword]
         FOR JSON PATH
      ) c([Json])
      FOR XML PATH('')
      ), 1, 1, N''
   ),  
   N']}'
) AS JsonOutput

输出:

JsonOutput

{"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":"Feet","Text":"Ft"},{"Value":"Set","Text":"Set"},{"Value":"Dollar","Text":"$"}]}]}