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, May 20 '20 at 13:15

Chris Saxon · Accepted Answer · 2020-05-21T09:24:25.293

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

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 — gabox01, May 20 '20 at 14:05
You can do these conversions in `(not) exists`, so you can place them in `where` if needed; see example added — Chris Saxon, May 21 '20 at 09:25
I wonder why this doesn't work in 12c, but works in 18c express — gabox01, May 21 '20 at 20:05
It's not even an Oracle error it is: "No more data to read from socket" — gabox01, May 22 '20 at 12:12
deadline = '2020-05-18' I have converted this to actual date and now it works... — gabox01, May 22 '20 at 12:12

Oracle json path compound expression using multiple context does not work

1 Answers1