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
で指定された結果とは異なる、このタイプのエラーに対する結果を指定できます。
次の句を指定できます。
-
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条件」を参照してください。「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;