Oracle Reading a JSON_LIST of JSON_LIST using PLJSON

Question

I'm trying to read a JSON object with has nested lists. Which looks like this:

[{
    "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/ - where the person is creating the object, but I want to do the opposite and read it.

Do I have to iterate like this (note name is children within children):

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;

pl/json is limited by the crappy language it's a library for. So, yeah, you have to do excessive work for simple tasks. — James Sumners, May 05 '16 at 12:42

veronika · Answer 1 · 2022-06-22T13:53:48.240

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

Oracle Reading a JSON_LIST of JSON_LIST using PLJSON

1 Answers1