JSON_TABLE
구문
JSON_TABLE
( expr [ FORMAT JSON ] [ , JSON_basic_path_expression ]
[ JSON_table_on_error_clause ] [ JSON_table_on_empty_clause ]
JSON_columns_clause )
JSON_table_on_error_clause::=
{ ERROR | NULL } ON ERROR
JSON_table_on_empty_clause::=
{ ERROR | NULL } ON EMPTY
JSON_columns_clause::=
COLUMNS ( JSON_column_definition [, JSON_column_definition ]... )
JSON_column_definition::=
JSON_exists_column
| JSON_query_column
| JSON_value_column
| JSON_nested_path
| ordinality_column
JSON_exists_column::=
column_name [ JSON_value_return_type ]
EXISTS PATH JSON_basic_path_expression
[ JSON_exists_on_error_clause ]
[ JSON_exists_on_empty_clause ]
JSON_query_column::=
column_name [ JSON_query_return_type ]
FORMAT JSON [ JSON_query_wrapper_clause ]
PATH JSON_basic_path_expression
[ JSON_query_on_error_clause ]
[ JSON_query_on_empty_clause ]
JSON_value_column::=
column_name [ JSON_value_return_type ] PATH JSON_basic_path_expression
[ JSON_value_on_error_clause ]
[ JSON_value_on_empty_clause ]
JSON_nested_path::=
NESTED PATH JSON_basic_path_expression JSON_columns_clause
ordinality_column::=
column_name FOR ORDINALITY
JSON_path ::=
JSON_basic_path_expression | JSON_relative_object_access
JSON_relative_object_access ::=
JSON_object_key [ array_step ]
( "." JSON_object_key [ array_step ] )...
nested_clause ::=
table_reference (NESTED [PATH]) identifier
[
("." [ JSON_object_key array_step ] ) |
("," JSON_basic_path_expression )
]
[ JSON_table_on_error_clause ]
[ JSON_table_on_empty_clause ]
JSON_columns_clause
목적
SQL/JSON 함수인 JSON_TABLE은 JSON 데이터의 관계형 뷰(relational view)를 생성합니다. 이 함수는 JSON 데이터 평가 결과를 관계형 rows외 columns로 매핑합니다. SQL을 사용하여 함수가 반환한 결과를 가상의 관계형 테이블로서 쿼리할 수 있습니다. JSON_TABLE의 주요 목적은 JSON 배열 내의 각 객체에 대해 관계형 데이터 행을 생성하고 해당 객체 내부의 JSON 값을 개별 SQL 컬럼 값으로 출력하는 것입니다.
JSON_TABLE은 SELECT 문의 FROM 절에서만 지정해야 합니다. 함수는 먼저 SQL/JSON row path expression이라고 하는 경로 표현식을 제공된 JSON 데이터에 적용합니다. row path expression과 일치하는 JSON 값은 row source라고 하며, 이는 관계형 데이터의 행을 생성합니다. COLUMNS 절은 행 소스를 평가하고, 행 소스 내에서 특정 JSON 값을 찾아 이러한 JSON 값을 관계형 데이터의 개별 컬럼으로 SQL 값으로 반환합니다.
COLUMNS 절은 다음과 같은 절을 사용하여 다양한 방식으로 JSON 값을 검색할 수 있도록 합니다:
- 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 - 중첩된 JSON 객체나 JSON 배열의 JSON 값을 부모 객체나 배열의 JSON 값과 함께 하나의 행 내에서 개별 컬럼으로 평면화할 수 있도록 합니다. 이 절을 재귀적으로 사용하여 중첩된 객체나 배열의 여러 레이어에서 데이터를 단일 행으로 투영할 수 있습니다.
- ordinality_column - 생성된 행 번호 컬럼을 반환합니다.
컬럼 정의 절을 사용하여 반환되는 각 데이터 컬럼에 이름을 지정할 수 있습니다. 이러한 컬럼 이름은 SELECT 문의 다른 곳에서 참조할 수 있으며, SELECT 목록이나 WHERE 절과 같은 곳에서 사용할 수 있습니다.
참고:
Oracle Database Globalization Support Guide의 부록 C는 JSON_TABLE에 의해 생성된 테이블의 각 문자 데이터 유형 컬럼에 할당된 정렬(collation) 결정 규칙에 대한 정보를 제공합니다.
expr
이 절은 평가할 JSON 데이터를 지정하는 데 사용됩니다. expr에는 텍스트 리터럴로 평가되는 식을 지정합니다. expr이 컬럼인 경우 해당 컬럼은 VARCHAR2, CLOB 또는 BLOB 데이터 유형이어야 합니다. expr이 null이면 함수는 null을 반환합니다.
expr이 strict 또는 lax 구문을 사용하여 올바르게 구성된 텍스트 리터럴이 아닌 경우 함수는 기본적으로 null을 반환합니다. 이 기본 동작을 무시하려면 JSON_table_on_error_clause를 사용할 수 있습니다. JSON_table_on_error_clause를 참조하십시오.
FORMAT JSON
expr이 BLOB 데이터 유형의 컬럼인 경우 FORMAT JSON을 지정해야 합니다.
PATH
PATH 절을 사용하여 컬럼 콘텐츠로 사용할 row의 일부를 구분합니다. PATH 절이 없으면 '$.<column-name>' 경로로 동작이 변경되지 않으며, 여기서 <column-name>은 컬럼 이름입니다. 대상이 되는 객체 필드의 이름이 암시적으로 컬럼 이름으로 사용됩니다. PATH의 전체 의미에 대해서는 Oracle Database JSON Developer's Guide를 참조하십시오.
JSON_basic_path_expression
JSON_basic_path_expression은 텍스트 리터럴입니다. 이 절의 전체 의미에 대해서는 Oracle Database JSON Developer's Guide를 참조하십시오.
JSON_relative_object_access
현재 행 항목을 기준으로 JSON/Path 식으로 평가되는 JSON_relative_object_access 값을 지정하여 간단한 점 표기법을 활성화합니다.
JSON_object_key clause에 대한 자세한 내용은 JSON Object Access Expressions을 참조하십시오.
JSON_table_on_error_clause
이 절을 사용하여 오류가 발생했을 때 함수가 반환하는 값을 지정합니다:
- NULL ON ERROR
º 입력이 잘 형식화되지 않은 JSON 텍스트인 경우 오류가 감지되는 즉시 더 이상의 행이 반환되지 않습니다. JSON_TABLE은 스트리밍 평가를 지원하므로, 오류가 있는 입력 부분을 만나기 전에도 행이 반환될 수 있습니다.
º 행 경로 식을 평가할 때 일치하는 내용을 찾을 수 없는 경우 행이 반환되지 않습니다.
º 모든 컬럼 표현식(expressions)의 기본 오류 동작을 NULL ON ERROR로 설정합니다. - ERROR ON ERROR
º 입력이 잘 형식화되지 않은 JSON 텍스트인 경우 오류가 발생합니다.
º 행 경로 식을 평가할 때 일치하는 내용을 찾을 수 없는 경우 오류가 발생합니다.
º 모든 컬럼 표현식(expressions)의 기본 오류 동작을 ERROR ON ERROR로 설정합니다.
JSON_table_on_empty_clause
이 절을 사용하여 SQL/JSON 경로 식을 사용하여 JSON 데이터를 평가할 때 일치하는 내용을 찾을 수 없는 경우 함수가 반환하는 값을 지정합니다. 이 절을 사용하여 JSON_table_on_error_clause에서 지정된 결과와는 다른 결과를 지정할 수 있습니다.
다음과 같은 절을 지정할 수 있습니다.
- NULL ON EMPTY - 일치하는 내용을 찾을 수 없는 경우 null을 반환합니다.
- ERROR ON EMPTY - 일치하는 내용을 찾을 수 없는 경우 적절한 Oracle 오류를 반환합니다.
- DEFAULT literal ON EMPTY - 일치하는 내용을 찾을 수 없는 경우 literal을 반환합니다. literal의 데이터 유형은 이 함수가 반환하는 값의 데이터 유형과 일치해야 합니다.
이 절을 생략하면 JSON_table_on_error_clause에서 일치하는 내용을 찾을 수 없는 경우 반환되는 값을 결정합니다.
JSON_columns_clause
COLUMNS 절을 사용하여 JSON_TABLE 함수에서 반환하는 가상 관계형 테이블의 컬럼을 정의합니다.
컬럼이 JSON_VALUE 의미를 가지는 경우 TRUNCATE를 지정합니다.
JSON_exists_column
이 절은 JSON_EXISTS 조건과 동일한 방식으로 JSON 데이터를 평가합니다. 즉, 지정된 JSON 값이 있는지 여부를 확인합니다. 이 절은 'true' 또는 'false' 값의 VARCHAR2 컬럼 또는 1 또는 0 값의 NUMBER 컬럼을 반환합니다.
'true'나 1의 값은 JSON 값이 있는 것을 나타내며, 'false'나 0의 값은 JSON 값이 없는 것을 나타냅니다.
JSON_value_return_type 절을 사용하여 반환된 컬럼의 데이터 유형을 제어할 수 있습니다. 이 절을 생략하면 데이터 유형은 VARCHAR2(4000)입니다. 컬럼의 이름을 지정하기 위해 column_name을 사용합니다. JSON_exists_column의 나머지 절은 JSON_EXISTS 조건과 동일한 의미를 가집니다. 이러한 절에 대한 자세한 정보는 "JSON_EXISTS Condition"을 참조하십시오. 또한 "JSON_exists_column 사용 예제"를 참조하여 예제를 확인할 수 있습니다.
JSON_query_column
이 절은 JSON_QUERY 함수와 동일한 방식으로 JSON 데이터를 평가합니다. 즉, 지정된 하나 이상의 JSON 값들을 찾아 해당 JSON 값들을 포함하는 문자열 컬럼을 반환합니다.
반환된 컬럼의 이름을 지정하기 위해 column_name을 사용합니다. JSON_query_column의 나머지 절은 여기서 JSON_QUERY 함수와 동일한 의미를 가집니다. 이러한 절에 대한 자세한 정보는 JSON_QUERY를 참조하십시오. 또한 "JSON_query_column 사용 예제"를 참조하여 예제를 확인할 수 있습니다.
JSON_value_column
이 절은 JSON_VALUE 함수와 동일한 방식으로 JSON 데이터를 평가합니다. 즉, 지정된 스칼라 JSON 값들을 찾아 해당 JSON 값들을 SQL 값으로 구성된 컬럼으로 반환합니다.
반환된 컬럼의 이름을 지정하기 위해 column_name을 사용합니다. JSON_value_column의 나머지 절은 여기서 JSON_VALUE 함수와 동일한 의미를 가집니다. 이러한 절에 대한 자세한 정보는 JSON_VALUE를 참조하십시오. 또한 "JSON_value_column 사용 예제"를 참조하여 예제를 확인할 수 있습니다.
JSON_nested_path
이 절을 사용하여 중첩된 JSON 객체나 JSON 배열의 JSON 값을 부모 객체나 배열의 JSON 값과 함께 하나의 행 안에서 개별 컬럼으로 변환합니다. 이 절을 재귀적으로 사용하여 중첩된 객체나 배열의 여러 레이어에서 데이터를 추출하여 하나의 행으로 투영할 수 있습니다.
JSON_TABLE 함수에서 지정한 SQL/JSON 행 경로 식에 상대적인 JSON_basic_path_expression 절을 지정하여 중첩된 객체나 배열을 일치시킵니다.
중첩된 객체나 배열의 컬럼을 정의하기 위해 COLUMNS 절을 사용합니다. 이 절은 재귀적으로 작성할 수 있으며 다른 JSON_nested_path 절 내에서 JSON_nested_path 절을 지정할 수도 있습니다. 또한 "JSON_nested_path 사용 예제"를 참조하여 예제를 확인할 수 있습니다.
ordinality_column
이 절은 NUMBER 데이터 유형의 생성된 행 번호 컬럼을 반환합니다. ordinality_column은 최대 하나까지 지정할 수 있습니다. 또한 "JSON_value_column 사용 예제"에서 ordinality_column 절을 사용한 예제를 확인할 수 있습니다.
nested_clause
nested_clause를 사용하여 JSON 값을 관계형 컬럼에 매핑하는 축약 구문으로 사용합니다. 이는 JSON_TABLE columns 절의 구문을 재사용하며 사실상 JSON_TABLE과의 왼쪽 외부 ANSI 조인과 거의 동등합니다.
nested_clause를 사용한 예제 1은 JSON_TABLE을 사용한 왼쪽 외부 조인을 이용한 예제 2와 동등합니다.
Example 1 Nested_Clause
SELECT t.*
FROM j_purchaseOrder
NESTED po_document COLUMNS(PONumber, Reference, Requestor) t;
PONUMBER REFERENCE REQUESTOR
--------------- ------------------------------ -----------------------------
1600 ABULL-20140421 Alexis Bull
Example 2 Left-Outer Join With JSON_TABLE
SELECT t.*
FROM j_purchaseOrder LEFT OUTER JOIN
JSON_TABLE(po_document COLUMNS(PONumber, Reference, Requestor)) t ON 1=1;
nested_clause를 사용할 때, NESTED 키워드 다음에 오는 JSON 컬럼 이름은 SELECT * 확장에 포함되지 않습니다. 예를 들어:
SELECT *
FROM j_purchaseOrder
NESTED po_document.LineItems[*]
COLUMNS(ItemNumber, Quantity NUMBER);
ID DATE_LOADED ITEMN QUANTITY
------------------------ ---------------------------------------- ------- -----------
6C5589E9A9156… 16-MAY-18 08.40.30.397688 AM -07:00 1 9
6C5589E9A9156… 16-MAY-18 08.40.30.397688 AM -07:00 2 5
결과에는 JSON 컬럼 이름인 po_document가 결과의 컬럼 중 하나로 포함되지 않습니다.
JSON 컬럼 데이터를 해체할 때는 LEFT OUTER JOIN 의미를 사용하는 것이 권장되며, 이로써 JSON 컬럼이 행을 생성하지 않을 경우 다른 non-JSON 데이터가 결과에서 필터되지 않습니다. 예를 들어, NULL po_document 컬럼을 가진 j_purchaseOrder 행은 결과에서 non-NULL 관계형 columns id와 date_loaded를 필터링하지 않습니다.
columns 절은 JSON_TABLE에 정의된 모든 기능을 지원하며 중첩된 컬럼도 지원합니다. 예를 들어:
SELECT t.*
FROM j_purchaseorder
NESTED po_document COLUMNS(PONumber, Reference,
NESTED LineItems[*] COLUMNS(ItemNumber, Quantity)
) t
PONUMBER REFERENCE ITEMN QUANTITY
--------------- ------------------------------ ----- ------------
1600 ABULL-20140421 1 9
1600 ABULL-20140421 2 5
예제
JSON 문서를 포함하는 테이블 생성: 예제
이 예제는 나머지 JSON_TABLE 예제에서 사용되는 j_purchaseorder 테이블을 생성하고 채우는 방법을 보여줍니다.
다음 문장은 j_purchaseorder 테이블을 생성합니다. po_document 컬럼은 JSON 데이터를 저장하기 위한 컬럼으로, 따라서 컬럼에는 잘 형식화된 JSON만 저장되도록 IS JSON 확인 제약 조건이 있습니다.
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));
다음 문장은 하나의 row 또는 하나의 JSON 문서를 j_purchaseorder 테이블에 삽입합니다:
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}]}');
JSON_query_column 사용 예제
이 예제에서의 문장은 JSON_query_column 절을 사용하여 특정 JSON 속성에 대한 JSON 데이터를 조회하고 해당 속성 값을 컬럼으로 반환합니다.
이 문장은 먼저 column po_document에 SQL/JSON 행 경로 식을 적용하여 ShippingInstructions 속성과 일치하도록 합니다. 그런 다음 COLUMNS 절은 JSON_query_column 절을 사용하여 Phone 속성 값을 VARCHAR2(100) 컬럼으로 반환합니다.
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"}]
JSON_value_column 사용 예제
이 예제의 문장은 이전 예제의 문장을 보완하여 JSON_value_column 절을 사용하여 특정 JSON 값에 대한 JSON 데이터를 조회하고 해당 JSON 값을 관계형 행과 컬럼으로 SQL 값으로 반환합니다.
이 문장은 먼저 column po_document에 SQL/JSON 행 경로 식을 적용하여 JSON 배열 Phone의 요소와 일치하도록 합니다. 이러한 요소들은 두 개의 멤버 type과 number를 포함하는 JSON 객체입니다. 문장은 COLUMNS 절을 사용하여 각 객체의 type 값을 VARCHAR2(10) 컬럼인 phone_type으로 반환하고, 각 객체의 number 값을 VARCHAR2(20) 컬럼인 phone_num으로 반환합니다. 또한 문장은 row_number라는 순서 컬럼(ordinal 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;
ROW_NUMBER PHONE_TYPE PHONE_NUM
---------- ---------- --------------------
1 Office 909-555-7307
2 Mobile 415-555-1234
JSON_exists_column 사용 예제
이 예제의 문장은 JSON_exists_column 절을 사용하여 JSON 데이터에서 JSON 값이 존재하는지 테스트합니다. 첫 번째 예제는 테스트 결과를 'true' 또는 'false' 값으로 나타내는 컬럼에 반환합니다. 두 번째 예제는 테스트 결과를 WHERE 절에서 사용합니다.
다음 문장은 먼저 column po_document에 SQL/JSON 행 경로 식을 적용하여 전체 컨텍스트 항목 또는 JSON 문서와 일치하도록 합니다. 그런 다음 COLUMNS 절을 사용하여 요청자의 이름과 해당 요청자의 JSON 데이터에 우편번호(zip code)가 있는지 여부를 나타내는 'true' 또는 'false' 문자열 값을 반환합니다. COLUMNS 절은 먼저 JSON_value_column 절을 사용하여 Requestor 값을 VARCHAR2(32) 컬럼인 requestor로 반환한 다음 JSON_exists_column 절을 사용하여 zipCode 객체가 있는지 여부를 결정하고 결과를 VARCHAR2(5) 컬럼인 has_zip으로 반환합니다.
SELECT requestor, has_zip
FROM j_purchaseorder,
JSON_TABLE(po_document, '$'
COLUMNS
(requestor VARCHAR2(32) PATH '$.Requestor',
has_zip VARCHAR2(5) EXISTS PATH '$.ShippingInstructions.Address.zipCode'));
REQUESTOR HAS_ZIP
-------------------------------- -------
Alexis Bull true
다음 문장은 이전 문장과 비슷하지만, has_zip의 값이 WHERE 절에서 사용되어 Requestor 값을 반환할지 여부를 결정합니다.
SELECT requestor
FROM j_purchaseorder,
JSON_TABLE(po_document, '$'
COLUMNS
(requestor VARCHAR2(32) PATH '$.Requestor',
has_zip VARCHAR2(5) EXISTS PATH '$.ShippingInstructions.Address.zipCode'))
WHERE (has_zip = 'true');
REQUESTOR
--------------------------------
Alexis Bull
JSON_nested_path 사용 예제
다음의 두 가지 간단한 문장은 JSON_nested_path 절의 기능을 보여줍니다. 이들은 세 개의 요소를 포함하는 간단한 JSON 배열에서 작동합니다. 첫 두 개의 요소는 숫자입니다. 세 번째 요소는 두 개의 문자열 값 요소를 포함하는 중첩된 JSON 배열입니다.
다음의 문장은 JSON_nested_path 절을 사용하지 않습니다. 이 문장은 배열 내의 세 요소를 하나의 행으로 반환합니다. 중첩된 배열은 전체로 반환됩니다.
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]'));
OUTER_VALUE_0 OUTER_VALUE_1 OUTER_VALUE_2
------------- ------------- --------------------
1 2 ["a","b"]
다음 문장은 이전 문장과 다릅니다. 이 문장은 JSON_nested_path 절을 사용하여 중첩된 배열의 개별 요소를 개별 컬럼으로 반환하며 부모 배열 요소와 함께 하나의 행으로 반환합니다.
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
이전 예제에서는 중첩된 JSON 배열을 사용하는 방법을 보여줍니다. 다음 예제에서는 JSON_nested_path 절을 중첩된 JSON 객체와 함께 사용하는 방법을 보여줍니다. 이 예제는 중첩된 객체의 개별 요소를 개별 컬럼으로 반환하며 부모 객체 요소와 함께 하나의 행으로 반환합니다.
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
다음 문장은 j_purchaseorder 테이블을 쿼리할 때 JSON_nested_path 절을 사용합니다. 먼저 po_document 컬럼에 대해 행 경로 표현식을 적용하여 전체 컨텍스트 항목 또는 JSON 문서에 일치하도록 합니다. 그런 다음 COLUMNS 절을 사용하여 Requestor 값을 VARCHAR2(32) 컬럼인 requestor에 반환합니다. 그런 다음 JSON_nested_path 절을 사용하여 중첩된 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;
REQUESTOR PHONE_TYPE PHONE_NUM
-------------------- -------------------- ---------------
Alexis Bull Office 909-555-7307
Alexis Bull Mobile 415-555-1234
다음 예제는 JSON_nested_path에서 간단한 점 표기법의 사용 및 해당 점 표기법 없이 동등한 예를 보여줍니다.
SELECT c.*
FROM customer t,
JSON_TABLE(t.json COLUMNS(
id, name, phone, address,
NESTED orders[*] COLUMNS(
updated, status,
NESTED lineitems[*] COLUMNS(
description, quantity NUMBER, price NUMBER
)
)
)) c;
위의 문장은 점 표기법을 사용한 것으로 다음의 점 표기법 없이 작성한 문장과 동등합니다:
SELECT c.*
FROM customer t,
JSON_TABLE(t.json, '$' COLUMNS(
id PATH '$.id',
name PATH '$.name',
phone PATH '$.phone',
address PATH '$.address',
NESTED PATH '$.orders[*]' COLUMNS(
updated PATH '$.updated',
status PATH '$.status',
NESTED PATH '$.lineitems[*]' COLUMNS(
description PATH '$.description',
quantity NUMBER PATH '$.quantity',
price NUMBER PATH '$.price'
)
)
)) c;
출처: 오라클 레퍼런스
원문 링크: Oracle JSON_TABLE 함수 문서
'코딩 > 오라클 함수' 카테고리의 다른 글
| [오라클 레퍼런스 함수] JSON_VALUE - JSON 속성 값 추출 (0) | 2023.08.08 |
|---|---|
| [오라클 레퍼런스 함수] JSON_TRANSFORM - JSON 문서 내용 변경 (0) | 2023.08.08 |
| [오라클 레퍼런스 함수] JSON_SERIALIZE - JSON 데이터를 텍스트 형식으로 변환 (0) | 2023.08.08 |
| [오라클 레퍼런스 함수] JSON_QUERY - JSON 문서에서 일부 내용 추출 (0) | 2023.08.08 |
| [오라클 레퍼런스 함수] JSON_OBJECTAGG - 키-값 쌍으로 구성된 JSON 객체 반환 (0) | 2023.08.07 |
댓글