表的分层JSON输出

Question

我有这个桌子结构

| User | Type    | Data |
|------|---------|------|
| 1    | "T1"    | "A"  |
| 1    | "T1"    | "B"  |
| 1    | "T2"    | "C"  |
| 2    | "T1"    | "D"  |

我想从我的查询中得到一个层次化的JSON字符串

{
  "1": {
    "T1": [
      "A",
      "B"
    ],
    "T2": [
      "C"
    ]
  },
  "2": {
    "T1": [
      "D"
    ]
  }
}

因此，每个用户有一个条目，每个类型有一个子条目，然后每个数据有一个子条目

我只找到了JSON路径的、ROOT（'x'）或AUTO语句，但没有任何东西可以使这个层次化。这可能是开箱即用的吗？我找不到任何东西，所以我尝试了（递归）CTE，但没走多远。如果有人能为我指出正确的方向，我将不胜感激。

Answer 1

这并不是你想要的（我对JSON不太在行），但它确实能让你接近你需要的形状，直到更好的东西出现(https://jsonformatter.org/json-parser/974b6b)

use tempdb
GO

drop table if exists users
create table users (
    [user] integer
    , [type] char(2)
    , [data] char(1)
)


insert into users 
values (1, 'T1', 'A')
    , (1, 'T1', 'B')
    , (1, 'T2', 'C')
    , (2, 'T1', 'D')

select DISTINCT ONE.[user], two.[type], three.[data] 
from users AS ONE
inner join users two
    on one.[user] = two.[user]
inner join users three
    on one.[user] = three.[user]
    and two.[type] = three.[type]
for JSON AUTO

Answer 2

我不确定您是否可以使用FOR JSON AUTO和FOR JSON PATH创建具有可变键名称的JSON。我建议以下解决方案：

• 使用XML路径生成JSON，并进行字符串操作

表：

CREATE TABLE #InputData (
   [User] int,
   [Type] varchar(2),
   [Data] varchar(1)
)
INSERT INTO #InputData 
   ([User], [Type], [Data])
VALUES
   (1, 'T1', 'A'),
   (1, 'T1', 'B'),
   (1, 'T2', 'C'),
   (2, 'T1', 'D')

对XML路径使用的语句：

;WITH SecondLevelCTE AS (
   SELECT 
      d.[User],
      d.[Type],
      Json1 = CONCAT(
         '[', 
         STUFF(
         (
         SELECT CONCAT(',"', [Data], '"')
         FROM #InputData 
         WHERE [User] = d.[User] AND [Type] = d.[Type]
         FOR XML PATH('')
         ), 1, 1, ''),
         ']')
   FROM #InputData d
   GROUP BY d.[User], d.[Type]
), FirstLevelCTE AS (
   SELECT 
      d.[User],
      Json2 = CONCAT(
         '{',
         STUFF(
         (
         SELECT CONCAT(',"', [Type], '":', [Json1])
         FROM SecondLevelCTE 
         WHERE [User] = d.[User]
         FOR XML PATH('')
         ), 1, 1, ''),
         '}'
      )
   FROM SecondLevelCTE d
   GROUP BY d.[User]
)
SELECT CONCAT(
   '{',
   STUFF(
   (
   SELECT CONCAT(',"', [User], '":', Json2)
   FROM FirstLevelCTE
   FOR XML PATH('')
   ), 1, 1, '')   ,
   '}'
)

使用字符串AGG（）的语句：

;WITH SecondLevelCTE AS (
   SELECT 
      d.[User],
      d.[Type],
      Json1 = (
         SELECT CONCAT('["', STRING_AGG([Data], '","'), '"]')
         FROM #InputData 
         WHERE [User] = d.[User] AND [Type] = d.[Type]
      )
   FROM #InputData d
   GROUP BY d.[User], d.[Type]
), FirstLevelCTE AS (
   SELECT 
      d.[User],
      Json2 = (
         SELECT STRING_AGG(CONCAT('"', [Type], '":', [Json1]), ',')
         FROM SecondLevelCTE
         WHERE [User] = d.[User]
      )
   FROM SecondLevelCTE d
   GROUP BY d.[User]
)
SELECT CONCAT('{', STRING_AGG(CONCAT('"', [User], '":{', Json2, '}'), ','), '}')
FROM FirstLevelCTE

使用STRING_AGG（）和JSON_MODIFY（）的语句：

DECLARE @json nvarchar(max) = N'{}'
SELECT 
   @json = JSON_MODIFY(
      CASE 
         WHEN JSON_QUERY(@json, CONCAT('$."', [User] , '"')) IS NULL THEN JSON_MODIFY(@json, CONCAT('$."', [User] , '"'), JSON_QUERY('{}'))
         ELSE @json
      END,
      CONCAT('$."', [User] , '".', [Type]), 
      JSON_QUERY(Json)
   )
FROM (
   SELECT 
      d.[User],
      d.[Type],
      Json = (
         SELECT CONCAT('["', STRING_AGG([Data], '","'), '"]')
         FROM #InputData 
         WHERE [User] = d.[User] AND [Type] = d.[Type]
      )
   FROM #InputData d
   GROUP BY d.[User], d.[Type]
) t

输出：

{"1":{"T1":["A","B"],"T2":["C"]},"2":{"T1":["D"]}}