SQL Server - 使用cross apply或openjson等递归获取嵌套JSON数组

Question

我一直在阅读尽可能多的有关同一个问题的问题和答案，但我想我的问题需要更具创造性的方法。

所以这里有一个JSON字符串：

declare @json nvarchar(max) =
'{
    "propertyObjects": [{
        "propertyID": 1
        , "title": "foo"
        , "class": ""
        , "typeid": 150
        , "value": "bar"
        , "children": [{}]
    }, {
        "propertyID": 2
        , "title": "foo"
        , "class": ""
        , "typeid": 128
        , "value": "bar"
        , "children": [{}]
    }, {
        "propertyID": 3
        , "title": "foo"
        , "class": ""
        , "typeid": 128
        , "value": "bar"
        , "children": [{
            "propertyID": 4
            , "title": "foo"
            , "class": ""
            , "typeid": 128
            , "value": "bar"
            , "children": [{}]
        }, {
            "propertyID": 5
            , "title": "foo"
            , "class": ""
            , "typeid": 128
            , "value": "bar"
            , "children": [{}]
        }, {
            "propertyID": 6
            , "title": "foo"
            , "class": ""
            , "typeid": 128
            , "value": "bar"
            , "children": [{
                "propertyID": 7
                , "title": "foo"
                , "class": ""
                , "typeid": 128
                , "value": "bar"
                , "children": [{
                    "propertyID": 8
                    , "title": "foo"
                    , "class": ""
                    , "typeid": 128
                    , "value": "bar"
                    , "children": [{}]
                }]
            }]
        }]
    }]
}'

乍一看很疯狂，但是这样想：
有一个名为propertyObjects的数组，其中包含多个父子结构对象。
在每个级别中，只能有一个对象作为父级。正如您所看到的，对象3内部有子元素。

我想要的是将这些对象列在表格中，同时为它们指定一个parentID，因此对象4具有ID为3的父级，而对象3本身则具有ID为0的父级，因为它基本上处于顶层。

到目前为止，我尝试了一些方法，例如使用公共表达式（Common Table Expression）进行递归调用，但我失败了：

;with cte 
as
(
    -- anchor member definition
    select p.propertyID
        , 0 as parentID
        , p.title
        , p.typeid
        , p.[value]
        , p.children
    from openjson(@json, '$.propertyObjects')
    with (
        propertyID int
        , title nvarchar(100)
        , typeid int
        , [value] nvarchar(1000)
        , children nvarchar(max) as JSON
    ) as p

    UNION ALL

    -- recursive member definition
    select 0 as propertyID
        , 0 as parentID
        , '' as title
        , 0 typeid
        , '' as [value]
        , '' as children
    /** child should be bound to parent **/
)
select * from cte

这是我失败的地方，我不知道如何通过递归查找子对象。而且，我也不知道如何指定每个子对象的父ID！

propertyID    parentID    title    typeid    value    children
----------------------------------------------------------------------------
1             0           foo      150       bar      [{}]
2             0           foo      128       bar      [{}]
3             0           foo      128       bar      [{ "propertyID" : 4 ...
0             0                    0   

我还尝试了使用cross apply：

select *
from 
    openjson(@json, '$.propertyObjects')
    with (
        propertyID int
        , title nvarchar(100)
        , typeid int
        , [value] nvarchar(1000)
        , children nvarchar(max) as JSON
    ) as p
cross apply
    openjson(p.children)
    with (
        propertyID int
        , title nvarchar(100)
        , typeid int
        , [value] nvarchar(1000)
        , children nvarchar(max) as JSON
    ) as r

但是没有机会，我不知道这些孩子会在JSON字符串中深入多少。此外，交叉应用的结果将追加列而不是行，这会导致结果中出现一个巨大的表，在这种方法中，我甚至无法考虑指定parentIDs。

这完全是失败的，有什么想法可以让所有子项都以行的形式显示在表格中？

所需表格

propertyID    parentID    title    typeid    value
--------------------------------------------------
1             0           foo      150       bar
2             0           foo      128       bar
3             0           foo      128       bar
4             3           foo      128       bar
5             3           foo      128       bar
6             3           foo      128       bar
7             6           foo      128       bar
8             7           foo      128       bar

- Brian Salehi

---

我不确定，但你是否尝试从JSON文件导入数据？如果是这样，你可能想探索其他选项，比如使用PowerShell。我猜你已经阅读了这个页面：https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server ，但为了确保，我在这里提一下。 - ksauter

ksauter，感谢您的提醒，是的，我已经阅读了所有页面，但是阅读这个链接让我想起了使用内存优化表，虽然这与我的问题无关，但它可以帮助我更加优化地使用答案。答案将比仅使用内置函数复杂得多。 - Brian Salehi

它必须是存储过程吗？使用 SSIS 是一个选项吗？ - ksauter

SSIS对我来说不是一个选择，但可以使用存储过程来解决这个问题。 - Brian Salehi

Answer 1

您实际上已经非常接近了 - 您只需要使用CROSS APPLY与OPENJSON一起使用:

with cte as (
    select
        p.propertyID,
        0 as parentID,
        p.title,
        p.typeid,
        p.[value],
        p.children
    from openjson(@json, '$.propertyObjects') with (
        propertyID int,
        title nvarchar(100),
        typeid int,
        [value] nvarchar(1000),
        children nvarchar(max) as json
    ) as p

    union all

    select
        p.propertyID,
        c.propertyID,
        p.title,
        p.typeid,
        p.[value],
        p.children  
    from cte as c
        cross apply openjson(c.children) with (
            propertyID int,
            title nvarchar(100),
            typeid int,
            [value] nvarchar(1000),
            children nvarchar(max) as json
        ) as p
    where
        c.children <> '[{}]'
)
select
    c.propertyID,
    c.parentID,
    c.title,
    c.typeid,
    c.value
from cte as c

SQL Fiddle演示

- Roman Pekar

---

哇，自从我上次使用 Microsoft SQL Server 工作以来已经很长时间了 :)) 但是我检查过了，它正在工作！感谢您的精彩答案。 - Brian Salehi

Answer 2

就我在工作中所了解的，钻研未知嵌套JSON级别而不降低性能几乎是不可能的。

我的做法是避免嵌套级别，我创建了单层的JSON字符串并简单地返回所有对象及其内部的键值对，并且还更改了数据库中的数据模型。

拥有有效合理的数据结构比拥有复杂编码技能更加重要。

- Brian Salehi