JSON_TABLE
構文
(JSON_basic_path_expression: Oracle Database JSON開発者ガイドを参照、JSON_table_on_error_clause::=、JSON_columns_clause::=)
JSON_table_on_error_clause::=
JSON_table_on_empty_clause::=
JSON_columns_clause::=
JSON_column_definition::=
JSON_exists_column::=
(JSON_value_return_type::=(JSON_VALUEの一部)、JSON_basic_path_expression: Oracle Database JSON開発者ガイドを参照、JSON_exists_on_error_clause::=(JSON_EXISTSの一部))
JSON_query_column::=
(JSON_query_return_type::=、JSON_query_wrapper_clause::=、JSON_query_on_error_clause::=(JSON_QUERYの一部)、JSON_basic_path_expression: Oracle Database JSON開発者ガイドを参照)
JSON_value_column::=
(JSON_value_return_type::=およびJSON_value_on_error_clause::=(JSON_VALUEの一部)、JSON_basic_path_expression: Oracle Database JSON開発者ガイドを参照)
JSON_nested_path::=
(JSON_basic_path_expression: Oracle Database JSON開発者ガイドを参照、JSON_columns_clause::=)
ordinality_column::=
JSON_path ::=
JSON_relative_object_access ::=
目的
SQL/JSONファンクションJSON_TABLEは、JSONデータのリレーショナル・ビューを作成します。JSONデータの評価結果をリレーショナル行および列にマップします。SQLを使用して、このファンクションで戻される結果を仮想リレーショナル表として問い合せることができます。JSON_TABLEの主な目的は、JSON配列内の各オブジェクトのリレーショナル・データの行を作成し、個々のSQL列値としてそのオブジェクト内からJSON値を出力することです。
SELECT文のFROM句にのみ、JSON_TABLEを指定する必要があります。ファンクションは、最初にSQL/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文でこれらの列名を参照できます。
関連項目:
JSON_TABLEによって生成される表の、文字データ型の列ごとに割り当てる照合を定義する照合導出ルールは、『Oracle Databaseグローバリゼーション・サポート・ガイド』の付録Cを参照してください。
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を指定する必要があります。
PATH
PATH句を使用して、列の内容として使用する行の一部を記述します。PATH句がない場合、パス'$.<column-name>'(<column-name>は列名)での動作は変更されません。ターゲットであるオブジェクト・フィールド名が列名として暗黙的に取得されます。PATHの完全なセマンティクスについては、『Oracle Database JSON開発者ガイド』を参照してください。
JSON_basic_path_expression
JSON_basic_path_expressionはテキスト・リテラルです。この句のセマンティクスの詳細は、Oracle Database JSON開発者ガイドを参照してください。
JSON_relative_object_access
単純なドット表記法を有効にするには、この行パス式を指定します。JSON_relative_object_accessの値は、現在の行アイテムに関連したJSON/パス式として評価されます。
JSON_object_key句の詳細は、JSONオブジェクト・アクセス式を参照してください。
JSON_table_on_error_clause
この句を使用して、エラーが発生した場合にこのファンクションで戻される値を指定します。
-
NULL ON ERROR-
入力が整形式の
JSONテキストではない場合、エラーが検出されると即時にそれ以降の行が戻されなくなります。JSON_TABLEではストリーミング評価がサポートされているため、入力のエラーの部分が検出される前に行が戻される可能性があることに注意してください。 -
行パス式の評価時に一致が見つからない場合は、行が返されません。
-
すべての列式のデフォルト・エラー動作を
NULL ON ERRORに設定します。
-
-
ERROR ON ERROR-
入力が整形式の
JSONテキストではない場合、エラーが発生します。 -
行パス式の評価時に一致が見つからない場合は、エラーが発生します。
-
すべての列式のデフォルト・エラー動作を
ERROR ON ERRORに設定します。
-
JSON_table_on_empty_clause
この句を使用して、JSONデータがSQL/JSONパス式を使用して評価されるときに一致が見つからない場合にこのファンクションで戻される値を指定します。この句により、JSON_table_on_error_clauseで指定された結果とは異なる、このタイプのエラーに対する結果を指定できます。
次の句を指定できます。
-
NULLONEMPTY- 一致が見つからない場合にNULLを戻します。 -
ERRORONEMPTY- 一致が見つからない場合に適切なOracleエラーを戻します。 -
DEFAULTliteralONEMPTY- 一致が見つからない場合に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条件」を参照してください。「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_basic_path_expression句を指定して、ネストされたオブジェクトまたは配列と照合します。このパス式は、JSON_TABLEファンクションで指定されたSQL/JSON行パス式に相対的になります。
COLUMNS句を使用して、戻されるネストされたオブジェクトまたは配列の列を定義します。この句は再帰的です。つまり、別のJSON_nested_path句内にJSON_nested_path句を指定できます。「JSON_nested_pathの使用方法: 例」の例も参照してください。
ordinality_column
この句では、データ型がNUMBERの、生成された行番号の列を戻します。1つのordinality_columnのみ指定できます。ordinality_column句を使用した例は、「JSON_value_columnの使用方法: 例」も参照してください。
nested_clause
nested_clauseは、JSON値をリレーショナルの列にマッピングする簡易構文として使用します。JSON_TABLE columns句の構文を再利用し、基本的にはJSON_TABLEでの左外部ANSI結合と等価です。
nested_clauseを使用する例1は、JSON_TABLEで左外部結合を使用する例2と等価です。
例1 Nested_Clause
SELECT t.*
FROM j_purchaseOrder
NESTED po_document COLUMNS(PONumber, Reference, Requestor) t;
PONUMBER REFERENCE REQUESTOR
--------------- ------------------------------ -----------------------------
1600 ABULL-20140421 Alexis Bull例2 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は、結果の列の1つとして含まれません。
JSON列データのネストを解除するときには、LEFT OUTER JOINセマンティクスを使用することをお薦めします。行を生成しないJSON列が、他の非JSONデータを結果から除外しないようにするためです。たとえば、NULLのpo_document列がある j_purchaseOrder行は、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 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データを問い合せて、列のプロパティ値を戻します。
この文は、最初にSQL/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値を戻します。
この文は、最初に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句のテストの結果を使用します。
次の文は、最初にSQL/JSON行パス式を列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次の例は、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;