oracle 19, json query, with arbitrary json data

javi_alt
January 27, 2024
185 views
0 votes
2 Answers

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?

Thank you.

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

Answers

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.

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()

Please signup or login to give your own answer.

Click here to cancel reply.