JSON PASSING 절 개선

오라클 23ai에서 다수의 바인드 변수를 사용할 수 있게  JSON PASSING 절의 기능이 개선되었습니다. 이번 글에서 관련 내용을 살펴보겠습니다.

 

테스트 버전은 아래와 같습니다.

-- 1
SELECT version_full FROM product_component_version;

VERSION_FULL
------------
23.3.0.23.09

1 row selected.

 

테스트를 위해 아래와 같이 테이블을 생성하겠습니다.

-- 2
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 JSON);

INSERT INTO t1 VALUES ('{
    "instId":1,
    "statList":[
        {"statId":1,"valueList":[{"type":"MIN","value":1},{"type":"MAX","value":2}]},
        {"statId":2,"valueList":[{"type":"MIN","value":3},{"type":"MAX","value":4}]}
    ]
}');

COMMIT;

 

아래 쿼리는 JSON PASSING 절에 2개의 바인드 변수를 사용하며 결과가 정상적으로 반환됩니다.

-- 3: 23.3
SELECT JSON_VALUE (c1
                 , '$.statList[*]?(@.statId == $v1).valueList[*]?(@.type == $v2).value'
                   PASSING 2 AS "v1", 'MAX' AS "v2" RETURNING NUMBER) AS value
  FROM t1;

VALUE
-----
    4

1 row selected.

 

동일한 쿼리를 21.3 버전에서 수행하면 결과가 반환되지 않습니다. 21.3 버전은 문서의 문법과 달리 1개의 바인드 변수만 지원하는 것으로 보입니다.

-- 4-1: 21.3
SELECT JSON_VALUE (c1
                 , '$.statList[*]?(@.statId == $v1).valueList[*]?(@.type == $v2).value'
                   PASSING 2 AS "v1", 'MAX' AS "v2" RETURNING NUMBER) AS value
  FROM t1;
  
VALUE
-----


1 row selected.

-- 4-2: 21.3
SELECT JSON_VALUE (c1
                 , '$.statList[*]?(@.statId == $v1).valueList[*]?(@.type == "MAX").value'
                   PASSING 2 AS "v1" RETURNING NUMBER) AS value
  FROM t1;

VALUE
-----
    4

1 row selected.

 

아래와 같이 JSON_QUERY 함수를 중첩해서 사용하면  21.3 버전에서도 2개 이상의 바인드 변수를 사용할 수 있습니다.

-- 5: 21.3
SELECT JSON_VALUE (JSON_QUERY (c1, '$.statList[*]?(@.statId == $v1).valueList' PASSING 2 AS "v1")
                 , '$[*]?(@.type == $v2).value'
                   PASSING 'MAX' AS "v2") AS value
  FROM t1;

VALUE
-----
    4

1 row selected.