19

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}] }';
Improve this question
2
  • We experienced a multi-threading bug when running the JSON_TABLE function. Basically, one of the sessions got disconnected when running two simultaneous JSON_TABLE. Make sure you have patched it before going all in with JSON. Commented Apr 27, 2015 at 15:13
  • @PeterHenell: thanks for the hint. Currently I'm just exploring the possibilities, but I will keep that in mind. Commented Apr 27, 2015 at 15:28

2 Answers 2

Reset to default
23

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Can we update an existing array? Can we add a product object to products array of the order with id 1?
0

In 12.2 you can do this with JSON_EXISTS

 SQL> WITH ORDERS as
   2  (
   3    select 1 as ID, '{"products": [{ "product": 1, "quantity": 5}, {"product": 2, "quantity": 1}], "delivery_address": "My hometown"}' as DETAILS
   4      from dual
   5    union all
   6    select 2 as ID, '{"products": [{ "product": 42, "quantity": 1}, {"product": 10, "quantity": 2}], "comment": "Your website is too slow"}' as DETAILS
   7      from dual
   8    union all
   9    select 3 as ID, '{"products": [{ "product": 543, "quantity": 1}], "discount": "15"}' as DETAILS
  10      from dual
  11    union all
  12    select 4 as ID, '{"products": [{ "product": 738, "quantity": 12}], "discount": "32"}' as DETAILS
  13     from dual
  14  )
  15  select *
  16    from ORDERS
  17   where JSON_EXISTS(DETAILS,'$?(@.products.product == $PRODUCT)' passing 2 as "PRODUCT")
  18  /

         ID
 ----------
 DETAILS
 --------------------------------------------------------------------------------
          1
 {"products": [{ "product": 1, "quantity": 5}, {"product": 2, "quantity": 1}], "d
 elivery_address": "My hometown"}


 SQL>
Improve this answer

1 Comment

Actualy json_exist work in the same fasion in 12.1 but doen't use indexex

Your Answer

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