Oracle에서 JSON 배열 검색

Oracle에서 JSON 배열 검색

Oracle 12.1.0.2에 도입된 새로운 JSON 기능을 사용하려고 합니다.

하지만 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"}');

지금 저는 제품 #2가 주문된 모든 주문을 반환하는 SQL 쿼리를 작성하려고 합니다.

배열 표현식을 허용하지 않기 때문에 사용할 수 없습니다(어쨌든 값을 지정하는 방법을 알 수 없습니다).

json_value 단일 값만 반환하므로 배열 값을 "반복"할 수 없습니다.

노력했습니다.

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

하지만 그것은 아무것도 돌려주지 않았습니다.

저도 시도했지만, 그것도 배열의 첫 번째 요소만 가져오는 것 같습니다.

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

하지만 그것은 아무것도 보여주지 않았습니다.분명히 "array_step"의 "star expansion"은 다음의 값을 확장하지 않습니다.json_table

그래서 제 질문은:

2번 제품이 주문된 곳에서 (위 샘플 데이터를 기반으로) 모든 주문을 검색하려면 어떻게 해야 합니까?

저는 본질적으로 이 Postgres 쿼리와 동등한 것을 찾고 있습니다.

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

지금은 Oracle을 설치할 수 없지만 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_EXIST를 사용하여 이 작업을 수행할 수 있습니다.

 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>

언급URL : https://stackoverflow.com/questions/29898750/search-a-json-array-in-oracle