Search a JSON array in Oracle

Question

I'm trying to use the new JSON features introduced in Oracle 12.1.0.2

However I can't seem to find a way to look for a specific value in an array inside my JSON document.

Consider the following table and data:

create table orders
(
   id      integer not null primary key,
   details clob not null check (details is json (strict))
);

insert into orders (id, details) values 
(1, '{"products": [{ "product": 1, "quantity": 5}, {"product": 2, "quantity": 1}], "delivery_address": "My hometown"}');

insert into orders (id, details) values 
(2, '{"products": [{ "product": 42, "quantity": 1}, {"product": 10, "quantity": 2}], "comment": "Your website is too slow"}');

insert into orders (id, details) values 
(3, '{"products": [{ "product": 543, "quantity": 1}], "discount": "15"}');

insert into orders (id, details) values 
(4, '{"products": [{ "product": 738, "quantity": 12}], "discount": "32"}');

Now I'm trying to write a SQL query that returns all orders, where product #2 was ordered.

I can't use json_exists because it doesn't allow array expressions (and I wouldn't know how to specify the value anyway).

json_value only returns a single value, so I can't "iterate" over the array values.

I tried:

select *
from orders o
where json_value(details, '$.products[*].product') = '2';

but that didn't return anything.

I also tried json_table, but that also seems to only take the first element from the array:

select *
from orders o, 
     json_table(o.details, '$' columns (product_id integer path '$.products[*].product')) t
where t.product_id = 2;

But that didn't show anything. Apparently the "star expansion" in the "array_step" doesn't expand the values in the json_table

So my question is:

how can I (based on the above sample data) retrieve all orders where the product with the number 2 has been ordered?

I am essentially looking for the equivalent to this Postgres query:

select *
from orders
where details @> '{"products": [{"product": 2}] }';

Answer 1

I do not have any installation of oracle available right now but I believe that the first string in json_table should be the path to the array which we want to produce rows from. Then inside COLUMNS, the path should be relative to the array, not the root.

Try this:

select *
from orders o, 
     json_table(o.details, '$.products[*]' 
         columns (
              product_id integer path '$.product'
         )
     ) t
where t.product_id = 2;