SQL/JSONファンクションJSON

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オブジェクトの一意フィールドと重複フィールドを参照してください。

15.1 SQL/JSONファンクションJSON_VALUEとブール型のJSON値の使用

JSONには、ブール型の値trueおよびfalseがあります。Oracle SQLには、ブール・データ型がありません。SQL/JSONファンクションjson_valueによってSQL/JSONパス式が評価され、結果がtrueまたはfalseである場合、結果をSQLで処理する方法は文字列と数値の2通りあります。

デフォルトでは、戻されるデータ型はSQL文字列(VARCHAR2)であり、これは、結果が文字列'true'または'false'であることを意味します。または、結果をSQL数値として戻すこともでき、この場合、JSON値trueは数値1として戻され、falseは0として戻されます。

例15-1に、これを示します。最初の問合せでは文字列'true'を戻し、2番目の問合せでは数値1を戻します。

PL/SQLコードでjson_valueを使用することもできます。その場合、BOOLEANは、組込みのPL/SQLファンクションjson_valueに対する有効なPL/SQL戻り型です。例15-2に、これを示します。

例15-1 JSON_VALUE: SQLでJSONのブール値を戻す2通りの方法

SELECT json_value(po_document, '$.AllowPartialShipment')
 FROM j_purchaseorder;

SELECT json_value(po_document, '$.AllowPartialShipment' RETURNING NUMBER)
  FROM j_purchaseorder;

例15-2 JSON_VALUEから戻されるBOOLEAN PL/SQL値

Oracle SQLとは異なり、PL/SQLのBOOLEANデータ型は、PL/SQL組込み関数json_valueの戻り値に対して使用できます。

また、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のnull値に適用されるSQL/JSONファンクションJSON_VALUE

SQL/JSONファンクションjson_valueがJSON値nullに適用されると、SQL文字列の'null'ではなく、SQL NULLが戻されます。つまり、特に、json_valueを使用してJSON値nullと値が存在しないことの区別はできません。この場合、SQLのNULLは両方の事例を表します。

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";

関連項目

JSON_TABLEによるSQL/JSON問合せファンクションおよび条件の一般化