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

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

ドット表記法は、簡単で一般的な、共通するユースケース向けに設計されています。ドット表記法構文を使用したJSONデータの問合せでは、可能な場合は必ずJSON値が戻されます。

ドット表記法の問合せに対する戻り値は常に、JSONデータを表す文字列(データ型VARCHAR2(4000))です。文字列の内容は、次のように対象のJSONデータによって決まります。

単一のJSON値が対象である場合、この値はJSONスカラー、オブジェクトまたは配列のいずれであるかに関係なく文字列内容です。
複数のJSON値が対象である場合、文字列の内容は、これらの値が要素であるJSON配列です。

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

前述の最初の事例におけるドット表記法の動作は、スカラー値の場合はjson_value関数の動作と似ており、オブジェクトまたは配列値の場合はjson_queryの動作と似ています。2番目の事例における動作は、配列ラッパーを使用したjson_queryの動作と似ています。

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

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

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

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

po.po_document.PONumber – フィールドPONumberの値。
po.po_document.LineItems[1] – 配列LineItemsの2つ目の要素(配列の位置は0が基準)。
po.po_document.LineItems[*] – 配列LineItemsのすべての要素(*はワイルドカード)。
po.po_document.ShippingInstructions.name – オブジェクトShippingInstructionsの子であるフィールドnameの値。

ノート:

ドット表記法構文の各コンポーネントは最大128バイトに制限されています。

SQLドット表記法構文およびSQL識別子の詳細は、Oracle Database SQL言語リファレンスを参照してください。
単純なドット表記法のJSON問合せでは、4Kバイトよりも長い値は戻せません。値がこの制限を超えると、かわりにSQL NULLが戻されます。実際の値を取得するには、ドット表記法のかわりにSQL/JSONファンクションjson_queryまたはjson_valueを使用し、RETURNING句で適切な戻り型を指定します。

JSONドット表記法構文の詳細は、Oracle Database SQL言語リファレンスを参照してください。

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。

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

例14-1は、ドット表記法の問合せとjson_valueの問合せの対応を示しています。例4-2のデータが与えられた場合、各問合せにより、文字列"1600" (JSON数値1600を表すVARCHAR2値)が戻されます。

例14-2は、ドット表記法の問合せとjson_queryの問合せの対応を示しています。最初のペアの各問合せにより、電話オブジェクトのJSON配列(を表す値VARCHAR2)が戻されます。2番目のペアの各問合せにより、例19-1の場合のように電話のタイプの配列(を表すVARCHAR2値)が返されます。