0

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)

Improve this question

2 Answers 2

Reset to default
0

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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()
Improve this answer

2 Comments

But I was looking for another approach (my fault). As I have arbitrary data (i have only fixed id, and type) i don't know the exact attributes that the objects have. I have rewritten the question (clarification 1). Anyway thank you for your 2 answers.
Each row must return the same columns, even when using PTF, so the only way would be to return (key, value) additional columns over the "fixed" ones. That would complicate for sure the building of the query in the procedure, and I don't have time to check if possible. And that would also means N rows per object level instead of 1.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.