主页 / dba / 问题 / 239180
Accepted
Michael Green
Asked: 2019-05-28 22:57:18 +0800 CST

从 JSON 中查找祖先

  • 772

我有一个看起来像这样的层次结构：

在此处输入图像描述

作为 TSQL 中的 JSON，它是这样的：

declare @Employees nvarchar(max) =
'{
  "person": "Amy",
  "staff": [
    { "person": "Bill" },
    {
      "person": "Chris",
      "staff": [
        { "person": "Dan" },
        { "person": "Emma" }
      ]
    }
  ]
}';

这只是一个例子。实际数据可以是任何深度或宽度不确定的树。

我发现的文档和所有示例都显示了自上而下的遍历。每个 JSON 路径都从根节点开始，并通过已知节点名称导航到所需节点。我没有发现从层次结构中的不确定深度开始向上工作。我觉得我需要像传递闭包这样的东西。

给定一个名字，我想得到这个名字的祖先。例如，给定“Emma”，结果将是“Emma / Chris / Amy”。给定“Bill”，答案将是“Bill / Amy”。输出格式不重要；它可以是 JSON、字符串或结果集。名字是独一无二的。

这是我自己的学习练习。可以将原始 JSON 表示更改为任何等效的表示，只要它仍然是 JSONy。在层次结构的邻接列表表示上的 JSON_QUERY 不会达到我的目标。

sql-server t-sql

2 个回答

  1. Best Answer

    在我看来，这似乎是一个非常普通的递归查询，只要您动态地解开 JSON。

    db<>fiddle
    以前，更简单的 db<>fiddle

    declare @json nvarchar(max) =
'{
  "person": "Amy",
  "staff": [
    { "person": "Bill" },
    {
      "person": "Chris",
      "staff": [
        { "person": "Dan" },
        { "person": "Emma" }
      ]
    }
  ]
}';

with level_0 as (
    select
        convert(int,0) as [Level],
        convert(nvarchar(4000),N'$') as [Key],
        @json as [Value],
        convert(
            int,
            case left(@json,1)
                when N'[' then 4
                when N'{' then 5
                else 0
            end
        ) as [Type]
)
, key_value_unwrap as(
    select 
        l0.[Level] + 1 as [Level],
        convert(nvarchar(max),null) as Parent,
        l0.[Key] + iif(l0.[Type] = 5, '.' + oj.[Key], quotename(-1 + row_number() over (order by (select null)))) collate database_default as [Key],
        oj.[Value],
        oj.[Type]
    from level_0 l0
    outer apply openjson(l0.[Value]) as oj
    where l0.[Value] is not null 
    union all
    select 
        kvu.[Level] + 1 as [Level],
        convert(nvarchar(max),kvu.[Key]) as Parent,
        kvu.[Key] + iif(kvu.[Type] = 5, '.' + oj.[Key], quotename(-1 + row_number() over (order by (select null)))) as [Key],
        oj.[Value],
        oj.[Type]
    from key_value_unwrap as kvu
    outer apply openjson(kvu.[Value], 'lax $') as oj
    where kvu.[Type] in (4,5)
), _union as (
    select 
        l0.[Level],
        convert(nvarchar(max),null) as Parent,
        l0.[Key] + N'.' as [Key],
        l0.[Value],
        l0.[Type]
    from level_0 as l0
    union all
    select 
        kvu.[Level],
        kvu.Parent,
        kvu.[Key],
        kvu.[Value],
        kvu.[Type]
    from key_value_unwrap as kvu
) 
select 
    u.[Level],
    iif(u.[Level]=1,N'$.',u.Parent) as Parent,
    u.[Key],
    u.[Value],
    u.[Type]
from _union as u
where u.[Type] not in (4,5);

    内联或捆绑到函数中运行，您可以解析出每个节点的 JSON 路径以及相应的父路径。

    select * 
from dbo.json_shred(@Employees)
where [Type] not in (4,5);
    等级 家长 钥匙 价值 类型
    1 美元。 $.person 艾米 1
    3 $.员工[1] $.staff[1].person 克里斯 1
    5 $.staff[1].staff[1] $.staff[1].staff[1].person 艾玛 1
    5 $.staff[1].staff[0] $.staff[1].staff[0].person 担 1
    3 $.staff[0] $.staff[0].person 账单 1
    • 7

  2. 您可以使用OPENJSON和 CTE 来提取每个人及其相关人员：

    declare @Employees nvarchar(max) =
'{
  "person": "Amy",
  "staff": [
    { "person": "Bill" },
    {
      "person": "Chris",
      "staff": [
        { "person": "Dan" },
        { "person": "Emma" }
      ]
    }
  ]
}';

;WITH Level1 AS (
    SELECT 1 AS Level, NULL AS Parent, l1.person, l1.staff
    FROM OPENJSON(@Employees)
    WITH
    (
        person NVARCHAR(255),
        staff NVARCHAR(MAX) AS JSON
    ) l1
), Level2 AS (
    SELECT 2 AS Level, l1.person AS Parent, l2.person, l2.staff
    FROM Level1 l1
    CROSS APPLY OPENJSON(l1.staff)
    WITH
    (
        person NVARCHAR(255),
        staff NVARCHAR(MAX) AS JSON
    ) l2
), Level3 AS (
    SELECT 3 AS Level, l2.person AS Parent, l3.person, l3.staff
    FROM Level2 l2
    CROSS APPLY OPENJSON(l2.staff)
    WITH
    (
        person NVARCHAR(255),
        staff NVARCHAR(MAX) AS JSON
    ) l3
)

SELECT Level, Parent, Person FROM Level1
UNION
SELECT Level, Parent, Person FROM Level2
UNION
SELECT Level, Parent, Person FROM Level3
ORDER BY Level

    回报：

    Level   Parent  Person
----------------------
1       NULL    Amy
2       Amy     Bill
2       Amy     Chris
3       Chris   Emma
3       Chris   Dan
    • 3

相关问题