15 SQL/JSONファンクションJSON_VALUE
SQL/JSONファンクションjson_value
は、JSONデータからスカラー値を選択し、これをSQL値として戻します。
また、json_value
を使用して、JSONデータで使用する関数ベースのBツリー索引を作成することもできます。JSONデータの索引を参照してください。
関数json_value
は、2つの必須引数を持ち、オプションのRETURNING句およびエラー句を受け入れます。
json_value
の最初の引数は、スカラーのSQLデータ型のインスタンスを戻すSQL式です(つまり、オブジェクト・データ型でもコレクション・データ型でもありません)。これは、データ型VARCHAR2
、BLOB
またはCLOB
のいずれかになります。これは、適切にキャストされた表またはビューの列の値、PL/SQL変数、バインド変数のいずれかになります。SQL式の評価の結果は、パス式を評価するためのコンテキスト項目として使用されます。
json_value
の2番目の引数はSQL/JSONパス式であり、オプションでRETURNING
句、ON ERROR
句およびON EMPTY
句が付加されます。このパス式では単一のスカラー値を対象とする必要があり、そうでない場合、エラーが発生します。
デフォルトのエラー処理動作はNULL ON ERROR
であり、これは、エラーが発生しても値が戻されない、つまり、エラーが発生しないことを意味します。特に、パス式が配列などの非スカラー値を対象としている場合、デフォルトではエラーは発生しません。エラーが発生するようにするには、ERROR ON ERROR
を使用します。
注意:
特定のJSONオブジェクト内のフィールド名は、それぞれが一意である必要はありません。同じフィールド名を繰り返すことができます。Oracle Databaseで行われるストリーム評価では、特定のフィールド名を持つ1つのオブジェクト・メンバーのみが常に使用され、同じフィールド名を持つ他のメンバーは無視されます。このような複数のメンバーのうちどれが使用されるかは指定されていません。
JSONオブジェクトの一意フィールドと重複フィールドを参照してください。
関連項目:
json_value
の詳細は、Oracle Database SQL言語リファレンスを参照してください。
- SQL/JSONファンクションJSON_VALUEとブール型のJSON値の使用
JSONには、ブール型の値true
およびfalse
があります。SQL/JSONファンクションjson_value
がSQL/JSONパス式を評価し、結果がJSONtrue
またはfalse
の場合、BOOLEAN
値としてPL/SQLに戻すことができます。あるいは、VARCHAR2
値'true'
または'false'
としてSQLに戻すこともできます。 - JSONのnull値に適用されるSQL/JSONファンクションJSON_VALUE
SQL/JSONファンクションjson_value
がJSON値null
に適用されると、SQL文字列の'null'
ではなく、SQLNULL
が戻されます。つまり、特に、json_value
を使用してJSON値null
と値が存在しないことの区別はできません。この場合、SQLのNULL
は両方の事例を表します。 - JSON_TABLEとしてのJSON_VALUE
SQL/JSONファンクションjson_value
は、ファンクションjson_table
の特別な事例であるとみなすことができます。
関連項目
親トピック: JSONデータの問合せ
15.1 SQL/JSONファンクションJSON_VALUEとブール型のJSON値の使用
JSONには、ブール型の値true
およびfalse
があります。SQL/JSONファンクションjson_value
がSQL/JSONパス式を評価し、結果がJSON true
またはfalse
の場合、BOOLEAN
値としてPL/SQLに戻すことができます。あるいは、VARCHAR2
値'true'
または'false'
としてSQLに戻すこともできます。
PL/SQLコードでは、BOOLEAN
は、組込みのPL/SQLファンクションjson_value
に対する有効なPL/SQL戻り型です。例15-1に、これを示します。
Oracle SQLにはブール・データ型がないため、文字列(VARCHAR2
)値を使用して、JSONのブール値を戻します。例15-2に、これを示します。:問合せは文字列'true'
を戻します。
SQL/JSONファンクションjson_table
は、json_value
などの他のSQL/JSON問合せファンクションを一般化します。これを使用してJSONのブール値を投影する場合、json_value
が暗黙的に使用され、生成されるSQL値はVARCHAR2
値として戻されます。そのため、投影された列のデータ型はVARCHAR2
である必要があります。
例15-1 JSON_VALUE: BOOLEANとしてPL/SQLに戻されるJSONのブール値
また、PL/SQLには例外処理もあります。この例では句ERROR ON ERROR
を使用して、エラーがあった場合にエラーが発生するようにしています(ユーザー・コードで処理できる)。
DECLARE
b BOOLEAN;
jsonData CLOB;
BEGIN
SELECT po_document INTO jsonData FROM j_purchaseorder WHERE rownum = 1;
b := json_value(jsonData, '$.AllowPartialShipment'
RETURNING BOOLEAN
ERROR ON ERROR);
END;
/
例15-2 JSON_VALUE: VARCHAR2としてSQLに戻されるJSONのブール値
SELECT json_value(po_document, '$.AllowPartialShipment')
FROM j_purchaseorder;
15.2 JSONのnull値に適用されるSQL/JSONファンクションJSON_VALUE
SQL/JSONファンクションjson_value
がJSON値null
に適用されると、SQL文字列の'null'
ではなく、SQL NULL
が戻されます。つまり、特に、json_value
を使用してJSON値null
と値が存在しないことの区別はできません。この場合、SQLのNULL
は両方の事例を表します。
親トピック: SQL/JSONファンクションJSON_VALUE
15.3 JSON_TABLEとしてのJSON_VALUE
SQL/JSONファンクションjson_value
は、関数json_table
の特別な事例であるとみなすことができます。
例15-3に、この対応を示します。2つのSELECT
文で得られる結果は同じになります。
この対応は、おそらくjson_value
についてより深く理解する手助けとなるのみでなく、どちらの関数を使用しても同じ結果が得られることを意味しているため、実質的に重要な意味を持ちます。
特に、json_value
を複数回使用する場合、またはこれをjson_exists
またはjson_query
(これらもjson_table
を使用して表すことができます)と組み合せて使用して同じデータにアクセスする場合、json_table
を1回呼び出す方が、データが解析されるのが1回のみであるという利点があります。
このため、オプティマイザがjson_exists
、json_value
およびjson_query
の複数の呼出し(任意の組合せ)を、より少ないjson_table
の呼出しに自動的にリライトすることがよく起こります。
例15-3 JSON_TABLEを使用して表されたJSON_VALUE
SELECT json_value(column, json_path RETURNING data_type error_hander ON ERROR)
FROM table;
SELECT jt.column_alias
FROM table,
json_table(column, '$' error_handler ON ERROR
COLUMNS ("COLUMN_ALIAS" data_type PATH json_path)) AS "JT";