Oracle JSON_VALUE 설명 : 오라클 함수

* 주의 : 본 함수는 12C(12.1.0.2) 이상부터 사용가능하다.

1. 함수의 목적 

    Oracle JSON_VALUE는 JSON 데이터에서 특정 JSON값을 찾아 SQL값으로 반환한다.

2. 샘플을 통한 개념 퀵뷰

JSON 데이터 속성명이 'a'인 멤버의 값을 찾아 반환한다.

SELECT JSON_VALUE('{a:100}', '$.a') AS value
FROM DUAL;

VALUE
-----
100

3. 사용방법 

1) JSON_VALUE 전체 요약 사용방법

2) JSON_path_expression절 상세 사용방법

2-1) object_step절 상세 사용방법

2-2) array_step절 상세 사용방법

3) JSON_value_returning_clause 상세 사용방법

3-1) JSON_value_return_type절 상세 사용방법

4) JSON_value_on_error_clause절 상세 사용방법

4. 함수 PARAMETER 설명

[expr]

JSON데이터를 함수에 넣는 입력파라미터이다.
expr이 컬럼이라면, 컬럼의 데이터 타입은 VARCHAR2, CLOB 혹은 BLOB이여야 한다.
expr이 null이라면 null을 리턴한다.
expr이 strict나 lax syntax를 사용하여 만든 정규 JSON데이터가 아니라면, 이때도 기본적으로 NULL을 반환한다.
JSON_value_on_error_clause 절을 사용하여 에러시의 기본동작을 변경가능하다. 

[JSON_path_expression]

JSON path 표현식을 정의하는 절이다.
이 함수는 expr절을 평가하기 위해 path 표현식을 사용하며, path 표현식과 일치하는 스칼라 JSON값을 찾아낸다. path 표현식은 반드시 text 문자여야 한다.

path 표현식은 컨텍스트 항목, 즉 expr에 의해 지정된 표현식을 나타내는 달러 기호($)로 시작해야 한다.
달러($) 기호 다음에 0 개 이상의 단계가 있으며, 각 단계는 오브젝트 또는 배열이 될 수 있다.

이 함수는 path 표현식의 첫번째 단계를 컨텍스트 항목(expr)과 일치시키려고 시도한다.
첫 번째 단계가 일치하면 함수는 두 번째 단계를 첫 번째 단계와 일치하는 JSON 값과 비교하려고 시도한다. 두 번째 단계가 일치하면 함수는 세 번째 단계를 두 번째 단계와 일치하는 JSON 값과 일치시키려고 시도한다. 마지막 단계가 스칼라 JSON 값과 일치하면, 함수는 해당 값을 SQL 값으로 반환합니다.

JSON_value_returning_clause는 SQL로 반환되는 값의 데이터 타입을 조정 가능한 파라미터이다.

path 표현식의 단계가 일치하지 않으면 기본적으로 null을 반환한다. 이 기본 동작을 재정의하려면 JSON_value_on_error_clause를 사용할 수 있다.

[object_step]

■ simple_name 또는 complex_name을 사용하여 속성 이름을 지정한다. 해당 속성 이름을 가진 JSON 데이터 멤버가 JSON 오브젝트에 존재하면 오브젝트 단계는 해당 구성원의 속성 값과 일치한다. 두 가지 유형 모두 대소문자를 구분한다. 따라서 문자와 오브젝트가 JSON 데이터에서 일치하는 경우에만 일치가 발생한다.

simple_name은 영숫자만 포함 할 수 있으며 영문자로 시작해야하며, complex_name은 영숫자와 공백 만 포함 할 수 있으며 영숫자로 시작해야하며, complex_name은 큰 따옴표로 묶어야 한다.

■ 모든 속성 이름을 지정하려면 와일드 카드기호 (*)를 사용한다.
    expr의 JSON 객체가 적어도 하나의 멤버를 포함하면 모든 멤버의 값과 일치하게 된다.

[array_step]

array_step을 지정하는 방식은 아래 두가지가 방식이 있다.

■ 정수를 사용하여 JSON 배열의 인덱스를 지정한다. 두 인덱스 값 사이의 범위를 지정하려면 interger TO interger 형식을 사용한다. 지정된 element가 JSON 배열에 존재하면 해당 element와 일치하게 된다. JSON 배열의 첫 번째 element의 인덱스는 0이다.

■ 와일드 카드 기호(*)를 사용하여 JSON 배열의 모든 elements를 지정가능하다. JSON 배열에 적어도 하나의 element가 포함되어 있으면 배열 JSON 배열의 모든 요소와 일치하는 것으로 간주한다.

JSON 데이터가 JSON 배열이 아닌 경우 데이터는 배열로 암시적으로 래핑되어 평가되며, 이를 JSON path 표현식 완화라고합니다.

[JSON_value_returning_clause]

이 파라미터를 사용하여 리턴되는 값의 데이터타입과 포멧을 정의한다.

[RETURNING]

리턴값의 데이터 유형을 지정한다. 생략하면 varchar2(4000) 형식으로 지정된다.
지정하는 형식은 아래와 같다.

■ VARCHAR2[(size [BYTE,CHAR])]
■ NUMBER[(precision [, scale])]

데이터 타입의 리턴값을 받을 만큼 충분하지 않다면 null을 반환하니 유의해야 한다.

[ASCII]

표준 ASCII Unicode escape 시퀀스를 사용하여, ASCII가 아닌 모든 유니코드문자를 반환값에 자동으로 이스케이프하려면 ASCII를 지정한다.

[JSON_table_on_error_clause]

아래의 에러가 발생할 때 반환할 값을 지정한다.
■ expr이 strict or lax JSON syntax를 사용하여 잘 정제되어 있지 않은 경우
■ row path 표현식을 사용하여 JSON data를 평가할 때 일치하는 항목이 없을 경우 

아래의 clause로 에러 리턴 문자를 제어할 수 있다.  
■ NULL ON ERROR - 에러가 발생하면 null을 리턴한다. (기본값)
■ ERROR ON ERROR - 에러가 발생하면 그에 맞는 Oracle error를 리턴한다.
■ EMPTY ON ERROR - 에러가 발생하면 비어있는 JSON배열('[]')을 리턴한다.

5. 다양한 샘플표현

example1) 다음 쿼리는 속성 이름이 a 인 member의 값을 반환한다. "RETURNING NUMBER" 절이 지정되었으므로 값은 NUMBER 데이터 형식으로 반환된다.

SELECT JSON_VALUE('{a:100}', '$.a' RETURNING NUMBER) AS value
FROM DUAL;

VALUE
----------
100

example2) 다음 쿼리는 속성 이름이 a 인 멤버의 값에 있는 속성 이름이 b 인 멤버의 값을 반환한다.

SELECT JSON_VALUE('{a:{b:100}}', '$.a.b') AS value
FROM DUAL;

VALUE
-----
100

example3) 다음 쿼리는 모든 객체에서 속성 이름이 d 인 멤버의 값을 반환한다.

SELECT JSON_VALUE('{a:{b:100}, c:{d:200}, e:{f:300}}', '$.*.d') AS value
FROM DUAL;

VALUE
-----
200

example4) 다음 쿼리는 배열의 첫번째 요소의 값을 반환한다.

SELECT JSON_VALUE('[0, 1, 2, 3]', '$[0]') AS value
FROM DUAL;

VALUE
-----
0

example5) 다음 쿼리는 배열의 세번째 요소의 값을 반환한다. 배열은 속성 'a'의 멤버의 값이다.

SELECT JSON_VALUE('{a:[5, 10, 15, 20]}', '$.a[2]') AS value
FROM DUAL;

VALUE
-----
15

example6) 다음 쿼리는 배열의 두 번째 객체에서 속성 이름이 a 인 멤버의 값을 반환한다.

SELECT JSON_VALUE('[{a:100}, {a:200}, {a:300}]', '$[1].a') AS value
FROM DUAL;

VALUE
-----
200

example7) 다음 쿼리는 배열의 모든 객체에서 속성 이름이 c 인 멤버의 값을 반환한다.

SELECT JSON_VALUE('[{a:100}, {b:200}, {c:300}]', '$[*].c') AS value
FROM DUAL;

VALUE
-----
300

example8) 다음 쿼리는 속성 이름이 lastname 인 멤버의 값을 반환하려고 한다. 그러나 JSON 데이터에 존재하지 않아 결과가 일치하지 않는다. 
ON ERROR 절이 지정되지 않았기 때문에 디폴트인 NULL ON ERROR를 사용하고 있기 때문에 NULL을 리턴한다.

SELECT JSON_VALUE('{firstname:"John"}', '$.lastname') AS "Last Name"
FROM DUAL;

Last Name
---------

example9) 다음 쿼리는 JSON 데이터에 존재하지 않는 lastname 속성의 멤버 값을 반환하려고 하기 때문에 오류가 발생한다.
ON ERROR 절로  'No last name found'을 지정했으므로, 지정된 텍스트 리터럴을 리턴한다.

SELECT JSON_VALUE('{firstname:"John"}', '$.lastname'
DEFAULT 'No last name found' ON ERROR) AS "Last Name"
FROM DUAL;

Last Name
---------
No last name found