Я пытаюсь использовать новые функции JSON, представленные в Oracle 12.1.0.2
Однако я не могу найти способ поиска определенного значения в массиве внутри моего документа JSON.
Рассмотрим следующую таблицу и данные:
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"}');
Теперь я пытаюсь написать SQL-запрос, который возвращает все заказы, где был заказан товар # 2.
Я не могу использовать json_exists, потому что он не допускает выражения массива (и я бы не знал, как указать значение в любом случае).
json_value возвращает только одно значение, поэтому я не могу "перебирать" значения массива.
Я пробовал:
select *
from orders o
where json_value(details, '$.products[*].product') = '2';
но это ничего не вернуло.
Я также пробовал json_table, но это также, кажется, берет только первый элемент из массива:
select *
from orders o,
json_table(o.details, '$' columns (product_id integer path '$.products[*].product')) t
where t.product_id = 2;
Но это ничего не показывало. По-видимому, "звездообразное расширение" в array_step" не расширяет значения в json_table
Итак, мой вопрос:
как я могу (на основе вышеприведенных данных) получить все заказы, где был заказан продукт с номером 2?
Я в основном ищу эквивалент этого запроса Postgres:
select *
from orders
where details @> '{"products": [{"product": 2}] }';
У меня нет никакой установки оракула, доступного прямо сейчас, но я считаю, что первая строка в json_table должна быть путем к массиву, из которого мы хотим создать строки. Затем внутри COLUMNS путь должен относиться к массиву, а не к корню.
Попробуйте следующее:
select *
from orders o,
json_table(o.details, '$.products[*]'
columns (
product_id integer path '$.product'
)
) t
where t.product_id = 2;
В 12.2 вы можете сделать это с помощью 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>