a_horse_with_no_name a_horse_with_no_name -4 years ago 292
JSON Question

Search a JSON array in Oracle

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 Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download