17 SQL/JSONファンクションJSON_VALUE
SQL/JSONファンクションjson_valueは、JSONデータを選択し、SQLスカラーやユーザー定義のSQLオブジェクト型またはSQLコレクション型(VARRAY、ネストした表)のインスタンスを返します。
-
json_valueが単一のスカラーのJSON値を対象にする場合、スカラーのSQL値を返します。返されるスカラー値のSQLデータ型を指定できます。デフォルトは、VARCHAR2(4000)です。 -
json_valueがJSON配列を対象にしている場合、SQLコレクション型(VARRAYまたはネストした表)を戻り型として指定すると、json_valueはそのコレクション型のインスタンスを返します。対象のJSON配列の要素は、返されたコレクション型インスタンスの要素を提供します。スカラーのJSON配列要素は、返されたコレクション・インスタンスにスカラーのSQL値を生成します(前を参照)。オブジェクトであるJSON配列要素(次を参照)または配列は再帰的に処理されます。
-
json_valueがJSONオブジェクトを対象にしている場合、ユーザー定義のSQLオブジェクト型を戻り型として指定すると、json_valueはそのオブジェクト型のインスタンスを返します。対象のJSONオブジェクトのフィールド値は、返されたオブジェクト型インスタンスの属性値を提供します。対象のJSONオブジェクトのフィールド名は、SQLオブジェクト属性のSQL名と比較されます。スカラー・フィールド値は、返されたオブジェクト型インスタンスにスカラーのSQL値を生成します(前を参照)。配列であるフィールド値(前を参照)またはオブジェクトが再帰的に処理されます。
最終的に、これはスカラーのSQLオブジェクト属性の名前と比較されるスカラー値を持つJSONフィールドの名前です。大/小文字を区別して名前が正確に一致しない場合は、問合せコンパイル時に不一致エラーが処理されます。
また、json_valueを使用して、JSONデータで使用する関数ベースのBツリー索引を作成することもできます。JSONデータの索引を参照してください。
ファンクションjson_valueは、2つの必須引数を持ち、オプションのRETURNING句およびエラー句を受け入れます。
json_valueの最初の引数は、スカラーのSQLデータ型のインスタンスを戻すSQL式です(つまり、オブジェクト・データ型でもコレクション・データ型でもありません)。スカラー戻り値のデータ型は、JSON、VARCHAR2、BLOBまたはCLOBです。
最初の引数は、適切にキャストされた表またはビューの列の値、PL/SQL変数、バインド変数のいずれかになります。SQL式の評価の結果は、パス式を評価するためのコンテキスト項目として使用されます。
json_valueの2番目の引数はSQL/JSONパス式であり、オプションの句RETURNING、ON ERROR、ON EMPTYおよびON MISMATCHが後に続きます。このパス式では単一のスカラー値を対象とする必要があり、そうでない場合、エラーが発生します。
デフォルトのエラー処理動作はNULL ON ERRORであり、これは、エラーが発生しても値が戻されない、つまり、エラーが発生しないことを意味します。特に、パス式が配列などの非スカラー値を対象としている場合、デフォルトではエラーは発生しません。エラーが発生するようにするには、ERROR ON ERRORを使用します。
パス式配列ステップで、指定されている位置が1つのみである場合、データと照合されます。それ以外の場合は一致しません(デフォルトではNULLが返されます)。
注意:
特定の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_VALUEを使用したユーザー定義のオブジェクト型インスタンスのインスタンス化
SQL/JSONファンクションjson_valueを使用して、ユーザー定義のSQLオブジェクト型またはコレクション型のインスタンスをインスタンス化できます。これを行うには、パス式にJSONオブジェクトまたは配列を指定し、RETURNING句でオブジェクト型またはコレクション型をそれぞれ指定します。 - JSON_TABLEとしてのJSON_VALUE
SQL/JSONファンクションjson_valueは、ファンクションjson_tableの特別な事例であるとみなすことができます。
親トピック: JSONデータの問合せ
17.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戻り型です。例17-1に、これを示します。
Oracle SQLにはブール・データ型がないため、文字列(VARCHAR2)値を使用して、JSONのブール値を戻します。例17-2に、これを示します。:問合せは文字列'true'を戻します。
SQL/JSONファンクションjson_tableは、json_valueなどの他のSQL/JSON問合せファンクションを一般化します。これを使用してJSONのブール値を投影する場合、json_valueが暗黙的に使用され、生成されるSQL値はVARCHAR2値として戻されます。そのため、投影された列のデータ型はVARCHAR2である必要があります。
例17-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;
/ 例17-2 JSON_VALUE: VARCHAR2としてSQLに戻されるJSONのブール値
SELECT json_value(po_document, '$.AllowPartialShipment')
FROM j_purchaseorder;17.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
17.3 JSON_VALUEを使用したユーザー定義オブジェクト型インスタンスのインスタンス化
SQL/JSONファンクションjson_valueを使用して、ユーザー定義のSQLオブジェクト型またはコレクション型のインスタンスをインスタンス化できます。これを行うには、パス式にJSONオブジェクトまたは配列を指定し、RETURNING句でオブジェクト型またはコレクション型をそれぞれ指定します。
対象のJSON配列の要素は、返されたコレクション型インスタンスの要素を提供します。JSON配列要素は、コレクション型要素に1対1で対応している必要があります。対応しない場合は、不一致エラーが発生します。オブジェクトであるJSON配列要素(次を参照)または配列は再帰的に処理されます。
対象のJSONオブジェクトのフィールドは、返されたオブジェクト型インスタンスの属性値を提供します。JSONフィールドは、オブジェクト型属性に1対1で対応している必要があります。対応しない場合は、不一致エラーが発生します。
対象のJSONオブジェクトのフィールド名は、オブジェクト属性のSQL名と比較されます。配列またはオブジェクトであるフィールド値は再帰的に処理されるため、最終的に、これはスカラーのSQLオブジェクト属性の名前と比較されるスカラー値を持つJSONフィールドの名前です。名前が一致しない場合(デフォルトでは大/小文字を区別しない)、不一致エラーが発生します。
すべての名前が一致する場合、対応するデータ型の互換性がチェックされます。型の非互換性がある場合は、不一致エラーが発生します。表17-1には、互換性のあるスカラー・データ型が指定されています。その他の型の組合せには互換性がないため、不一致エラーが発生します。
表17-1 互換性のあるスカラー・データ型: JSONからSQLへの変換
| JSON言語型(ソース) | SQL型(宛先) | ノート |
|---|---|---|
binary |
RAW |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
binary |
BLOB |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
binary |
CLOB |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
boolean |
VARCHAR2 |
インスタンス値は、SQL文字列"true"または"false"です。
|
boolean |
CLOB |
インスタンス値は、SQL文字列"true"または"false"です。
|
date |
DATE (場合によってはゼロの)時間コンポーネントを含みます脚注1
|
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
date |
TIMESTAMP |
時間コンポーネントにはゼロが埋め込まれます。SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
daysecondInterval |
INTERVALDAYTOSECOND |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
double |
BINARY_DOUBLE |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
double |
BINARY_FLOAT |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
double |
NUMBER |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
double |
VARCHAR2 |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
double |
CLOB |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
float |
BINARY_FLOAT |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
float |
BINARY_DOUBLE |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
float |
NUMBER |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
float |
VARCHAR2 |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
float |
CLOB |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
null |
任意のSQLデータ型。 | インスタンス値はSQL NULLです。
|
number |
NUMBER |
なし。 |
number |
BINARY_DOUBLE |
なし。 |
number |
BINARY_FLOAT |
なし。 |
number |
VARCHAR2 |
なし。 |
number |
CLOB |
なし。 |
string |
VARCHAR2 |
なし。 |
string |
CLOB |
なし。 |
string |
NUMBER |
JSON文字列は数値である必要があります。 |
string |
BINARY_DOUBLE |
JSON文字列は数値である必要があります。 |
string |
BINARY_FLOAT |
JSON文字列は数値である必要があります。 |
string |
DATE (場合によってはゼロの)時間コンポーネントを含みます脚注1 |
JSON文字列は、サポートされているISO8601形式である必要があります。 |
string |
TIMESTAMP |
JSON文字列は、サポートされているISO8601形式である必要があります。 |
string |
INTERVALYEARTOMONTH |
JSON文字列は、サポートされているISO 8601継続時間形式である必要があります。 |
string |
INTERVALDAYTOSECOND |
JSON文字列は、サポートされているISO 8601継続時間形式である必要があります。 |
timestamp |
TIMESTAMP
|
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
timestamp |
DATE (場合によってはゼロの)時間コンポーネントを含みます脚注1 |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
yearmonthInterval |
INTERVALYEARTOMONTH |
SQL型JSONとして格納されているJSONデータに対してのみサポートされます。
|
脚注1 たとえば、時間コンポーネントがゼロのDATEインスタンスが、時間コンポーネントの保持が指定されていないjson_value RETURNING DATE句によって返されます。
次のいずれかに該当する場合、問合せコンパイル時に不一致エラーが発生します。デフォルトでは、不一致エラーは無視されますが、json_valueの起動に1つ以上のON MISMATCH句を含めることで、このエラー処理を変更できます。
-
対象のJSONオブジェクトのフィールドまたは対象のJSON配列の要素は、指定されたオブジェクト型インスタンスの属性または指定されたコレクション型インスタンスの要素に、数と種類が対応していません。
-
対象のJSONオブジェクトのフィールドが、指定されたオブジェクト型インスタンスの属性と同じ名前ではありません。デフォルトでは、この照合では大文字/小文字が区別されません。
- JSON値のJSONおよびOracle SQLスカラー・データ型と対応するオブジェクト属性値またはコレクション要素値には、表17-1によると、互換性がありません。
例17-3 JSON_VALUEを使用したJSONデータからのユーザー定義のオブジェクト・インスタンスのインスタンス化
この例では、SQLオブジェクト型shipping_tおよびaddr_tを定義します。オブジェクト型shipping_tには、それぞれVARCHAR2(30)型およびaddr_t型を持つ属性nameおよびaddressがあります。
オブジェクト型addr_tには、属性streetおよびcityがあります。
この例では、json_valueを使用して、フィールドShippingInstructionsの値であるJSONオブジェクトを選択し、SQLオブジェクト型shipping_tのインスタンスを返します。オブジェクト型属性の名前は、JSONオブジェクト・フィールド名と大/小文字を区別せずに照合されるため、たとえば、SQLオブジェクト型shipping_tの属性address (ADDRESSと同じ)はJSONフィールドaddressと一致します。
(ここでは、わかりやすいように、問合せの出力をフォーマット出力しています。)
CREATE TYPE shipping_t AS OBJECT
(name VARCHAR2(30),
address addr_t);
CREATE TYPE addr_t AS OBJECT
(street VARCHAR2(100),
city VARCHAR2(30));
-- Query data to return shipping_t instances:
SELECT json_value(po_document, '$.ShippingInstructions'
RETURNING shipping_t)
FROM j_purchaseorder;
JSON_VALUE(PO_DOCUMENT,'$.SHIPPINGINSTRUCTIONS'RETURNING
--------------------------------------------------------
SHIPPING_T('Alexis Bull',
ADDR_T('200 Sporting Green',
'South San Francisco'))
SHIPPING_T('Sarah Bell',
ADDR_T('200 Sporting Green',
'South San Francisco'))
例17-4 JSON_VALUEを使用したJSONデータからのコレクション型インスタンスのインスタンス化
この例では、SQLコレクション型items_tおよびSQLオブジェクト型part_tおよびitem_tを定義します。コレクション型items_tのインスタンスは、item_tインスタンスのVARRAYです。オブジェクト型item_tの属性partは、それ自体のSQLオブジェクト型part_tです。
次に、json_valueを使用してJSONを選択します
(ここでは、わかりやすいように、問合せの出力をフォーマット出力しています。)
CREATE TYPE part_t AS OBJECT
(description VARCHAR2(30),
unitprice NUMBER);
CREATE TYPE item_t AS OBJECT
(itemnumber NUMBER,
part part_t);
CREATE TYPE items_t AS VARRAY(10) OF item_t;
-- Query data to return items_t collections of item_t objects
SELECT json_value(po_document, '$.LineItems' RETURNING items_t)
FROM j_purchaseorder;
JSON_VALUE(PO_DOCUMENT,'$.LINEITEMS'RETURNINGITEMS_TUSIN
--------------------------------------------------------
ITEMS_T(ITEM_T(1, PART_T('One Magic Christmas', 19.95)),
ITEM_T(2, PART_T('Lethal Weapon', 19.95)))
ITEMS_T(ITEM_T(1, PART_T('Making the Grade', 20)),
ITEM_T(2, PART_T('Nixon', 19.95)),
ITEM_T(3, PART_T(NULL, 19.95)))関連項目:
json_valueの詳細は、Oracle Database SQL言語リファレンスを参照してください。
親トピック: SQL/JSONファンクションJSON_VALUE
17.4 JSON_TABLEとしてのJSON_VALUE
SQL/JSONファンクションjson_valueは、関数json_tableの特別な事例であるとみなすことができます。
例17-5に、この対応を示します。2つのSELECT文で得られる結果は同じになります。
この対応は、おそらくjson_valueについてより深く理解する手助けとなるのみでなく、どちらの関数を使用しても同じ結果が得られることを意味しているため、実質的に重要な意味を持ちます。
特に、json_valueを複数回使用する場合、またはこれをjson_existsまたはjson_query (これらもjson_tableを使用して表すことができます)と組み合せて使用して同じデータにアクセスする場合、json_tableを1回呼び出す方が、データが解析されるのが1回のみであるという利点があります。
このため、オプティマイザがjson_exists、json_valueおよびjson_queryの複数の呼出し(任意の組合せ)を、より少ないjson_tableの呼出しに自動的にリライトすることがよく起こります。
例17-5 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";