16 JSONデータに対する単純なドット表記法アクセス

ドット表記法は、JSONデータの問合せの簡単で一般的な、共通するユースケース向けに設計されています。単純な問合せの場合は、SQL/JSON問合せファンクションのかわりに使用すると役立ちます。

SQL/JSON問合せファンクションの場合と同様に、問合せ対象のJSON列は、整形式のJSONデータのみが含まれることが認識されている必要があります。つまり、(1)データ型がJSON、VARCHAR2、CLOBまたはBLOBである必要があり、(2)型がJSONでない場合は、列にis jsonチェック制約が必要となります。

次の問合せは、JSON列dataからフィールドPONumberの値を選択し、それをJSON値として返します。 

SELECT po.data.PONumber FROM j_purchaseorder po;

返される値は、列がJSON型の場合はJSONデータ型のインスタンスで、それ以外の場合はVARCHAR2(4000)値です。

ただし、SQLではJSON値は一般的に便利とは言えません。JSONデータを返すのではなく、SQLスカラー・データ型(JSONでない)のインスタンスを返す必要があることもよくあります。これを行うには、対象となるデータに項目メソッドを適用します。次の問合せは、前述の問合せと同様にPONumberフィールドの値を選択しますが、SQLのNUMBER値として返します。 

SELECT po.data.PONumber.number() FROM j_purchaseorder po;

項目メソッドは、対象となるJSONデータを変換します。変換されたデータは、その元のデータのかわりに問合せで処理されて返されます。ドット表記法構文を使用する場合は、一般に項目メソッドを使用します。

項目メソッドを使用するドット表記法問合せは、常に(JSON以外の) SQLスカラー値を返します。これには、SQL/JSONファンクションjson_valueを使用して、JSONスカラー値をSQLスカラー値に変換する効果があります。

項目メソッドを使用しないドット表記法問合せは、常にJSONデータを返します。これには、SQL/JSONファンクションjson_query (または、json_queryのセマンティクスを持つ列があるjson_table)を使用する効果があります。

例16-1は、ドット表記法の問合せとjson_valueの問合せの対応を示しています。例16-2は、ドット表記法の問合せとjson_queryの問合せの対応を示しています。

項目メソッドを使用したドット表記法

項目メソッドを使用するドット表記法問合せは、スカラーSQL型(項目メソッドで指定された型)を返すRETURNING句を含むjson_value問合せと同等です。

たとえば、数値に変換可能なJSONデータに項目メソッドnumber()を適用すると、結果はSQLのNUMBER値になります。サポートされているISO 8601の日付または日時形式のデータに項目メソッドdate()を適用すると、結果はSQLのDATE値になります。

ノート:

問合せ結果にJSON文字列が含まれていて、その結果がシリアライズされている場合、文字列はテキスト形式で表示されます。この形式では、その内容が二重引用符文字(")で囲まれ、内容の一部の文字がエスケープされることがあります。

シリアライズは、一部のケースでは暗黙的に行われます(たとえば、SQL*Plusなどのクライアントを使用する場合)。

t.jcol列がJSONデータ型で、内容が{"name":"orange"}であるとします。次のSQL*Plusの問合せは、二重引用符文字を使用して、JSONデータ型のJSON文字列である結果を出力します。 

SELECT t.data.name FROM fruit t;

NAME
----
"orange"

項目メソッドstring()を使用すると、同じ内容を持つSQL文字列にJSON文字列を変換できます。SQL*Plusでは、結果が(一重または二重)引用符で囲まれずにシリアライズ(出力)されます。 

SELECT t.data.name.string() FROM fruit t;

NAME.STRING()
-------------
orange

項目メソッドを使用しないドット表記法

ドット表記法問合せで項目メソッドを使用しない場合は、JSONデータを表すSQL値が返されます。この場合、次のようになります:

  • 問い合せたデータがJSON型の場合は、返されるデータもJSON型になります。

  • それ以外の場合、問い合せたデータがテキスト(VARCHAR2、CLOBまたはBLOB)の場合、返されるデータはVARCHAR2(4000)型になります。

ノート:

戻りデータ型がVARCHAR2(4000)の場合、選択した値が4000バイトを超えると、NULLがデフォルトで返されます。かわりにエラーを生成するには、パラメータJSON_BEHAVIORを使用します: 

ALTER SESSION SET JSON_BEHAVIOR="ON_ERROR:ERROR";

例18-1を参照してください。

ドット表記法問合せで項目メソッドを使用しない場合、返されるJSONデータは、次のように対象となるJSONデータによって異なります。

  • 単一のJSON値が対象である場合、JSONスカラー、オブジェクトまたは配列のいずれであるかに関係なく、その値が返されます。

  • 複数のJSON値が対象である場合、これらの値が要素であるJSON配列が返されます。(配列要素の順序は定義されません。)

この動作は、SQL/JSONファンクションjson_valueおよびjson_queryとは対照的です。これらの関数は、より複雑な問合せに使用できます。これらの関数は、指定したパス式が問合せ対象のJSONデータと一致しない場合には、NULLを戻すか、エラーを発生させます。また、オプションの句を受け入れるため、そこで戻り値のデータ型の指定(RETURNING句)、複数の値を配列としてラップするかどうか(ラッパー句)、通常のエラーの処理方法(ON ERROR句)、欠落したJSONフィールドの処理方法(ON EMPTY句)を指定できます。

単一のJSON値が対象である場合、ドット表記法の動作は、スカラーJSON値の場合はjson_value関数の動作と似ており、オブジェクトまたは配列値の場合はjson_queryの動作と似ています。

複数の値が対象である場合、動作は配列ラッパーを使用したjson_queryの動作と似ています。

ドット表記法構文

ドット表記法構文は、表の別名(必須)に続いて、ドット(つまりピリオド(.))、JSON列の名前、. json_fieldの形式または. json_fieldに続いてarray_stepの形式の1つ以上のペアが続きます。このjson_fieldはJSONフィールド名で、array_stepは配列ステップ式です(基本的なSQL/JSONパス式の構文で説明しています)。

各json_fieldは有効なSQL識別子の構文である必要があり脚注1、その列はJSONデータ型であるかis jsonチェック制約が設定されている必要があります。これにより、それに整形式のJSONデータが含まれるようにします。これらの規則のどちらも順守されない場合、問合せのコンパイル時にエラーが発生します。(列がJSONデータ型ではない場合は、エラーが発生しないようにチェック制約が存在する必要がありますが、アクティブである必要はありません。制約を非アクティブ化すると、このエラーは発生しません。)

JSON問合せのドット表記法の場合、SQLの一般的な事例とは異なり、引用符で囲まれていない識別子(列名の後ろ)は、大文字と小文字を区別して(つまり、引用符で囲まれているかのように)処理されます。これは大変都合がよく、JSONフィールド名を引用符で囲まなくても識別子として使用できます。たとえば、t.jcolumn."friends"ではなく、t.jcolumn.friendsと入力してかまいません(意味は同じです)。これは、名前が大文字であるJSONフィールド(FRIENDSなど)を問い合せる場合、t.jcolumn.friendsではなくt.jcolumn.FRIENDSと記述する必要があることも意味します。

次に、ドット表記法構文の例をいくつか示します。これらはすべて、poという別名がある表のJSON列dataを参照しています。

  • po.data.PONumber – JSON値としての、フィールドPONumberの値。この値は、列dataがJSON型である場合はJSON型のインスタンスとして返され、そうでない場合はSQLのVARCHAR2(4000)値として返されます。

  • po.data.PONumber.number() – SQLのNUMBER値としての、フィールドPONumberの値。これは項目メソッドnumber()によって行われます。

  • po.data.LineItems[1] – 配列LineItemsの2番目の要素(配列位置はゼロベース)。JSONデータとして返されます(列のデータ型に応じてJSON型またはVARCHAR2(4000))。

  • po.data.LineItems[*] – JSONデータとしての、配列LineItemsのすべての要素(*はワイルドカード)。

  • po.data.ShippingInstructions.name – JSONデータとしての、フィールドnameの値(このフィールドは、フィールドShippingInstructionsの値であるオブジェクトの子)。

JSONデータに対するJSONドット表記法の式の照合は、SQL/JSONパス式の場合と同じであり、これには、暗黙の配列反復を許可する緩和が含まれます(SQL/JSONパス式の構文の緩和を参照)。ドット表記法の式のJSON列はパス式のコンテキスト項目に相当し、ドット表記法で使用される各識別子は、パス式で使用される各識別子に相当します。

たとえば、JSON列jcolumnがパス式のコンテキスト項目に相当する場合、式jcolumn.friendsはパス式$.friendsに相当し、jcolumn.friends.nameはパス式$.friends.nameに相当します。

後者の例の場合、コンテキスト項目はオブジェクトでもオブジェクトの配列でもかまいません。これがオブジェクトの配列である場合、配列内の各オブジェクトはフィールドfriendsに対して照合されます。フィールドfriendsの値自体はオブジェクトでもオブジェクトの配列でもかまいません。後者の場合、配列内の最初のオブジェクトが使用されます。

ノート:

(1)配列要素に対してワイルドカードを暗黙で使用する(SQL/JSONパス式の構文の緩和を参照)、(2)配列の大カッコ間にワイルドカードを明示的に使用する([*])以外に、ドット表記法構文を使用する際には、パス式でワイルドカードを使用することはできません。これは、アスタリスク(*)が有効なSQL識別子ではないためです。

たとえば、次のように指定すると構文エラーが発生します: mytable.mycolumn.object1.*.object2。

ドット表記法構文は、単純なパス式の使用に対する便利な代替手段ですが、一般的なパス式の使用の代用手段ではありません。

関連項目:

SQLオブジェクトおよびオブジェクト属性へのアクセス(オブジェクト・アクセス式)に使用されるドット表記法の詳細は、Oracle Database SQL言語リファレンスを参照してください。

例16-1 JSONドット表記法の問合せとJSON_VALUEの比較

例4-3のデータを使用した場合、次の各問合せはJSONの数値1600を返します。JSON列がテキストである(JSON型ではない)場合、問合せはJSONの数値を表すVARCHAR2文字列'1600'を返します。 

SELECT po.data.PONumber FROM j_purchaseorder po;

SELECT json_value(data, '$.PONumber') FROM j_purchaseorder;

次の各問合せでは、SQLのNUMBER値1600が返されます。 

SELECT po.data.PONumber.number() FROM j_purchaseorder po;

SELECT json_value(data, '$.PONumber.number()')
  FROM j_purchaseorder;

例16-2 JSONドット表記法の問合せとJSON_QUERYの比較

次の各問合せは、電話オブジェクトのJSON配列を返します。JSON列がテキストである(JSON型ではない)場合、問合せは配列を表すVARCHAR2値を返します。 

SELECT po.data.ShippingInstructions.Phone
  FROM j_purchaseorder po;

SELECT json_query(data, '$.ShippingInstructions.Phone')
  FROM j_purchaseorder;

次の各問合せは、例21-1と同様に電話タイプの配列を返します。JSON列がテキストである(JSON型ではない)場合、問合せは配列を表すVARCHAR2値を返します。 

SELECT po.data.ShippingInstructions.Phone.type
  FROM j_purchaseorder po;

SELECT json_query(data, '$.ShippingInstructions.Phone.type'
                  WITH WRAPPER)
  FROM j_purchaseorder;

関連トピック

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


脚注の凡例

脚注1: これは、特に、ドット表記法構文では空白のフィールド名("")を使用できないことを意味します。