如何从带层级关联的数据库用户设置表生成嵌套JSON？

我把频繁变更且用户专属的设置存在层级结构的数据库表中，表结构和测试数据如下：

DECLARE @tem AS TABLE
(
    id INT,
    Person VARCHAR(100),
    JLabel NVARCHAR(100),
    JValue NVARCHAR(200),
    Parent INT
);

INSERT @tem
(
    id,
    Person,
    JLabel,
    JValue,
    Parent
)
VALUES
(1,'arun', 'Area', '250', NULL),
(2,'arun', 'brder', NULL, NULL),
(3,'arun', 'width', '5', 2),
(4,'arun', 'marker', NULL, NULL),
(5,'arun', 'dataLabel', NULL, 4),
(6,'arun', 'visible', '1', 5),
(7,'arun', 'position', 'Top', 5),
(8,'arun', 'font', NULL, 5),
(9,'arun', 'fontWeight', '600', 8),
(10,'arun', 'color', '#ffffff', 8);

需要生成如下嵌套JSON输出：

{"Area":"250","brder":{"width":"5"},"marker": { "dataLabel": { "visible": "1", "position": "Top", "font": { "fontWeight": "600", "color": "#ffffff" } } }}

其中width是brder的子元素，因为它的Parent字段值等于brder的id（2）。以下是实现方案：

解决方案 #

利用SQL Server的递归CTE构建完整的节点层级关系，再结合FOR JSON PATH生成嵌套JSON：

WITH RecursiveSettings AS (
    -- 锚点成员：获取所有根节点（Parent为NULL）
    SELECT 
        id,
        Person,
        JLabel,
        JValue,
        Parent,
        CAST(JLabel AS NVARCHAR(MAX)) AS NodePath,
        CASE WHEN JValue IS NOT NULL THEN 1 ELSE 0 END AS IsLeaf
    FROM @tem
    WHERE Parent IS NULL

    UNION ALL

    -- 递归成员：获取子节点，拼接路径
    SELECT 
        child.id,
        child.Person,
        child.JLabel,
        child.JValue,
        child.Parent,
        CAST(parent.NodePath + '.' + child.JLabel AS NVARCHAR(MAX)) AS NodePath,
        CASE WHEN child.JValue IS NOT NULL THEN 1 ELSE 0 END AS IsLeaf
    FROM @tem child
    JOIN RecursiveSettings parent ON child.Parent = parent.id
)
-- 生成JSON：利用路径映射层级，去掉默认根节点和数组包裹
SELECT 
    CASE 
        WHEN IsLeaf = 1 THEN JSON_QUERY('"' + JValue + '"')
        ELSE NULL
    END AS [value],
    NodePath AS [key]
FROM RecursiveSettings
FOR JSON PATH, ROOT(''), WITHOUT_ARRAY_WRAPPER;

代码说明 #

1. 递归CTE：从根节点开始逐层遍历所有子节点，为每个节点生成完整的层级路径（比如marker.dataLabel.visible），以此标记节点在JSON中的嵌套位置。
2. JSON生成逻辑：
   • 叶子节点（有JValue）直接输出键值对；
   • 非叶子节点（无JValue）自动作为嵌套对象的容器；
   • FOR JSON PATH会根据生成的节点路径自动拼接嵌套结构，WITHOUT_ARRAY_WRAPPER确保输出是单个JSON对象而非数组。

执行上述脚本后，即可得到预期的嵌套JSON结果。

内容的提问来源于stack exchange，提问作者Arun K Pushpakaran