Oracle json path compound expression using multiple context does not work

Question

I have the following json:

{
   "signedOffTasks":[
      "TASK2"
   ],
   "taskDeadlines":[
      {
         "taskKey":"TASK1",
         "deadline":"2016-05-02"
      },
      {
         "taskKey":"TASK2",
         "deadline":"2016-05-02"
      },
      {
         "taskKey":"TASK3",
         "deadline":"2016-05-02"
      },
      {
         "taskKey":"TASK4",
         "deadline":"2016-05-02"
      },
      {
         "taskKey":"TASK5",
         "deadline":"2016-05-02"
      },
      {
         "taskKey":"TASK6",
         "deadline":"2020-05-18"
      }
   ]
}

and the following json path expression:

$.taskDeadlines[?(@.deadline == "2020-05-18" && $.signedOffTasks.indexOf(@.taskKey) == -1)]

that is I want to return something where the deadline is 2020-05-18, and the appropriate task key is not in the signedOffTasks array.

This works very well in

https://codebeautify.org/jsonpath-tester

but unfortunately, if I use the same JSON in an Oracle 12c CLOB column, Oracle does not allow me to mix contexts. It has problems with the $ in the second predicate.

How can I express what I want with Oracle Json path implementation?

This is how I want to make the Oracle query:

SELECT
     *
 FROM
     TABLE MY_TABLE T
 WHERE
    JSON_EXISTS ( T.JSON_COLUMN,'$.taskDeadlines[?(@.deadline == "2020-05-18" && $.signedOffTasks.indexOf(@.taskKey) == -1)]')

Thanks

How exactly are you calling the JSON expression in your SQL statement? — Chris Saxon
– Chris Saxon, Commented May 20, 2020 at 13:15

Chris Saxon · Accepted Answer · 2020-05-21 09:24:25Z

2

The indexOf method is unsupported (as of Oracle Database 19c). So you can't use this to check if the task exists within signedOffTasks.

I'm not sure how you'd write this using JSON path expressions in Oracle Database. But it's possible with SQL!

You can convert the arrays to rows-and-columns using json_table:

select j.* from t, json_table (
  c1, '$.taskDeadlines[*]'
  columns (
    taskKey path '$.taskKey',
    deadline path '$.deadline'
  )
) j
where  deadline = '2020-05-18';

TASKKEY    DEADLINE     
TASK6      2020-05-18  

select j.* from t, json_table (
  c1, '$.signedOffTasks[*]'
  columns (
    task path '$'
  )
) j;

TASK    
TASK2

From there you could use not exists/minus to return the deadline tasks not in the signed off array:

select j.taskKey from t, json_table (
  c1, '$.taskDeadlines[*]'
  columns (
    taskKey path '$.taskKey',
    deadline path '$.deadline'
  )
) j
where  deadline = '2020-05-18'
minus
select j.task from t, json_table (
  c1, '$.signedOffTasks[*]'
  columns (
    task path '$'
  )
) j;

TASKKEY   
TASK6

It's a little fiddly, but you can do this all in the where clause if needed:

select * from t
where  exists (
  select * from json_table (
    c1, '$.taskDeadlines[*]'
    columns (
      taskKey path '$.taskKey',
      deadline path '$.deadline'
    )  
  ) j1
  where  deadline = '2020-05-18'
  and    not exists ( 
    select * from json_table (
      c1, '$.signedOffTasks[*]'
      columns (
        task path '$'
      )
    ) j2
    where  j1.taskKey = j2.task
  )
);

C1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
{
   "signedOffTasks":[
      "TASK2"
   ],
   "taskDeadlines":[ ...

edited May 21, 2020 at 9:24

answered May 20, 2020 at 13:51

Chris Saxon

10.2k1 gold badge33 silver badges47 bronze badges

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

7 Comments

gabox01 Over a year ago

That looks nice, but unfortunately, my query is programmatically generated, and I cannot mess with anything apart from the where clause. Meaning the whole thing must be contained in a WHERE clause fragment

Chris Saxon Over a year ago

You can do these conversions in (not) exists, so you can place them in where if needed; see example added

gabox01 Over a year ago

Yes, that seems to be a solution.

gabox01 Over a year ago

Thanks very much for taking time with this!

gabox01 Over a year ago

I wonder why this doesn't work in 12c, but works in 18c express

|

Collectives™ on Stack Overflow

Oracle json path compound expression using multiple context does not work

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related