Início / dba / Perguntas / 239180
Accepted
Michael Green
Asked: 2019-05-28 22:57:18 +0800 CST

Encontrar ancestralidade de JSON

  • 772

Eu tenho uma hierarquia que se parece com isso:

insira a descrição da imagem aqui

Como JSON no TSQL é isso:

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

Este é apenas um exemplo. Os dados reais podem ser qualquer árvore de profundidade ou amplitude indeterminada.

A documentação e todos os exemplos que encontrei mostram a travessia de cima para baixo . Cada caminho JSON começa no nó raiz e navega, por nomes de nós conhecidos, em direção ao nó desejado. Não encontrei nada sobre começar em uma profundidade indeterminada dentro da hierarquia e trabalhar para cima. Sinto que preciso de algo como fechamento transitivo .

Dado um nome, gostaria de obter a ascendência para esse nome. Por exemplo, dado "Emma" o resultado será "Emma / Chris / Amy". Dado "Bill" a resposta será "Bill/Amy". O formato de saída não é importante; pode ser JSON, uma string ou um conjunto de resultados. Os nomes são únicos.

Este é um exercício de aprendizagem para mim. Não há problema em alterar a representação JSON original para algo equivalente, desde que ainda seja JSONy. Um JSON_QUERY sobre uma representação de lista de adjacências da hierarquia não atingiria meu objetivo.

sql-server t-sql

2 respostas

  1. Best Answer

    Isso me parece ser uma consulta de recursão bastante simples, desde que você desembrulhe o JSON dinamicamente.

    db<>fiddle
    anterior, db mais simples<>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);

    Executar em linha ou agrupado em uma função , você pode analisar o caminho JSON para cada nó com um caminho pai correspondente.

    select * 
from dbo.json_shred(@Employees)
where [Type] not in (4,5);
    Nível Pai Chave Valor Modelo
    1 $. $.pessoa Amy 1
    3 $.staff[1] $.staff[1].person Chris 1
    5 $.staff[1].staff[1] $.staff[1].staff[1].person Emma 1
    5 $.staff[1].staff[0] $.staff[1].staff[0].person Dan 1
    3 $.staff[0] $.staff[0].person Conta 1
    • 7

  2. Você pode usar OPENJSON e um CTE para extrair cada pessoa e sua equipe associada:

    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

    Devoluções:

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

relate perguntas