Oracle JSON_QUERY 설명 : 오라클 함수

* 주의 : 본 함수는 Oracle Database 12c Release 1 (12.1.0.2) 이상에서 사용가능

​

1. 함수의 목적

   

    Oracle JSON_QUERY는 JSON 형식의 데이터에서 하나 이상의 특정 JSON 값을 찾는데 사용하며 문자열로 리턴한다.   

​

2. 샘플을 통한 개념 퀵뷰

​

SELECT JSON_QUERY('{a:100, b:200, c:300}', '$') AS value

FROM DUAL;

​

VALUE

--------------------------------------------------------------------------------

{"a":100,"b":200,"c":300}

​

결과를 살펴보면 엄격한 형식의 JSON 데이터 스트링을 반환함을 알 수 있다.

​

3. 사용방법  

4. 함수 PARAMETER 설명

​

[expr]

 expr의 경우 텍스트로 표현식을 지정하여야 하는데, expr이 컬럼이라면 반드시 VARCHAR2, CLOB or BLOB등의 데이터 타입이여 한다.

expr이 null이라면 본 함수는 null을 리턴한다. expr이 strict 또는 lax 구문을 사용하는 올바른 형식의 JSON 텍스트가 아닌 경우 함수는 기본적으로 null을 반환한다

이러한 기본동작이 마음에 들지 않는다거나 다르게 정의하고 싶다면 JSON_query_on_error_clause를 사용하여 정의가 가능하다.

​

[JSON_path_expression]

JSON 경로 표현식을 지정하는 파라메터이다.

본 경로 표현식을 사용하여 경로 표현식과 일치하거나 만족하는 하나 이상의 JSON 값을 찾는다.

경로 표현식은 텍스트 문자열이여야 한다.

​

경로 표현식은 컨텍스트를 나타내는 달러 기호 ($)로 시작해야하며,

달러 기호 다음에 0 개 이상의 단계가 있으며, 각 단계는 오브젝트 단계 또는 배열 단계가 될 수 있다.

​

경로식의 첫 번째 단계를 컨텍스트 항목과 일치 시키려고 시도하고,

첫 번째 단계가 일치하면 함수는 두 번째 단계를 첫 번째 단계와 일치하는 JSON 값과 일치 시키려고 시도하고, 두 번째 단계가 일치하면 함수는 세 번째 단계를 두 번째 단계와 일치하는 JSON 값과 일치 시키려고 시도한다.

함수는 최종 단계에서 일치하는 값을 문자열에서 쉼표로 구분된 시퀀셜한 값으로 반환하며,

시퀀스의 순서는 고정적이지 않다.

모든 값은 원본 JSON 데이터가 JSON 구문을 엄격하게 사용하는지 아니면 느슨하게 사용하는지에 관계없이 엄격한 JSON 구문을 사용하여 반환하며, 달러 기호와 0 단계 ( '$')로 구성된 경로 식은 전체 컨텍스트 항목과 일치한다.

 

JSON_query_returning_clause를 지정하여 리턴 문자 스트링의 데이터 유형 및 형식을 제어 할 수 있다.

여러 값이 경로 표현식과 일치하거나 하나의 스칼라 값만이 경로 표현식과 일치하면, 배열 래퍼(wrapper)에 값을 래핑(wrapping)해야한다.

​

경로 표현식의 단계에서 일치하는 것을 찾을 수 없을 경우 함수는 디폴트로 null을 반환한다.

JSON_query_on_error_clause를 사용가능하면 이 기본동작을 재정의할 수 있다.

​

JSON_path_expression은 ARRAY와 OBJECT 두가지 방식으로 표현가능하다.

​

[JSON_query_returning_clause]

이 파라미터를 사용하여 함수가 리턴할 문자열의 데이터 유형 및 형식을 지정할 수 있다.

형식은 아래와 같다.

​

RETURNING은 문자열 데이터 타입을 지정할 때 사용하며, 지정하지 않으면 VARCHAR2(4000)로 지정된다.

특정형식으로 지정하고 싶다면, VARCHAR2[(size [BYTE,CHAR])]의 형식으로 지정가능하다.

​

PRETTY는 개행과 들여쓰기를 사용하여 정렬하여 표현하고 싶을 때 사용한다.  

​

ASCII는 표준 ASCII 유니 코드 이스케이프 시퀀스를 사용하여 반환 문자열에 비 ASCII 유니 코드 문자를 자동으로 이스케이프하려면 ASCII를 지정한다.

​

[JSON_query_wrapper_clause]

이 절을 사용하여이 함수가 경로 표현식과 일치하는 값을 Array Wrapper에 포함할지 여부를 제어한다. 즉, Value 시퀀스를 대괄호 ([])로 묶는다.

​

[JSON_query_on_error_clause]

이 절을 사용하면 에러발생시 반환되는 값을 지정할 수 있다.

​

5. 다양한 샘플표현

​

example 1) 반환값은 엄격한 형식의 JSON 구문으로 변환된다. 즉, OBJECT 속성 이름은 큰 따옴표로 묶는다.

​

SELECT JSON_QUERY('{a:100, b:200, c:300}', '$') AS value FROM DUAL;

VALUE

--------------------------------------------------------------------------------

{"a":100,"b":200,"c":300}

​

example 2) JSON데이터에서 속성명 a과 일치하는 값을 배열로 리턴

​

SELECT JSON_QUERY('{a:100, b:200, c:300}', '$.a' WITH WRAPPER) AS value FROM DUAL;

VALUE

--------------------------------------------------------------------------------

[100]

​

example 3) JSON 데이터의 모든 OBJECT 멤버의 값을 배열로 리턴

​

SELECT JSON_QUERY('{a:100, b:200, c:300}', '$.*' WITH WRAPPER) AS value FROM DUAL;

VALUE

--------------------------------------------------------------------------------

[100,200,300]

​

example 4) JSON 데이터의 특정 문자열, 혹은 CONTEXT ITEM으로 반환한다.

​

SELECT JSON_QUERY('[0,1,2,3,4]', '$') AS value

FROM DUAL;

VALUE

--------------------------------------------------------------------------------

[0,1,2,3,4]

example 5) example 4에 WITH WRAPPER를 붙이면 ARRAY로 반환한다.

​

SELECT JSON_QUERY('[0,1,2,3,4]', '$' WITH WRAPPER) AS value

FROM DUAL;

VALUE

--------------------------------------------------------------------------------

[[0,1,2,3,4]]

​

example 6) JSON ARRAY에서 0과, 3~5, 7을 찾는다.

​

SELECT JSON_QUERY('[0,1,2,3,4,5,6,7,8]', '$[0, 3 TO 5, 7]' WITH WRAPPER) AS value FROM DUAL;

VALUE

--------------------------------------------------------------------------------

[0,3,4,5,7]

​

example 7) JSON ARRAY의 모든 요소를 반환한다.

​

SELECT JSON_QUERY('[{"a":100},{"b":200},{"c":300}]', '$[*]' WITH CONDITIONAL WRAPPER) AS value FROM DUAL;

VALUE

--------------------------------------------------------------------------------

[{"a":100},{"b":200},{"c":300}]

​

example 8) JSON ARRAY의 모든 요소를 반환하되 VARCHAR2(100) 형식으로 반환한다.

​

SELECT JSON_QUERY('[{"a":100},{"b":200},{"c":300}]', '$[*]' RETURNING VARCHAR2(100) WITH CONDITIONAL WRAPPER) AS value FROM DUAL;

VALUE

--------------------------------------------------------------------------------

[{"a":100},{"b":200},{"c":300}]

​

example 9) 4번째 element를 반환하라고 했는데, 4번째 element가 없을 경우 에러가 난다. 에러의 경우 null값으로 반환하라고 정의했다.

​

SELECT JSON_QUERY('[{"a":100},{"b":200},{"c":300}]', '$[3]' EMPTY ON ERROR) AS value FROM DUAL;

VALUE

--------------------------------------------------------------------------------

[]

​