JSON_VALUE

構文

図json_value.epsの説明

JSON_basic_path_expression::=

(JSON_basic_path_expression: SQL/JSONパス式を参照)

JSON_value_returning_clause::=

図json_value_returning_clause.epsの説明

JSON_value_return_type::=

図json_value_return_type.epsの説明

JSON_value_return_object_instance ::=

図json_value_return_object_instance.epsの説明

JSON_value_mapper_clause ::=

図json_value_mapper_clause.epsの説明

JSON_value_on_error_clause::=

図json_value_on_error_clause.epsの説明

JSON_value_on_empty_clause::=

図json_value_on_empty_clause.epsの説明

JSON_value_on_mismatch_clause::=

図json_value_on_mismatch_clause.epsの説明

目的

SQL/JSONファンクションJSON_VALUEは、JSONデータを選択し、SQLスカラーやユーザー定義のSQLオブジェクト型またはSQLコレクション型(VARRAY、ネストした表)のインスタンスを返します。

関連項目:

• 『JSON開発者ガイド』のJSON TRANSFORM。

• このファンクションによって戻される値が文字値である場合に、それに割り当てる照合を定義する照合導出ルールは、『Oracle Databaseグローバリゼーション・サポート・ガイド』の付録Cを参照してください。

expr

JSON_VALUE exprの最初の引数は、スカラーのSQLデータ型のインスタンスを戻すSQL式です(つまり、オブジェクト・データ型でもコレクション・データ型でもありません)。JSON_VALUEから返されるスカラー値は、データ型BINARY_DOUBLE、BINARY_FLOAT、BOOLEAN、CHAR、CLOB、DATE、INTERVAL DAY TO SECOND、INTERVAL YEAR TO MONTH、NCHAR、NCLOB、NVARCHAR2、NUMBER、RAW1、SDO_GEOMETRY、TIMESTAMP、TIMESTAMP WITH TIME ZONE、VARCHAR2、VECTORのいずれかにできます。

exprが厳密なまたは緩い構文を使用した整形式のJSONデータのテキスト・リテラルでない場合、このファンクションはデフォルトでnullを戻します。JSON_value_on_error_clauseを使用して、このデフォルトの動作をオーバーライドできます。JSON_value_on_error_clauseを参照してください。

FORMAT JSON

exprがデータ・タイプBLOBの列の場合、FORMAT JSONを指定する必要があります。

JSON_basic_path_expression

この句を使用して、SQL/JSONパス式を指定します。このファンクションはパス式を使用してexprを評価し、パス式と一致する(パス式を満たす)スカラーJSON値を確認します。パス式はテキスト・リテラルである必要があります。JSON_basic_path_expressionのセマンティクスの詳細は、Oracle Database JSON開発者ガイドを参照してください。

JSON_value_returning_clause

この句を使用して、このファンクションで戻される値のデータ型および書式を指定します。

RETURNING

RETURNING句を使用して、戻り値のデータ型を指定します。この句を省略すると、JSON_VALUEはVARCHAR2(4000)型の値を戻します。

JSON_value_return_type ::=

JSON_value_return_typeを使用して、次のデータ型を指定できます。

• VARCHAR2[(size [BYTE,CHAR])]

  このデータ型を指定すると、このファンクションで戻されるスカラー値は文字または数値になります。数値は暗黙的にVARCHAR2に変換されます。SQLでVARCHAR2データ型を指定する場合、サイズを指定する必要があります。ただし、この句はサイズを省略できます。この場合、JSON_VALUEはVARCHAR2(4000)型の値を戻します。

  戻り値がN文字よりも大きい場合は、VARCHAR2(N)の直後にオプションのTRUNCATE句を指定して、戻り値をN文字になるように切り捨てます。

  TRUNCATE句のノート:

  • 文字列値が長すぎる場合は、ORA-40478が発生します。
  • TRUNCATEが指定されており、戻り値がキャラクタ・タイプでない場合は、コンパイル時エラーが発生します。
  • TRUNCATEがFORMAT JSONとともに指定されている場合、構文的に正しくないJSONデータが戻り値に含まれる可能性があります。
  • TRUNCATEはEXISTSと一緒には機能しません。

• CLOB

  このデータ型を指定すると、シングルバイトまたはマルチバイト文字を含むキャラクタ・ラージ・オブジェクトが返されます。

• NUMBER[(precision [, scale])]

  このデータ型を指定すると、このファンクションで戻されるスカラー値は数値になります。戻されるスカラー値をJSONブール値にすることもできます。ただし、JSONブール値としてNUMBERを戻す操作は非推奨です。

• DATE

  このデータ型を指定すると、このファンクションで戻されるスカラー値は、暗黙的にDATEデータ型に変換できる文字値になります。JSON入力が時間コンポーネントを含む日付を表している場合は、DATE PRESERVE TIMEを指定して時間コンポーネントを保持します。時間コンポーネントを保持しない場合は、DATE TRUNCATE TIMEを指定します。

  PRESERVE TIMEもTRUNCATE TIMEも指定しない場合、時間コンポーネントは保持されません。

• TIMESTAMP

  このデータ型を指定すると、このファンクションで戻されるスカラー値は、暗黙的にTIMESTAMPデータ型に変換できる文字値になります。

• TIME WITH TIME ZONE

  このデータ型を指定すると、このファンクションで戻されるスカラー値は、暗黙的にTIMESTAMP WITH TIME ZONEデータ型に変換できる文字値になります。

• BOOLEAN

  BOOLEANを指定して、true、falseまたはunknownを戻します。

• SDO_GEOMETRY

  このデータ型は、Oracle Spatial and Graphデータで使用されます。このデータ型を指定すると、exprが、JSONの地理データをエンコードする書式であるGeoJSONデータを含むテキスト・リテラルに評価されます。このデータ型を指定すると、このファンクションで戻されるスカラー値は、SDO_GEOMETRY型のオブジェクトになります。

• JSON_value_return_object_instance

  JSON_VALUEがJSONオブジェクトをターゲットにしており、ユーザー定義のSQLオブジェクト型を戻り型として指定すると、JSON_VALUEはobject_type_nameのオブジェクト型のインスタンスを返します。

  例については、『JSON開発者ガイド』のJSON_VALUEを使用したユーザー定義オブジェクト型インスタンスのインスタンス化を参照してください。

関連項目:

• SQL/JSON Function JSON_VALUE: JSON Developer's Guideの概念の理解。

• 前述のデータ型の詳細は、「データ型」を参照してください。

• データ型が戻り値を保持できる十分な大きさでない場合、このファンクションはデフォルトでnullを戻します。JSON_value_on_error_clauseを使用して、このデフォルトの動作をオーバーライドできます。JSON_value_on_error_clauseを参照してください。

ASCII

標準のASCII Unicodeエスケープ・シーケンスを使用して戻り値の非ASCII Unicode文字を自動的にエスケープするには、ASCIIを指定します。

JSON_value_on_error_clause

この句を使用して、次のエラーが発生した場合にこのファンクションで戻される値を指定します。

• exprが厳密なまたは緩いJSON構文を使用した整形式のJSONデータではありません。

• JSONデータがSQL/JSONパス式を使用して評価される場合に非スカラー値が見つかります。

• SQL/JSONパス式を使用してJSONデータを評価した場合に一致が見つかりません。JSON_value_on_empty_clauseを指定して、このタイプのエラーに対する動作を上書きできます。

• 戻り値のデータ型が戻り値を保持する十分な大きさではありません。

次の句を指定できます。

• NULL ON ERROR - エラーが発生した場合にnullを戻します。これはデフォルトです。

• ERROR ON ERROR - エラーが発生した場合に適切なOracleエラーを戻します。

• DEFAULT literal ON ERROR - エラーが発生した場合にliteralを戻します。literalのデータ型は、このファンクションにより戻される値のデータ型と一致する必要があります。

JSON_value_on_empty_clause

この句を使用して、JSONデータがSQL/JSONパス式を使用して評価されるときに一致が見つからない場合にこのファンクションで戻される値を指定します。この句により、JSON_value_on_error_clauseで指定された結果とは異なる、このタイプのエラーに対する結果を指定できます。

次の句を指定できます。

• NULL ON EMPTY - 一致が見つからない場合にNULLを戻します。

• ERROR ON EMPTY - 一致が見つからない場合に適切なOracleエラーを戻します。

• DEFAULT literal ON EMPTY - 一致が見つからない場合にliteralを戻します。literalのデータ型は、このファンクションにより戻される値のデータ型と一致する必要があります。

この句を省略すると、JSON_value_on_error_clauseによって、一致が見つからない場合に戻される値が決まります。

JSON_value_on_mismatch_clause

JSON_value_on_mismatch_clauseは、JSON番号をSQL日付に変換しようとした場合など、型変換が失敗した場合に適用されます。

JSON_VALUEの戻り型がNUMBERやDATEなどのSQLスカラーの場合、ON MISMATCHはすべての型変換エラーに適用されます。これ以上の指定は必要ありません。ERRORおよびNULLが有効なオプションです。

例1

select json_value( '{a:"cat"}','$.a.number()'  NULL ON EMPTY                                                 
 ERROR ON  MISMATCH  DEFAULT -1 ON ERROR ) from dual;
 ORA-01722: invalid number

戻り型がオブジェクト型の場合、ON MISMATCHはさらにMISSING DATA、EXTRA DATAおよびTYPE ERRORで指定できます。通常、すべてのエラー・ケースに適用するために使用できます。または、ケースごとに異なるON MISMATCH句を指定することにより、個々の場合に応じて使用できます。

例2

IGNORE ON MISMATCH (EXTRA DATA)

ERROR ON MISMATCH ( MISSING DATA, TYPE ERROR)

オプションIGNOREは、戻り型がオブジェクト型の場合のみ有効です。

TYPE句

STRICTおよびLAX構文の詳細は、「厳密なJSON構文と緩慢なJSON構文について」および「SQLファンクションおよび条件のTYPE句」を参照してください

例

次の問合せは、プロパティ名aのメンバーの値を戻します。RETURNING句が指定されていないため、値がVARCHAR2(4000)データ型として戻されます。

SELECT JSON_VALUE('{a:100}', '$.a') AS value
  FROM DUAL;

VALUE
-----
100

次の問合せは、プロパティ名aのメンバーの値を戻します。RETURNING NUMBER句が指定されているため、値がNUMBERデータ型として戻されます。

SELECT JSON_VALUE('{a:100}', '$.a' RETURNING NUMBER) AS value
  FROM DUAL;

     VALUE
----------
       100

次の問合せは、プロパティ名aを使用したメンバーの値にあるプロパティ名bを使用したメンバーの値を戻します。

SELECT JSON_VALUE('{a:{b:100}}', '$.a.b') AS value
  FROM DUAL;

VALUE
-----
100

次の問合せは、任意のオブジェクトのプロパティ名dのメンバーの値を戻します。

SELECT JSON_VALUE('{a:{b:100}, c:{d:200}, e:{f:300}}', '$.*.d') AS value
  FROM DUAL;

VALUE
-----
200

次の問合せは、配列の最初の要素の値を戻します。

SELECT JSON_VALUE('[0, 1, 2, 3]', '$[0]') AS value
  FROM DUAL;

VALUE
-----
0

次の問合せは、配列の3番目の要素の値を戻します。配列は、プロパティ名aのメンバーの値です。

SELECT JSON_VALUE('{a:[5, 10, 15, 20]}', '$.a[2]') AS value
  FROM DUAL;

VALUE
-----
15

次の問合せは、配列の2番目のオブジェクトのプロパティ名aのメンバーの値を戻します。

SELECT JSON_VALUE('[{a:100}, {a:200}, {a:300}]', '$[1].a') AS value
  FROM DUAL;

VALUE
-----
200

次の問合せは、配列の任意のオブジェクトのプロパティ名cのメンバーの値を戻します。

SELECT JSON_VALUE('[{a:100}, {b:200}, {c:300}]', '$[*].c') AS value
  FROM DUAL;

VALUE
-----
300

次の問合せは、プロパティ名lastnameのメンバーの値を戻そうとします。ただし、そのメンバーは指定されたJSONデータに存在しないため、一致は見つかりません。ON ERROR句が指定されていないため、この文はデフォルトのNULL ON ERRORを使用し、nullを戻します。

SELECT JSON_VALUE('{firstname:"John"}', '$.lastname') AS "Last Name"
  FROM DUAL;

Last Name
---------

指定されたJSONに存在しないプロパティ名lastnameのメンバーの値を戻そうとするため、次の問合せはエラーになります。ON ERROR句が指定されているため、この文は指定されたテキスト・リテラルを戻します。

SELECT JSON_VALUE('{firstname:"John"}', '$.lastname'
                  DEFAULT 'No last name found' ON ERROR) AS "Last Name"
  FROM DUAL;

Last Name
---------
No last name found