| Oracle® Database SQL言語リファレンス 12cリリース1 (12.1) B71278-13 |
|
 前 |
 次 |
|
注意: 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
