13 JSONデータに対する単純なドット表記法アクセス
ドット表記法は、JSONデータの問合せの簡単で一般的な、共通するユースケース向けに設計されています。単純な問合せの場合は、SQL/JSON問合せファンクションのかわりに使用すると役立ちます。
SQL/JSON問合せファンクションの場合と同様に、問合せ対象のJSON列は、整形式のJSONデータのみが含まれることが認識されている必要があります。つまり、(1)データ型がJSON
、VARCHAR2
、CLOB
またはBLOB
である必要があり、(2)型がJSON
でない場合は、列にis json
チェック制約が必要となります。
次の問合せは、JSON列po_document
からPONumber
フィールドの値を選択し、JSON値として返します。
SELECT po.po_document.PONumber FROM j_purchaseorder po;
返される値は、列がJSON
型の場合はJSON
データ型のインスタンスで、それ以外の場合はVARCHAR2(4000)
値です。
ただし、SQLではJSON値は一般的に便利とは言えません。特に、SQLのORDER BY
またはGROUP BY
で使用できず、比較操作または結合操作で使用できません。JSONデータは比較できません。脚注1
JSONデータを返すかわりに、(JSON
以外の) SQLスカラー・データ型(これは比較できます)のインスタンスを返すことができます。これを行うには、対象となるデータに項目メソッドを適用します。次の問合せは、前述の問合せと同様にPONumber
フィールドの値を選択しますが、SQLのNUMBER
値として返します。
SELECT po.po_document.PONumber.number() FROM j_purchaseorder po;
項目メソッドは、対象となるJSONデータを変換します。変換されたデータは、その元のデータのかわりに問合せで処理されて返されます。ドット表記法構文を使用する場合は、一般に項目メソッドを使用します。
項目メソッドを使用するドット表記法問合せは、常に(JSON
以外の) SQLスカラー値を返します。これには、SQL/JSONファンクションjson_value
を使用して、JSONスカラー値をSQLスカラー値に変換する効果があります。
項目メソッドを使用しないドット表記法問合せは、常にJSONデータを返します。これには、SQL/JSONファンクションjson_query
(または、json_query
のセマンティクスを持つ列があるjson_table
)を使用する効果があります。
例13-1は、ドット表記法の問合せとjson_value
の問合せの対応を示しています。例13-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)
型になります。
ドット表記法問合せで項目メソッドを使用しない場合、返される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識別子の構文である必要があり脚注2、列がJSON
データ型であるか、is json
チェック制約が設定されている必要があり、これにより、整形式のJSONデータが含まれるようにします。これらの規則のどちらも順守されない場合、問合せのコンパイル時にエラーが発生します。(列がJSON
データ型ではない場合は、エラーが発生しないようにチェック制約が存在する必要がありますが、アクティブである必要はありません。制約を非アクティブ化すると、このエラーは発生しません。)
JSON問合せのドット表記法の場合、SQLの一般的な事例とは異なり、引用符で囲まれていない識別子(列名の後ろ)は、大文字と小文字を区別して(つまり、引用符で囲まれているかのように)処理されます。これは大変都合がよく、JSONフィールド名を引用符で囲まなくても識別子として使用できます。たとえば、t.jcolumn."friends"
ではなく、t.jcolumn.friends
と入力してかまいません(意味は同じです)。これは、名前が大文字であるJSONフィールド(FRIENDS
など)を問い合せる場合、t.jcolumn.friends
ではなくt.jcolumn.FRIENDS
と記述する必要があることも意味します。
次に、ドット表記法構文の例をいくつか示します。これらはすべて、po
という別名を持つ表のJSON列po_document
を参照しています。
-
po.po_document.PONumber
–PONumber
フィールドのJSON値としての値。po_document
列がJSON
型の場合、この値はJSON
型インスタンスとして返され、そうでない場合はSQLのVARCHAR2(4000)
値として返されます。 -
po.po_document.PONumber.number()
–PONumber
フィールドのSQLのNUMBER
値としての値。これは項目メソッドnumber()
によって行われます。 -
po.po_document.LineItems[1]
- 配列LineItems
の2番目の要素(配列位置はゼロ・ベース)。JSONデータとして返されます(列のデータ型に応じてJSON
型またはVARCHAR2(4000)
)。 -
po.po_document.LineItems[*]
– JSONデータとしての配列LineItems
のすべての要素(*
はワイルドカード)。 -
po.po_document.ShippingInstructions.name
-ShippingInstructions
フィールドの値であるオブジェクトの子であるname
フィールドのJSONデータとしての値。
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言語リファレンスを参照してください。
例13-1 JSONドット表記法の問合せとJSON_VALUEの比較
例4-3のデータを使用した場合、次の各問合せはJSONの数値1600
を返します。JSON列がテキストである(JSON
型ではない)場合、問合せはJSONの数値を表すVARCHAR2
文字列'1600'
を返します。
SELECT po.po_document.PONumber FROM j_purchaseorder po;
SELECT json_value(po_document, '$.PONumber') FROM j_purchaseorder;
次の各問合せでは、SQL
のNUMBER
値1600
が返されます。
SELECT po.po_document.PONumber.number() FROM j_purchaseorder po;
SELECT json_value(po_document, '$.PONumber.number()') FROM j_purchaseorder;
例13-2 JSONドット表記法の問合せとJSON_QUERYの比較
次の各問合せは、電話オブジェクトのJSON配列を返します。JSON列がテキストである(JSON
型ではない)場合、問合せは配列を表すVARCHAR2
値を返します。
SELECT po.po_document.ShippingInstructions.Phone
FROM j_purchaseorder po;
SELECT json_query(po_document, '$.ShippingInstructions.Phone')
FROM j_purchaseorder;
次の各問合せは、例18-1と同様に電話タイプの配列を返します。JSON列がテキストである(JSON
型ではない)場合、問合せは配列を表すVARCHAR2
値を返します。
SELECT po.po_document.ShippingInstructions.Phone.type
FROM j_purchaseorder po;
SELECT json_query(po_document, '$.ShippingInstructions.Phone.type'
WITH WRAPPER)
FROM j_purchaseorder;
親トピック: JSONデータの問合せ
脚注の凡例
脚注1: JSONデータがJSON
データ型ではなくテキストの場合、照合ルールに従って文字列として比較できますが、JSONデータとしては比較できません。脚注2: 特に、ドット表記法構文では空のフィールド名(
""
)を使用できないことを意味します。