Oracle JSON_TABLE 설명 : 오라클 함수

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

1. 함수의 목적 

    Oracle JSON_TABLE은 JSON 데이터를 관계열 열과 행으로 매핑하는 것이며, 함수를 통해 리턴된 결과를 SQL을 사용하여 가상의 관계형 테이블로 조회 할 수 있게 만드는 것이 핵심이다.
     즉, JSON 데이터를 가지고 관계형 VIEW를 생성한다는 뜻이다.

2. 샘플을 통한 개념 퀵뷰

1) JSON데이터를 담을 컬럼 po_document를 생성한다.

CREATE TABLE j_purchaseorder
(id RAW (16) NOT NULL,
date_loaded TIMESTAMP(6) WITH TIME ZONE,
po_document CLOB CONSTRAINT ensure_json CHECK (po_document IS JSON));

2) JSON데이터를 po_document에 입력한다.

INSERT INTO j_purchaseorder
VALUES (
SYS_GUID(),
SYSTIMESTAMP,
'{"PONumber" : 1600,
"Reference" : "ABULL-20140421",
"Requestor" : "Alexis Bull",
"User" : "ABULL",
"CostCenter" : "A50",
"ShippingInstructions" : {"name" : "Alexis Bull",
"Address": {"street" : "200 Sporting Green",
"city" : "South San Francisco",
"state" : "CA",
"zipCode" : 99236,
"country" : "United States of America"},
"Phone" : [{"type" : "Office", "number" : "909-555-7307"},
{"type" : "Mobile", "number" : "415-555-1234"}]},
"Special Instructions" : null,
"AllowPartialShipment" : true,
"LineItems" : [{"ItemNumber" : 1,
"Part" : {"Description" : "One Magic Christmas",
"UnitPrice" : 19.95,
"UPCCode" : 13131092899},
"Quantity" : 9.0},
{"ItemNumber" : 2,
"Part" : {"Description" : "Lethal Weapon",
"UnitPrice" : 19.95,
"UPCCode" : 85391628927},
"Quantity" : 5.0}]}');

3) JSON_TABLE()과 COLUMN() 함수를 사용하여 아래와 같은 관계형 뷰로 생성할 수 있다.

SELECT jt.*
FROM j_purchaseorder,
JSON_TABLE(po_document, '$.ShippingInstructions.Phone[*]'
COLUMNS (row_number FOR ORDINALITY,
phone_type VARCHAR2(10) PATH '$.type',
phone_num VARCHAR2(20) PATH '$.number'))
AS jt;

JSON_TABLE

JSON_TABLE

3. 사용방법 

1) JSON_TABLE 전체 요약 사용법

2) JSON_path_expression인수의 세부 사용방법

2-1) obejct_step 인수의 세부 사용방법

2-2) array_step의 사용방법

3) JSON_table_on_error_clause의 세부 사용방법

4) JSON_columns_clause의 세부 사용방법

4-1) JSON_column_definition 세부 사용방법

4-1-1) JSON_exists_column의 세부 사용방법

4-1-2) JSON_query_column 세부 사용방법

4-1-3) JSON_value_column 세부 사용방법

4-1-4) nested_path_column 세부 사용방법

4-1-5) ordinality_column 세부 사용방법

4. 함수 PARAMETER 설명

 JSON_TABLE은 JSON 배열 내부의 각 객체를 관계형 데이터의 행을 작성하고, 해당 객체 내에서 개별 SQL 열 값으로 JSON 값을 출력하는 것이다.

반드시 SELECT 문의 FROM 절에서만 JSON_TABLE을 사용해야만 한다.

이 함수는 먼저 row path expression이라고 하는 JSON_path_expression을 제공된 JSON 데이터에 적용한다.

row path expression과 일치하는 JSON 값을 관계형 데이터 행 형식으로 생성한다는 점에서 row source라고 한다.

COLUMNS 절은 row source를 평가하고 row source 내에서 특정 JSON 값을 찾아, 해당 JSON 값을 관계형 데이터 행의 개별 컬럼에 SQL 값으로 리턴한다.

COLUMNS 절을 사용하면 아래의 clause을 사용하여 여러가지 방법으로 JSON 값을 검색 할 수 있다.
("4-1) JSON_column_definition 세부 사용방법"의 세부 설명에 해당함)

■ JSON_exists_column - JSON_EXISTS 조건과 동일한 방식으로 JSON 데이터를 평가한다. 즉, 지정된 JSON 값이 존재하는지 확인하고 'true'또는 'false'값의 VARCHAR2 열 혹은 1 또는 0 값의 NUMBER 열을 반환한다.

■ JSON_query_column - JSON_QUERY 함수와 같은 방식으로 JSON 데이터를 평가한다. 즉, 하나 이상의 지정된 JSON 값을 찾고 해당 JSON 값이 들어있는 문자열을 반환한다.

■ JSON_value_column - JSON_VALUE 함수와 동일한 방식으로 JSON 데이터를 평가한다. 즉, 지정된 스칼라 JSON 값을 찾고 해당 JSON 값의 열을 SQL 값으로 반환한다.

■ JSON_nested_path - 중첩된(nested) JSON 객체 또는 JSON 배열의 JSON 값을 부모 객체 또는 배열의 JSON 값과 함께 단일 행의 개별 열로 전개 할 수 있다.
이 절을 반복적으로 사용하면 중첩된 객체 또는 배열의 여러 레이어에서 단일 행으로 데이터를 펼칠 수 있다.

■ ordinality_column - 생성된 행번호(row numbers) 열을 반환한다.

[expr]

평가할 JSON 데이터를 지정하는 파라미터이다. 텍스트 리터럴로 평가되는 표현식으로 지정해야 한다.
expr이 열이면, 열은 VARCHAR2, CLOB 또는 BLOB 데이터 유형이어야 한다.
expr이 NULL이면, NULL을 리턴한다.

expr이 strict 또는 lax 구문을 사용한 올바른 형식의 JSON 데이터의 텍스트 리터럴이 아닌 경우, 기본적으로 null을 반환한다.

이 기본 동작을 재정의하려면 JSON_table_on_error_clause를 사용할 수 있다.

[JSON_path_expression]
row path 표현식을 지정한다.
이 함수는 row path 표현식을 사용하여 expr을 평가하고, path 표현식과 일치하거나 만족하는 row source라고하는 JSON 값을 찾는다. 이 row source는 COLUMNS 절에 의해 평가된다.
path 표현식은 텍스트 리터럴이어야한다.
JSON_path_expression 절은 JSON_TABLE 및 JSON_QUERY에 쓰인 것과 동일한 의미를 가진다.

[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배열('[]')을 리턴한다.

[JSON_columns_clause]
COLUMNS 절은 가상의 관계형 테이블의 컬럼을 지정할 때 쓴다.

[JSON_exists_column]
이 절은 JSON_EXISTS 조건과 동일한 방식으로 JSON 데이터를 평가한다. 즉, 지정된 JSON 값이 존재하는지 확인한다.
'true' 또는 'false'값의 VARCHAR2 열 혹은 1 또는 0 값의 NUMBER 열을 반환한다.
'true' 또는 1 값은 JSON 값이 존재함을 나타내고 'false' 또는 0 값은 JSON 값이 없음을 나타낸다.
 
[JSON_value_return_type]
JSON_value_return_type 절을 사용하여 리턴된 컬럼의 데이터 유형을 제어 할 수 있다.
이 절을 생략하면, 데이터 유형은 VARCHAR2 (4000)이 된다.

[column_name]
column_name을 사용하여 리턴된 컬럼의 이름을 지정할 수 있다.

[ordinality_column]
이 절은 행번호가 생성될 경우 생성된 행번호가 들어간 컬럼을 컬럼을 지정한다.
최대 하나의 ordinality_column만 지정가능하다.

5. 다양한 샘플표현

example1) JSON_query_column 파라미터를 사용한 쿼리로, 먼저 row path 표현식을 po_document 열에 적용하여 ShippingInstructions 등록 정보와 일치시킨다. 
  그런 다음 COLUMNS 절은 JSON_query_column 절을 사용하여 VARCHAR2 (100)의 Phone열에 등록 정보 값을 리턴한다.

SELECT jt.phones
FROM j_purchaseorder,
JSON_TABLE(po_document, '$.ShippingInstructions'
COLUMNS
(phones VARCHAR2(100) FORMAT JSON PATH '$.Phone')) AS jt;

PHONES
-------------------------------------------------------------------------------------
[{"type":"Office","number":"909-555-7307"},{"type":"Mobile","number":"415-555-1234"}]

example2) JSON_exists_column절을 사용한 예제로, JSON 값이 존재하는지 여부를 JSON_exists_column 절을 사용하여 판별한다.

SELECT requestor, has_zip
FROM j_purchaseorder,
JSON_TABLE(po_document, '$'
COLUMNS
(requestor VARCHAR(32) PATH '$.Requestor',
has_zip VARCHAR2(5) EXISTS PATH '$.ShippingInstructions.Address.zipCode'));

REQUESTOR                      HAS_ZIP
-------------------------------- -------
Alexis Bull                          true

example3) "example2)" 사용한 샘플에서 where절을 추가하여 true/false값을 활용하는 예제이다.   
WHERE 절에서 has_zip 값을 사용하여 필터링 여부를 결정한다는 점을 제외하면 이전 샘플과 동일하다. 

SELECT requestor
FROM j_purchaseorder,
JSON_TABLE(po_document, '$'
COLUMNS
(requestor VARCHAR(32) PATH '$.Requestor',
has_zip VARCHAR2(5) EXISTS PATH '$.ShippingInstructions.Address.zipCode'))
WHERE (has_zip = 'true');

REQUESTOR
--------------------------------
Alexis Bull

example3) nested_path_column절 샘플을 위한 예제이다.

1) nestsed_path_column을 사용하지 않으면 아래와 같이 문자열 값이 두개 들어 있는 중첩 JSON을 배열 그대로 반환한다.

SELECT *
FROM JSON_TABLE('[1,2,["a","b"]]', '$'
COLUMNS (outer_value_0 NUMBER PATH '$[0]',
outer_value_1 NUMBER PATH '$[1]',
outer_value_2 VARCHAR2(20) FORMAT JSON PATH '$[2]'));

2) NESTED_PATH 컬럼을 사용했기 때문에 아래와 같이 단일 컬럼으로 분리되어 a와 b 각각 값이 들어간다.

SELECT *
FROM JSON_TABLE('[1,2,["a","b"]]', '$'
COLUMNS (outer_value_0 NUMBER PATH '$[0]',
outer_value_1 NUMBER PATH '$[1]',
NESTED PATH '$[2]'
COLUMNS (nested_value_0 VARCHAR2(1) PATH '$[0]',
nested_value_1 VARCHAR2(1) PATH '$[1]')));

OUTER_VALUE_0 OUTER_VALUE_1 NESTED_VALUE_0 NESTED_VALUE_1
------------- -            ------------             --------------              --------------
 1                            2                           a                              b

3) 다음 예제는 중첩된 JSON Object를 단일 컬럼으로 각각 분리해서 반환하는 샘플이다.

SELECT *
FROM JSON_TABLE('{a:100, b:200, c:{d:300, e:400}}', '$'
COLUMNS (outer_value_0 NUMBER PATH '$.a',
outer_value_1 NUMBER PATH '$.b',
NESTED PATH '$.c'
COLUMNS (nested_value_0 NUMBER PATH '$.d',
nested_value_1 NUMBER PATH '$.e')));

OUTER_VALUE_0 OUTER_VALUE_1 NESTED_VALUE_0 NESTED_VALUE_1
-------------              -------------             --------------             --------------
100                        200                       300                         400

4) 다음 예제는 nested_path_column 절을 사용하는 마지막 예제다.
row path 표현식을 po_document 열에 적용하여 JSON 데이터와 일치시킨다. 그런 다음 COLUMNS 절을 사용하여 requestor라는 VARCHAR2 (32) 열에서 requestor 값을 리턴한다.
그러고 난 후 nested_path_column 절을 사용하여 중첩된 Phone 배열의 각 멤버에있는 개별 객체의 속성 값을 반환한다.
중첩된 배열의 각 멤버에 대해 각각 행이 생성되고 각 행에는 해당 requestor가 포함된다.

SELECT jt.*
FROM j_purchaseorder,
JSON_TABLE(po_document, '$'
COLUMNS
(requestor VARCHAR2(32) PATH '$.Requestor',
NESTED PATH '$.ShippingInstructions.Phone[*]'
COLUMNS (phone_type VARCHAR2(32) PATH '$.type',
phone_num VARCHAR2(20) PATH '$.number')))
AS jt;