JSON_VALUE
構文
JSON_basic_path_expression::=
(JSON_basic_path_expression
: SQL/JSONパス式を参照)
JSON_value_returning_clause::=
JSON_value_return_type::=
JSON_value_on_error_clause::=
JSON_value_on_empty_clause::=
目的
SQL/JSONファンクションJSON_VALUE
は、JSONデータの指定されたスカラーJSON値を確認し、SQL値として戻します。
関連項目:
このファンクションによって戻される値が文字値である場合に、それに割り当てる照合を定義する照合導出ルールは、『Oracle Databaseグローバリゼーション・サポート・ガイド』の付録Cを参照してください。
expr
この句は、評価の対象となるJSONデータを指定するために使用します。expr
では、テキスト・リテラルを評価する式を指定します。expr
が列である場合、列のデータ型はVARCHAR2
、CLOB
またはBLOB
のいずれかである必要があります。expr
がNULLの場合はNULLを戻します。
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のオブジェクト型のインスタンスを返します。
関連項目:
-
概念の理解には、SQL/JSON Function JSON_VALUE。
-
前述のデータ型の詳細は、「データ型」を参照してください。
-
データ型が戻り値を保持できる十分な大きさでない場合、このファンクションはデフォルトで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
が有効なオプションです。
例
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
句を指定することにより、個々の場合に応じて使用できます。
例
IGNORE ON MISMATCH (EXTRA DATA)
ERROR ON MISMATCH ( MISSING DATA, TYPE ERROR)
オプションIGNORE
は、戻り型がオブジェクト型の場合のみ有効です。
例
次の問合せは、プロパティ名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