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

Answer 1

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":[ ...