时间戳

首页 > 解决方案 > 如何在 T-SQL 中生成嵌套的第 n 级 JSON 对象？

json - 如何在 T-SQL 中生成嵌套的第 n 级 JSON 对象？

问题描述

我有以下数据

在此处输入图像描述

针对以下查询

declare @t table
(
    Id int identity,
    name varchar(50),
    rootid int,
    level int
);


insert into @t(name, rootid, level)
values
('Home', 0, 0)
,('Transaction', 0, 0)
, ('Settings', 0, 0)
,('Purchase Request', 2, 1)
,('Purchase Order', 2, 1)
,('Inventory', 2, 1)
,('Payment Advice', 2, 1)
,('Setup', 3, 1)
,('Budget', 3, 1)
,('CRC', 3, 1)
,('Create PR', 4, 3);

select * from @t;

期望输出：

[{
    "Id": 1,
    "name": "Home",
    "rootid": 0,
    "level": 0
}, {
    "Id": 2,
    "name": "Transaction",
    "rootid": 0,
    "level": 0,
    "children": [{
        "Id": 4,
        "name": "Purchase Request",
        "rootid": 2,
        "level": 1,
        "children": [{
            "Id": 11,
            "name": "Create PR",
            "rootid": 4,
            "level": 3
        }]
    }, {
        "Id": 5,
        "name": "Purchase Order",
        "rootid": 2,
        "level": 1
    }, {
        "Id": 6,
        "name": "Inventory",
        "rootid": 2,
        "level": 1
    }, {
        "Id": 7,
        "name": "Payment Advice",
        "rootid": 2,
        "level": 1
    }]
}, {
    "Id": 3,
    "name": "Settings",
    "rootid": 0,
    "level": 0,
    "children": [{
        "Id": 8,
        "name": "Setup",
        "rootid": 3,
        "level": 1
    }, {
        "Id": 9,
        "name": "Budget",
        "rootid": 3,
        "level": 1
    }, {
        "Id": 10,
        "name": "CRC",
        "rootid": 3,
        "level": 1
    }]
}]

还尝试了@Iptr 答案：

;WITH result (id, name, rootId, parent, Level) AS
(
    SELECT  id, 
            name,
            RootId,
            Id as Parent,
            0 as Level
    FROM    @t
    WHERE   RootId= 0
    UNION ALL
    SELECT  t.id, 
            t.Name,
            t.RootId,
            r.Parent,
            r.Level + 1
    FROM @t t
    INNER JOIN result r ON r.id = t.RootId 
)
SELECT  t.*, json_query(nullif(c.children, '[{}]')) as children
FROM @t as t
outer apply (
select 
(
select r.*
from result as r
where r.parent = t.Id
and r.level > 0
order by r.id
for json auto
) as children
) as c
where t.level = 0
order by t.Level
for json auto;

输出

[{
    "Id": 1,
    "name": "Home",
    "rootid": 0,
    "level": 0
}, {
    "Id": 2,
    "name": "Transaction",
    "rootid": 0,
    "level": 0,
    "children": [{
        "id": 4,
        "name": "Purchase Request",
        "rootId": 2,
        "parent": 2,
        "Level": 1
    }, {
        "id": 5,
        "name": "Purchase Order",
        "rootId": 2,
        "parent": 2,
        "Level": 1
    }, {
        "id": 6,
        "name": "Inventory",
        "rootId": 2,
        "parent": 2,
        "Level": 1
    }, {
        "id": 7,
        "name": "Payment Advice",
        "rootId": 2,
        "parent": 2,
        "Level": 1
    }, {
        "id": 11,
        "name": "Create PR",
        "rootId": 4,
        "parent": 2,
        "Level": 2
    }]
}, {
    "Id": 3,
    "name": "Settings",
    "rootid": 0,
    "level": 0,
    "children": [{
        "id": 8,
        "name": "Setup",
        "rootId": 3,
        "parent": 3,
        "Level": 1
    }, {
        "id": 9,
        "name": "Budget",
        "rootId": 3,
        "parent": 3,
        "Level": 1
    }, {
        "id": 10,
        "name": "CRC",
        "rootId": 3,
        "parent": 3,
        "Level": 1
    }]
}]

上面的查询没有返回第 n 个 json 子对象，假设我有第 n 级菜单项，Parent 有多个 Childs，Childs 有多个 Childs，比如 treeview。

试过@Naveen Arora 答案：

select ID,name,'' as id,'' as name from Navigations where id not in (select rootid from Navigations) and rootid=0
union
select B.id,B.name,A.id,A.name from Navigations A join Navigations B on A.rootid=B.id
FOR JSON AUTO;

但是输出

[{
"ID": 1,
"name": "Home",
"id": 0,
"name": ""
}, {
    "ID": 2,
    "name": "Transaction",
    "id": 4,
    "name": "Create PR"
}, {
    "ID": 2,
    "name": "Transaction",
    "id": 5,
    "name": "Generate PO"
}, {
    "ID": 2,
    "name": "Transaction",
    "id": 6,
    "name": "Create Receipt"
}, {
    "ID": 2,
    "name": "Transaction",
    "id": 7,
    "name": "Create Issue Request"
}, {
    "ID": 2,
    "name": "Transaction",
    "id": 8,
    "name": "Create Issue Note"
}, {
    "ID": 2,
    "name": "Transaction",
    "id": 9,
    "name": "Approve Payment Advice"
}, {
    "ID": 3,
    "name": "Settings",
    "id": 11,
    "name": "Navigation Management"
}, {
    "ID": 11,
    "name": "Navigation Management",
    "id": 12,
    "name": "Navigation & Form Mapping"
}]

上面的输出不包括 Childs 节点。就像在设置中一样，我有导航管理-> 导航和表单映射

标签： jsonsql-serverrecursion

解决方案

如果 sql server 版本是2016或更新，2016那么您可以使用FOR JSON PATH.

假设结果存储在测试表中。这只是为了让您了解如何执行此操作，可能不会为您提供确切的输出，但您可以根据您的要求进行更改。

SELECT
t.Id AS 'Id',
t.Name AS 'Name',
children = (
    SELECT A.id,A.name from test A join test B on A.rootid=B.id
    FOR JSON PATH
)
FROM Test t
FOR JSON PATH;

如果它比2016那么旧，你可以参考这个。

推荐阅读