oracle 19, json query, with arbitrary json data

Question

I need to do this with Oracle 19c.

I have arbitrary json data in a column of a table. I mean, I dont know the names of attributes, not even the deep the data is... Suppose that I can identify the json objects in witch I am interested because all of them have an "id", "type", "text", and "call" attributes, but I dont know if is the root object or is under any other object, even in any array of any other object.

I want to do a query that finds in a json field, if it contains the object with type=t, text=x, and call=c. It seems easy to find if there is an objects that have any of them have type=t, others have text=x, and others have call=c (but not consistently of the same object)

WITH Data AS (
    SELECT '1' AS id, '{id:"1",type:"menu",text:"option1",call:"option1()"}' AS json FROM DUAL   UNION ALL
    -- next Select 2a- .. from dual, all in one line, formated only for better view:
    SELECT '2-onlySubElements' AS id, 
        '{id:"2a",type:"menu",text:"option2",call:"option2()", 
            subElements:[
                 {id:"2.1",type:"menu",text:"option2.1",call:"option21()", 
                    subElements:[{id:"2.1.1",type:"menu",text:"option2.1.1",call:"option211()"} ]},
                 {id:"2.2",type:"menu",text:"option2.2",call:"option22()"}    
            ]
          }' AS json FROM DUAL   UNION ALL
    -- next Select 2b-mix .. from dual, all in one line, formated only for better view:
    SELECT '2b-mixOfInnerElements' AS id, 
        '{id:"2b",type:"menu",text:"option3",call:"option3()", 
            subElements:[         
                {id:"2.1",type:"menu",text:"option2.1",call:"option21()", 
                    innerElements:[{id:"2.1.1",type:"menu",text:"option2.1.1",call:"option211()"} ]},        
                {id:"2.2",type:"menu",text:"option2.2",call:"option22()"}
            ]  
          }' AS json FROM DUAL   UNION ALL
    SELECT '0' AS id, '{id:"0",type:"label",text:"label0"}' AS json FROM DUAL
)
SELECT rownum, JSON_VALUE(json,'$.type'), a.*
FROM Data a
  WHERE
   -- uncomment one of the following options:
    -- json_exists(json, '$?(@..type=="menu" && @..text=="option2" && @..call=="option21()" )');  -- BAD. it says exists, but attributes are not of the same element.
    -- json_exists(json, '$.subElements[*]?(@.type=="menu" && @.text=="option2.1" && @.call=="option21()" )'); --NOT GOOD: This finds consistent elements in '2a-onlySubElements' and '2b-mixOfInnerElements'. But it needs the path.
    -- json_exists(json, '$.subElements[*].subElements[*]?(@.type=="menu" && @.text=="option2.1.1" && @.call=="option211()" )'); --NOT GOOD: This finds one consistent element in '2a-onlySubElements'. But it needs the path
    -- json_exists(json, '$.subElements[*]?(@.type=="menu" && @.text=="option2.1.1" && @.call=="option211()" )'); --BAD: This finds nothing (the path is not good)

How can I find a consistent object independently of where the object is inside of the root, or even if the object is the root?

- Clarification 1: As I have arbitrary data (i have only fixed id, and type) i dont know the exact attributes that the object have.

I am looking for something similar to:

json_exists(json, '$?(@..type=="menu" && @..text=="option2" && @..call=="option21()" )')

but, that doesn't need the root $ in the expression, and works in a consistent way, something like (not real notation):

json_exists(json, '?@1.type=="order" && @1.detail.text=="optX" && @2.type=="car" && (( @2.wheel[x].model="abc" && @2.wheel[x].size>215) && @2.wheel[y].model="xyz") || @2.engine.power>100)

that will be true if an object has at least one order and one car (in any place, any deep) with the desired values but @1 and @2 were consistent themselves (one wheel is abc and >215, and another is xyz)

(end of clarification 1.)

Thank you.

(if this is not possible with oracle19, it could be possible with oracle21? Only to know, but I cant change oracle version)

Answer 1

Using json_dataguide you get the list of paths per id containing objects having the 4 fields (id, type, text, call):

WITH Data AS (
    SELECT '1' AS id, '{id:"1",type:"menu",text:"option1",call:"option1()"}' AS json FROM DUAL   UNION ALL
    -- next Select 2a- .. from dual, all in one line, formated only for better view:
    SELECT '2-onlySubElements' AS id, 
        '{id:"2a",type:"menu",text:"option2",call:"option2()", 
            subElements:[
                 {id:"2.1",type:"menu",text:"option2.1",call:"option21()", 
                    subElements:[{id:"2.1.1",type:"menu",text:"option2.1.1",call:"option211()"} ]},
                 {id:"2.2",type:"menu",text:"option2.2",call:"option22()"}    
            ]
          }' AS json FROM DUAL   UNION ALL
    -- next Select 2b-mix .. from dual, all in one line, formated only for better view:
    SELECT '2b-mixOfInnerElements' AS id, 
        '{id:"2b",type:"menu",text:"option3",call:"option3()", 
            subElements:[         
                {id:"2.1",type:"menu",text:"option2.1",call:"option21()", 
                    innerElements:[{id:"2.1.1",type:"menu",text:"option2.1.1",call:"option211()"} ]},        
                {id:"2.2",type:"menu",text:"option2.2",call:"option22()"}
            ]  
          }' AS json FROM DUAL   UNION ALL
    SELECT '0' AS id, '{id:"0",type:"label",text:"label0"}' AS json FROM DUAL
),
guides AS (
    SELECT id, json_dataguide(json) AS guide
    FROM Data a group by id
)
, paths AS (
    SELECT id, t.pth
    FROM guides g,
    json_table(
            g.guide,
            '$[*]'
            columns (
                pth  PATH '$."o:path"'
            )
        ) t
)
SELECT id, substr(pth,1, instr(pth, '.', -1)) AS subpath
FROM paths t
WHERE 
    t.pth LIKE '%type' 
    OR t.pth LIKE '%id' 
    OR t.pth LIKE '%text' 
    OR t.pth LIKE '%call' 
GROUP BY id, substr(pth,1, instr(pth, '.', -1)) having(count(*) = 4)
;


2b-mixOfInnerElements   $.subElements.
2b-mixOfInnerElements   $.subElements.innerElements.
2-onlySubElements   $.
2-onlySubElements   $.subElements.
2-onlySubElements   $.subElements.subElements.
2b-mixOfInnerElements   $.
1   $.

To go further you will need to write PL/SQL code because you can't use expressions in JSON functions like json_table for PATH accepting only constants, so you will need to use EXECUTE IMMEDIATE.

Answer 2

Example of whole solution:

create table t_data77881961 (
    id varchar2(32),
    json clob check (json is json)
);

insert into t_data77881961
   SELECT '1' AS id, '{id:"1",type:"menu",text:"option1",call:"option1()"}' AS json FROM DUAL   UNION ALL
    -- next Select 2a- .. from dual, all in one line, formated only for better view:
    SELECT '2-onlySubElements' AS id, 
        '{id:"2a",type:"menu",text:"option2",call:"option2()", 
            subElements:[
                 {id:"2.1",type:"menu",text:"option2.1",call:"option21()", 
                    subElements:[{id:"2.1.1",type:"menu",text:"option2.1.1",call:"option211()"} ]},
                 {id:"2.2",type:"menu",text:"option2.2",call:"option22()"}    
            ]
          }' AS json FROM DUAL   UNION ALL
    -- next Select 2b-mix .. from dual, all in one line, formated only for better view:
    SELECT '2b-mixOfInnerElements' AS id, 
        '{id:"2b",type:"menu",text:"option3",call:"option3()", 
            subElements:[         
                {id:"2.1",type:"menu",text:"option2.1",call:"option21()", 
                    innerElements:[{id:"2.1.1",type:"menu",text:"option2.1.1",call:"option211()"} ]},        
                {id:"2.2",type:"menu",text:"option2.2",call:"option22()"}
            ]  
          }' AS json FROM DUAL   UNION ALL
    SELECT '0' AS id, '{id:"0",type:"label",text:"label0"}' AS json FROM DUAL
;

    
create type t_77881961_result as object (
    obj_id      varchar2(32),
    item_id     varchar2(32),
    item_type   varchar2(32),
    item_text   varchar2(32),
    item_call   varchar2(32)
)
;
/

create type t_77881961_result_tab as table of t_77881961_result;
/

CREATE OR REPLACE FUNCTION f_data77881961
RETURN t_77881961_result_tab
PIPELINED IS
    v_sql   VARCHAR2(32400);
    v_cursor    SYS_REFCURSOR ;
    v_result    t_77881961_result := t_77881961_result(null, null, null, null, null);
BEGIN

    WITH guides AS (
        SELECT id, json_dataguide(json) AS guide
        FROM t_data77881961 a group by id
    ), 
    paths AS (
        SELECT id, t.pth
        FROM guides g,
        json_table(
            g.guide,
            '$[*]'
            columns (
                pth  PATH '$."o:path"'
            )
        ) t
    ), 
    colsexpressions(id, subpath, rn, rnd, lvl, colexpr) as (
        SELECT id, substr(subpath,1, length(subpath)-1) || '[*]' as subpath, rn, rnd, lvl, q'~columns (
                ~' || lvl || q'~id PATH '$.id',
                ~' || lvl || q'~text PATH '$.text',
                ~' || lvl || q'~type PATH '$.type',
                ~' || lvl || q'~call PATH '$.call'~' AS colexpr
        FROM (
            SELECT id, subpath, row_number() over(PARTITION BY id ORDER BY LENGTH(subpath)) AS rn,
                row_number() over(PARTITION BY id ORDER BY LENGTH(subpath) DESC) AS rnd,
                'L' || row_number() over(PARTITION BY id ORDER BY LENGTH(subpath)) || '_' AS lvl
            FROM (
                SELECT id, substr(pth,1, instr(pth, '.', -1)) AS subpath
                FROM paths t
                WHERE 
                    t.pth LIKE '%type' 
                    OR t.pth LIKE '%id' 
                    OR t.pth LIKE '%text' 
                    OR t.pth LIKE '%call' 
                GROUP BY id, substr(pth,1, instr(pth, '.', -1)) having(count(*) = 4)
            )
        )
    ),
    subpaths(subpath, ids) as (
        SELECT subpath, LISTAGG( '''' || id || '''', ',') FROM colsexpressions c1 GROUP BY c1.subpath
    ),
    sqls(lvl, sql) as (
        select -- c.id, c.subpath, c.lvl,
        distinct c.lvl,
            'SELECT d.id, t.' || lvl || 'ID,  t.' || lvl || 'TYPE,  t.' || lvl || 'TEXT,  t.' || lvl || 'CALL'
            || q'~
        FROM t_data77881961 d, json_table(
            d.json,
            '~' || c.subpath || q'~'
            ~' || c.colexpr || q'~
            )
        ) t
        WHERE d.id IN (~' ||
        s.ids || 
        q'~)
        ~'
        as sql
        from colsexpressions c
        join subpaths s on s.subpath = c.subpath
    )
    SELECT LISTAGG(sql, q'~
    UNION ALL
    
    ~') WITHIN GROUP(ORDER BY lvl) INTO v_sql
    FROM sqls
    ;

    OPEN v_cursor FOR v_sql;
    LOOP
        FETCH v_cursor INTO v_result.obj_id, v_result.item_id, v_result.item_type, v_result.item_text, v_result.item_call;
        EXIT WHEN v_cursor%NOTFOUND;
        PIPE ROW ( v_result );
    END LOOP ;
    CLOSE v_cursor;
    RETURN ;
END ;
/

select * from f_data77881961() ;


OBJ_ID                           ITEM_ID                          ITEM_TYPE                        ITEM_TEXT                        ITEM_CALL                       
-------------------------------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
1                                1                                menu                             option1                          option1()                       
2-onlySubElements                2a                               menu                             option2                          option2()                       
2b-mixOfInnerElements            2b                               menu                             option3                          option3()                       
2-onlySubElements                2.1                              menu                             option2.1                        option21()                      
2-onlySubElements                2.2                              menu                             option2.2                        option22()                      
2b-mixOfInnerElements            2.1                              menu                             option2.1                        option21()                      
2b-mixOfInnerElements            2.2                              menu                             option2.2                        option22()                      
2b-mixOfInnerElements            2.1.1                            menu                             option2.1.1                      option211()                     
2-onlySubElements                2.1.1                            menu                             option2.1.1                      option211()