1. JSON開発者ガイド
  2. JSONデータの問合せ
  3. SQL/JSONファンクションJSON_QUERY

18 SQL/JSONファンクションJSON_QUERY

SQL/JSONファンクションjson_queryは、JSONデータから1つ以上の値を選択してそれらの値を返します。したがって、json_queryを使用してJSON文書のフラグメントを取得できます。

ユーザーが問い合せるJSONデータは、json_queryの最初の引数です。より正確には、JSONデータ(JSON脚注1、VARCHAR2、CLOBまたはBLOB型)を含むSQLデータ型のインスタンスを返すSQL式です。これは、適切にキャストされた表またはビューの列の値、PL/SQL変数、バインド変数のいずれかになります。式の評価の結果は、パス式を評価するためのコンテキスト項目として使用されます(次に説明されています)。

json_queryの2番目の引数はSQL/JSONパス式であり、オプションでRETURNING句、WRAPPER句、ON ERROR句およびON EMPTY句が付加されます。このパス式は、任意の数のJSON値を対象とすることができます。

パス式配列ステップでは、指定された各位置は、指定方法に関係なく順番にデータと照合されます。配列の索引と範囲の順序、索引の複数出現、および範囲の重複による指定された位置の重複が、すべて考慮されます。

RETURNING句で指定できるのは、データ型JSON、VARCHAR2、CLOBまたはBLOBです。BLOBの結果はAL32UTF8キャラクタ・セットです。

デフォルトの戻り型は、入力データ型によって異なります。入力の型がJSONの場合は、デフォルトの戻り型もJSONです。それ以外の場合、デフォルトの戻り型はVARCHAR2です。

戻り値には、常に整形式のJSONデータが含まれています。これにより、文字列値の非ASCII文字が必要に応じてエスケープされていることが確認されます。たとえば、ASCIIタブ文字(Unicode文字CHARACTER TABULATION、U+0009)は\tとしてエスケープされます。キーワードFORMAT JSONは、json_queryに必要(または使用可能)ではありません。JSON形式は戻り値に対して暗黙的です。

ラッパー句により、戻される文字列値の形式が決まります。

json_queryのエラー句ではEMPTY ON ERRORを指定でき、これは、エラーの場合は空の配列([])が戻されることを意味します(エラーは発生しない)。

初期化パラメータcompatibleが20以上の場合、Oracle DatabaseではIETF RFC 8259がサポートされ、最上位レベルのJSONスカラー値のみをJSON文書に含めることができます。

compatibleパラメータが20より低い場合は、RFC 4627のみがサポートされます。JSON文書の最上位レベルに、スカラーではなくJSONオブジェクトまたは配列のみを含めることができます。RFC 8259には、RFC 4627 (およびRFC 7159)のサポートが含まれています。

RFC 8259がサポートされておらず、json-queryパス式の引数で対象となる値が複数の値または単一のスカラー値である場合は、キーワードWITH WRAPPERを使用して、配列にラップされた値を返す必要があります。そうでない場合は、エラーが発生します。

RFC 8259がサポートされている場合、json_queryはデフォルトでスカラーJSON値を返すことができます。json_queryでスカラー以外のJSON値のみが返されるようにするには、RETURNING句にキーワードDISALLOW SCALARSを使用します。この場合の動作は、RFC 8259がサポートされていない場合と同じであるため、WITH WRAPPERを使用する必要があります。

例18-1は、SQL/JSONファンクションjson_queryとともに配列ラッパーを使用する例を示しています。文書ごとにVARCHAR2値が戻され、この内容は、電話のタイプの要素が不特定の順序で含まれるJSON配列を表します。例4-3の文書では、電話のタイプは"Office"および"Mobile"であり、戻される配列は["Mobile", "Office"]または["Office", "Mobile"]です。

例18-1でパス式$.ShippingInstructions.Phone.typeが使用されていたとしても、同じ結果が得られるはずであることに注意してください。SQL/JSONのパス式構文の緩和のため、[*].typeは.typeに相当します。

関連項目:

例18-1 JSON_QUERYを使用したJSON値の選択

SELECT json_query(po_document, '$.ShippingInstructions.Phone[*].type'
                  WITH WRAPPER)
  FROM j_purchaseorder;
  • JSON_TABLEとしてのJSON_QUERY
    SQL/JSONファンクションjson_queryは、ファンクションjson_tableの特別な事例であるとみなすことができます。

関連項目

親トピック: JSONデータの問合せ

18.1 JSON_TABLEとしてのJSON_QUERY

SQL/JSONファンクションjson_queryは、関数json_tableの特別な事例であるとみなすことができます。

例18-2に、この対応を示します。2つのSELECT文で得られる結果は同じになります。

この対応は、おそらくjson_queryについてより深く理解する手助けとなるのみでなく、どちらの関数を使用しても同じ結果が得られることを意味しているため、実質的に重要な意味を持ちます。

特に、json_queryを複数回使用する場合、またはこれをjson_existsまたはjson_value (これらもjson_tableを使用して表すことができます)と組み合せて使用して同じデータにアクセスする場合、json_tableを1回呼び出す方が、データが解析されるのが1回のみであるという利点があります。

このため、オプティマイザがjson_exists、json_valueおよびjson_queryの複数の呼出し(任意の組合せ)を、より少ないjson_tableの呼出しに自動的にリライトすることがよく起こります。

例18-2 JSON_TABLEを使用して表されたJSON_QUERY

キーワードFORMAT JSONは、data_typeがJSON型ではない場合にのみ使用します。(キーワードFORMAT JSONはJSON型には使用できません。) 

SELECT json_query(column, json_path
                  RETURNING data_type array_wrapper error_hander ON ERROR)
  FROM table;

SELECT jt.column_alias
  FROM table,
       json_table(column, '$' error_handler ON ERROR
         COLUMNS ("COLUMN_ALIAS" data_type FORMAT JSON array_wrapper
                  PATH json_path)) AS "JT";

関連項目

親トピック: SQL/JSONファンクションJSON_QUERY


脚注の凡例

脚注1: JSONデータ型を使用するには、データベース初期化パラメータcompatibleが少なくとも20である必要があります。