SQL Server：如何单查询生成带新增层级的JSON并封装为函数

原查询语句 #

Nodes 查询 #

select * from
    (
        SELECT Person.id, Person.name as label, 'Person' as 'group', Person.id as value
        FROM Person, likes, Restaurant
        WHERE MATCH (Person-(likes)->Restaurant)
    ) 
    t FOR JSON PATH,
    ROOT('nodes')

Edges 查询（原查询存在语法错误，已修正） #

SELECT pe.id as 'from', re.id as 'to', 
    CAST(pe.id AS NVARCHAR(12)) + N'=>' + CAST(re.id AS NVARCHAR(12)) as label,
    'to' as arrows
FROM person pe, likes li, restaurant re
WHERE MATCH (pe-(li)->re)
FOR JSON PATH, ROOT('edges')

目标JSON格式 #

{
  "name": "Nodes",
  "nodes": [
    {"id":1,"label":"John","group":"Person","value":1},
    {"id":2,"label":"Mary","group":"Person","value":2},
    {"id":3,"label":"Alice","group":"Person","value":3}
  ],
  "edges": [
    {"from":1,"to":1,"label":"1=>1","arrows":"to"},
    {"from":2,"to":2,"label":"2=>2","arrows":"to"}
  ]
}

实现方案：创建返回合并JSON的标量函数 #

步骤1：创建函数 #

CREATE FUNCTION dbo.GetGraphData()
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @nodesJson NVARCHAR(MAX), @edgesJson NVARCHAR(MAX), @resultJson NVARCHAR(MAX)

    -- 获取nodes数据的JSON
    SELECT @nodesJson = (
        SELECT Person.id, Person.name as label, 'Person' as [group], Person.id as value
        FROM Person, likes, Restaurant
        WHERE MATCH (Person-(likes)->Restaurant)
        FOR JSON PATH, ROOT('nodes')
    )

    -- 获取edges数据的JSON（修正原查询的label拼接错误）
    SELECT @edgesJson = (
        SELECT pe.id as [from], re.id as [to], 
            CAST(pe.id AS NVARCHAR(12)) + N'=>' + CAST(re.id AS NVARCHAR(12)) as label,
            'to' as arrows
        FROM person pe, likes li, restaurant re
        WHERE MATCH (pe-(li)->re)
        FOR JSON PATH, ROOT('edges')
    )

    -- 提取nodes和edges数组，避免嵌套重复
    SET @nodesJson = JSON_QUERY(@nodesJson, '$.nodes')
    SET @edgesJson = JSON_QUERY(@edgesJson, '$.edges')

    -- 合并为包含name层级的最终JSON
    SET @resultJson = (
        SELECT 'Nodes' as name,
               @nodesJson as nodes,
               @edgesJson as edges
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    )

    RETURN @resultJson
END
GO

步骤2：调用函数获取结果 #

SELECT dbo.GetGraphData() AS GraphData

关键说明 #

• 修正了原Edges查询中label字段的语法错误：原代码将CAST(re.id AS NVARCHAR(12))写在单引号内，导致变成固定字符串，改为实际的类型转换拼接。
• 使用JSON_QUERY提取nodes和edges数组，避免合并时出现额外的嵌套层级。
• 通过WITHOUT_ARRAY_WRAPPER确保最终结果是单个JSON对象而非数组。

内容的提问来源于stack exchange，提问作者ZedZip