注意: JSON_TABLE ファンクションは、Oracle Database 12cリリース1(12.1.0.2)から使用可能です。 |
構文
(JSON_path_expression::=、JSON_table_on_error_clause::=、JSON_columns_clause::=)
object_step::=
array_step::=
JSON_column_definition::=
(これらの句の構文およびセマンティクスは、JSON_EXISTS
およびJSON_VALUE
ドキュメントに記載されています: JSON_value_return_type::=、JSON_path_expression::=、JSON_exists_on_error_clause::=)
(これらの句の構文およびセマンティクスは、JSON_QUERY
ドキュメントに記載されています: JSON_query_return_type::=、JSON_query_wrapper_clause::=、JSON_path_expression::=、JSON_query_on_error_clause::=)
(これらの句の構文およびセマンティクスは、JSON_VALUE
ドキュメントに記載されています: JSON_value_return_type::=、JSON_path_expression::=、JSON_value_on_error_clause::=)
(JSON_path_expression::=、JSON_columns_clause::=)
用途
JSON_TABLE
は、JSONデータのリレーショナル・ビューを作成します。JSONデータの評価結果をリレーショナル行および列にマップします。SQLを使用して、このファンクションで戻される結果を仮想リレーショナル表として問い合せることができます。JSON_TABLE
の主な目的は、JSON配列内の各オブジェクトのリレーショナル・データの行を作成し、個々のSQL列値としてそのオブジェクト内からJSON値を出力することです。
SELECT
文のFROM
句にのみ、JSON_TABLE
を指定できます。ファンクションは、最初に行パス式と呼ばれるJSONパス式を指定されたJSONデータに適用します。行パス式と一致するJSON値は、リレーショナル・データの行を生成するので行ソースと呼ばれます。COLUMNS
句は行ソースを評価し、行ソース内の特定のJSON値を確認し、リレーショナル・データの行の個々の列のSQL値としてそれらのJSON値を戻します。
COLUMNS
句によって、次の句を使用して別の方法でJSON値を検索できます。
JSON_exists_column
- JSON_EXISTS
条件と同じ方法でJSONデータを評価します。つまり、指定されたJSONデータが存在するかどうかを確認し、値'true
'または'false
'のVARCHAR2
列あるいは値1または0のNUMBER
列を戻します。
JSON_query_column
- JSON_QUERY
ファンクションと同じ方法でJSONデータを評価します。つまり、1つ以上の指定されたJSON値を確認し、それらのJSON値を含む文字列の列を戻します。
JSON_value_column
- JSON_VALUE
ファンクションと同じ方法でJSONデータを評価します。つまり、指定されたスカラーJSON値を確認し、SQL値としてそれらのJSON値の列を戻します。
JSON_nested_path
- ネストされたJSONオブジェクトまたはJSON配列のJSON値を親のオブジェクトまたは配列のJSON値とともに単一の行の個々の列にフラット化できます。この句を再帰的に使用して、ネストされたオブジェクトまたは配列の複数のレイヤーから単一の行にデータを投影できます。
ordinality_column
- 生成された行番号の列を戻します。
列定義句を使用すると、戻されるデータの列ごとに名前を指定できます。SELECT
リストやWHERE
句などのSELECT
文でこれらの列名を参照できます。
expr
この句は、評価の対象となるJSONデータを指定するために使用します。expr
では、テキスト・リテラルを評価する式を指定します。expr
が列である場合、列のデータ型はVARCHAR2
、CLOB
またはBLOB
のいずれかである必要があります。expr
がNULLの場合はNULLを戻します。
expr
が厳密なまたは緩い構文を使用した整形式のJSONデータのテキスト・リテラルでない場合、このファンクションはデフォルトでnullを戻します。JSON_table_on_error_clause
を使用して、このデフォルトの動作をオーバーライドできます。JSON_table_on_error_clauseを参照してください。
FORMAT JSON
expr
がデータ・タイプBLOB
の列の場合、FORMAT
JSON
を指定する必要があります。
JSON_path_expression
この句を使用して、行パス式を指定します。ファンクションは、行パス式を使用してexpr
を評価し、パス式と一致する(パス式を満たす)行ソースと呼ばれるJSON値を確認します。この行ソースは、COLUMNS
句によって評価されます。パス式はテキスト・リテラルである必要があります。
JSON_path_expression
句は、JSON_TABLE
およびJSON_QUERY
に対して同じセマンティクスを持ちます。この句の完全なセマンティクスは、JSON_QUERYのドキュメントのJSON_path_expressionを参照してください。
JSON_table_on_error_clause
この句を使用して、次のエラーが発生した場合にこのファンクションで戻される値を指定します。
expr
が厳密なまたは緩いJSON構文を使用した整形式のJSONデータではありません。
JSONデータが行パス式を使用して評価される場合に一致が見つかりません。
次の句を指定できます。
NULL
ON
ERROR
- エラーが発生した場合にnullを戻します。これはデフォルトです。
ERROR
ON
ERROR
- エラーが発生した場合に適切なOracleエラーを戻します。
DEFAULT
literal
ON
ERROR
- エラーが発生した場合にliteral
を戻します。このファンクションで戻される値のデータ型がVARCHAR2
である場合、テキスト・リテラルを指定する必要があります。データ型がNUMBER
である場合、数値リテラルを指定する必要があります。
JSON_columns_clause
COLUMNS
句を使用して、JSON_TABLE
ファンクションで戻される仮想リレーショナル表の列を定義します。
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データを評価します。つまり、1つ以上の指定されたJSON値を確認し、それらのJSON値を含む文字列の列を戻します。
column_name
を使用して、戻される列の名前を指定します。JSON_query_column
の残りの句は、JSON_QUERY
ファンクションと同じセマンティクスを持ちます。これらの句の詳細は、JSON_QUERYを参照してください。「JSON_query_columnの使用: 例」の例も参照してください。
JSON_value_column この句は、JSON_VALUE
ファンクションと同じ方法でJSONデータを評価します。つまり、指定されたスカラーJSON値を確認し、SQL値としてそれらのJSON値の列を戻します。
column_name
を使用して、戻される列の名前を指定します。JSON_value_column
の残りの句は、JSON_VALUE
ファンクションと同じセマンティクスを持ちます。これらの句の詳細は、JSON_VALUEを参照してください。「JSON_value_columnの使用: 例」の例も参照してください。
JSON_nested_path この句を使用して、ネストされたJSONオブジェクトまたはJSON配列のJSON値を親のオブジェクトまたは配列のJSON値とともに単一の行の個々の列にフラット化できます。この句を再帰的に使用して、ネストされたオブジェクトまたは配列の複数のレイヤーから単一の行にデータを投影できます。
JSON_path_expression
句を指定して、ネストされたオブジェクトまたは配列と照合します。このパス式は、JSON_TABLE
ファンクションで指定された行パス式に相対的になります。
COLUMNS
句を使用して、戻されるネストされたオブジェクトまたは配列の列を定義します。この句は再帰的です。つまり、別のJSON_nested_path
句内にJSON_nested_path
句を指定できます。「JSON_nested_pathの使用: 例」の例も参照してください。
ordinality_column この句は、データ型NUMBER
の生成された行番号の列を戻します。1つのordinality_column
のみ指定できます。ordinality_column
句を使用した例は、「JSON_value_columnの使用: 例」も参照してください。
例
JSONドキュメントを含む表の作成: 例 この例は、このセクションの残りのJSON_TABLE
例で使用される表j_purchaseorder
の作成方法および移入方法を示します。
次の文は、表j_purchaseorder
を作成します。列po_document
はJSONデータを格納するために使用され、整形式のJSONのみを列に格納するためにIS
JSON
CHECK制約があります。
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));
次の文は、1つの行または1つの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データを問い合せて、列のプロパティ値を戻します。
この文は、最初に行パス式を列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"}]
JSON_value_columnの使用: 例 この例の文は、JSON_value_column
句を使用して特定のJSON値のJSONデータを問い合せて前の例の文を改良し、リレーショナル行および列のSQL値としてJSON値を戻します。
この文は、最初に行パス式を列po_document
に適用し、これによってJSON配列Phone
の要素と一致します。これらの要素は、type
とnumber
の2つのメンバーを含むJSONオブジェクトです。この文は、COLUMNS
句を使用して、phone_type
と呼ばれるVARCHAR2(10)
列の各オブジェクトのtype
値およびphone_num
と呼ばれるVARCHAR2(20)
列の各オブジェクトのnumber
値を戻します。この文は、row_number
と呼ばれる順序列も戻します。
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
'値としてテストの結果を戻します。2番目の例は、WHERE
句のテストの結果を使用します。
次の文は、最初に行パス式を列po_document
に適用し、これによってコンテキスト項目全体またはJSONドキュメントと一致します。次に、COLUMNS
句を使用して、リクエスタの名前およびリクエスタのJSONデータに郵便番号が含まれるかどうかを示す'true
'または'false
'の文字列値を戻します。COLUMNS
句は最初にJSON_value_column
句を使用して、requestor
と呼ばれるVARCHAR2(32)
列のRequestor
値を戻します。次に、JSON_exists_column
句を使用して、zipCode
オブジェクトがあるかどうかを判断し、has_zip
と呼ばれるVARCHAR2(5)
列の結果を戻します。
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
次の文は、WHERE
句のhas_zip
の値を使用して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の使用: 例 次の2つの簡単な文は、JSON_nested_path
句の機能を示します。3つの要素を含む簡単なJSON配列を操作します。最初の2つの要素は数値です。3つ目の要素は、2つの文字列値要素を含むネストされたJSON配列です。
次の文は、JSON_nested_path
句を使用しません。単一の行の配列の3つの要素を戻します。ネストされた配列はすべて戻します。
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オブジェクトとともにJSON_nested_path
句を使用する方法を示します。
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