用PLJSON读取JSON_LIST of JSON_LIST

Question

我正在尝试读取一个有嵌套列表的JSON对象。看起来是这样的：

[{
    "id": 70070037001,
    "text": "List 1",
    "isleaf": 0,
    "children": [
        {
            "oid": 100,
            "text": "Innerlistobject100",
            "isleaf": 0,
            "children": [
                {
                    "sid": 1000,
                    "text": "Innerlistobject1000",
                    "isleaf": 1
                },
                {
                    "sid": 2000,
                    "text": "Innerlistobject2000",
                    "isleaf": 1
                }
            ]
        },
        {
            "oid": 200,
            "text": "Innerlistobject200",
            "isleaf": 0,
            "children": [
                {
                    "sid": 1000,
                    "text": "Innerlistobject1000",
                    "isleaf": 1
                },
                {
                    "sid": 2000,
                    "text": "Innerlistobject2000",
                    "isleaf": 1
                }
            ]
        }
    ]
}]

ref：https://sourceforge.net/p/pljson/discussion/935365/thread/375c0293/ --在这里，这个人正在创建对象，但是我想做相反的事情，然后阅读它。

我是否必须像这样迭代(注意，名字是孩子中的孩子)：

Declare
    l_Children_List                json_list;
    JSON_Obj                     json;
    l_Child_JSON_Obj                     json;
Begin
IF (JSON_Obj.exist ('children')) THEN
    IF (JSON_Obj.get ('children').is_array)
    l_Children_List := json_list (JSON_Obj.get ('children'));
    FOR i IN 1 .. l_Children_List.COUNT

        IF (JSON_Obj.exist ('children')) THEN
            IF (JSON_Obj.get ('children').is_array)
            l_Children_List := json_list (JSON_Obj.get ('children'));
            FOR i IN 1 .. l_Children_List.COUNT

                jSON_child_val := l_Children_List.get (i);
                l_Child_JSON_Obj := json (jSON_child_val );

            LOOP
        End If;

    LOOP
End If;
End;

Answer 1

with json_example as (
select '{
    "id": 70070037001,
    "text": "List 1",
    "isleaf": 0,
    "children": [
        {
            "oid": 100,
            "text": "Innerlistobject100",
            "isleaf": 0,
            "children": [
                {
                    "sid": 1000,
                    "text": "Innerlistobject1000",
                    "isleaf": 1
                },
                {
                    "sid": 2000,
                    "text": "Innerlistobject2000",
                    "isleaf": 1
                }
            ]
        },
        {
            "oid": 200,
            "text": "Innerlistobject200",
            "isleaf": 0,
            "children": [
                {
                    "sid": 1000,
                    "text": "Innerlistobject1000",
                    "isleaf": 1
                },
                {
                    "sid": 2000,
                    "text": "Innerlistobject2000",
                    "isleaf": 1
                }
            ]
        }
    ]
}' as json_document 
from dual
)
SELECT tab.*
            FROM json_example a
             join json_table (a.json_document, '$'
                               COLUMNS
                               (id                  NUMBER PATH '$.id'
                               ,text                VARCHAR2(50) PATH '$.text'
                               ,isleaf              NUMBER PATH '$.isleaf'
                               ,NESTED PATH '$.children[*]'
                                COLUMNS
                                   (oid         NUMBER  PATH '$.oid'
                                   ,otext         VARCHAR2(150) PATH '$.text'
                                   ,oisleaf      NUMBER PATH '$.isleaf'
                                   ,NESTED PATH '$.children[*]'
                                    COLUMNS
                                       (sid       NUMBER  PATH '$.sid'
                                       ,stext      VARCHAR2(250)  PATH '$.text'
                                       ,sisleaf    NUMBER  PATH '$.isleaf'
                                       )
                                   )
                              )
                            ) tab on 1=1