SQL Server如何构造查询生成按姓、名嵌套的JSON数组

环境与基础数据 #

当前使用环境为 Microsoft SQL Server Standard Version 13、Microsoft SQL Server Management Studio 18，测试表Table1样例数据如下：

+----------+-----------+-----+--------+---------+---------+
| LastName | Firstname | Age | Weight | Sallery | Married |
+----------+-----------+-----+--------+---------+---------+
| Smith    | Stan      |  58 |     87 |  59.000 | true    |
| Smith    | Maria     |  53 |     57 |  45.000 | true    |
| Brown    | Chris     |  48 |     77 | 159.000 | true    |
| Brown    | Stepahnie |  39 |     67 |  95.000 | true    |
| Brown    | Angela    |  12 |     37 |     0.0 | false   |
+----------+-----------+-----+--------+---------+---------+

预期输出 #

需要生成如下结构的嵌套JSON数组：

[
    {
        "Smith": [
            {
                "Stan": [
                    {
                        "Age": 58,
                        "Weight": 87,
                        "Sallery": 59.000,
                        "Married": true
                    }
                ],
                "Maria": [
                    {
                        "Age": 53,
                        "Weight": 57,
                        "Sallery": 45.000,
                        "Married": true
                    }
                ]
            }
        ],
        "Brown": [
            {
                "Chris": [
                    {
                        "Age": 48,
                        "Weight": 77,
                        "Sallery": 159.000,
                        "Married": true
                    }
                ],
                "Stepahnie": [
                    {
                        "Age": 39,
                        "Weight": 67,
                        "Sallery": 95.000,
                        "Married": true
                    }
                ],
                "Angela": [
                    {
                        "Age": 12,
                        "Weight": 37,
                        "Sallery": 0.0,
                        "Married": false
                    }
                ]
            }
        ]
    }
]

问题说明 #

原生FOR JSON语法不支持直接将列值作为动态键名，尝试的两种写法均存在问题：

1. 第一种写法仅能实现FirstName单一层级嵌套，缺少LastName外层结构，代码如下：

   WITH cte AS
   (
          SELECT FirstName
                 js = json_query(
                 (
                        SELECT Age,
                               Weight,
                               Sallery,
                   Married
                                FOR json path,
                               without_array_wrapper ) )
          FROM   Table1)
   SELECT '[' + stuff(
          (
                   SELECT   '},{"' + FirstName + '":' + '[' + js + ']'
                   FROM     cte
                    FOR xml path ('')), 1, 2, '') + '}]'
   

2. 第二种写法无法生成动态键名，代码如下：

   SELECT
     LastName ,json  
   FROM Table1 as a
   OUTER APPLY (
     SELECT
       FirstName
     FROM Table1 as b
     WHERE a.LastName = b.LastName
     FOR JSON PATH
   ) child(json)
   FOR JSON PATH
   

可行方案 #

通过三层CTE从内到外逐层拼接JSON，适配SQL Server 2016（Version 13）版本，无需高版本函数支持：

WITH PersonLevel AS (
    -- 构造单个人的属性JSON对象
    SELECT 
        LastName,
        FirstName,
        PersonJson = (
            SELECT Age, Weight, Sallery, Married
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        )
    FROM Table1
),
FirstNameLevel AS (
    -- 按姓氏分组，拼接同姓氏下所有名字对应的键值对
    SELECT 
        LastName,
        LastNameGroupJson = (
            SELECT '{' + STUFF((
                SELECT ',"' + FirstName + '":[' + PersonJson + ']'
                FROM PersonLevel p2
                WHERE p2.LastName = p1.LastName
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') + '}'
        )
    FROM PersonLevel p1
    GROUP BY LastName
)
-- 拼接最外层姓氏键值对，包装根数组
SELECT '[' + STUFF((
    SELECT ',{"' + LastName + '":[' + LastNameGroupJson + ']}'
    FROM FirstNameLevel
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '') + ']' AS NestedJson

写法说明 #

• 从最内层个人属性开始逐层向外拼接，避免根节点重复问题
• 拼接时使用TYPE参数加.value()方法读取XML内容，自动处理特殊字符转义，避免JSON格式错误
• 所有函数均为SQL Server 2016原生支持，无需额外升级或安装插件

内容的提问来源于stack exchange，提问作者Mec-Eng