JSON_VALUE
構文
(JSON_basic_path_expression
: Oracle Database 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
を使用して、次のデータ型を指定できます。
-
VARCHAR2[(
size
[BYTE,CHAR])]
このデータ型を指定すると、このファンクションで戻されるスカラー値は文字または数値になります。数値は暗黙的に
VARCHAR2
に変換されます。SQLでVARCHAR2
データ型を指定する場合、サイズを指定する必要があります。ただし、この句はサイズを省略できます。この場合、JSON_VALUE
はVARCHAR2(4000)
型の値を戻します。戻り値が
N
文字よりも大きい場合は、VARCHAR2(N)
の直後にオプションのTRUNCATE
句を指定して、戻り値をN
文字になるように切り捨てます。 -
NUMBER[(
precision
[,
scale
])]
このデータ型を指定すると、このファンクションで戻されるスカラー値は数値になります。戻されるスカラー値をJSONブール値にすることもできます。ただし、JSONブール値として
NUMBER
を戻す操作は非推奨です。 -
DATE
このデータ型を指定すると、このファンクションで戻されるスカラー値は、暗黙的に
DATE
データ型に変換できる文字値になります。 -
TIMESTAMP
このデータ型を指定すると、このファンクションで戻されるスカラー値は、暗黙的に
TIMESTAMP
データ型に変換できる文字値になります。 -
TIME
WITH
TIME
ZONE
このデータ型を指定すると、このファンクションで戻されるスカラー値は、暗黙的に
TIMESTAMP
WITH
TIME
ZONE
データ型に変換できる文字値になります。 -
SDO_GEOMETRY
このデータ型は、Oracle Spatial and Graphデータで使用されます。このデータ型を指定すると、
expr
が、JSONの地理データをエンコードする書式であるGeoJSONデータを含むテキスト・リテラルに評価されます。このデータ型を指定すると、このファンクションで戻されるスカラー値は、SDO_GEOMETRY
型のオブジェクトになります。 -
CLOB
このデータ型を指定すると、シングルバイトまたはマルチバイト文字を含むキャラクタ・ラージ・オブジェクトが返されます。
関連項目:
-
詳細は、Oracle Database JSON開発者ガイド を参照してください
-
前述のデータ型の詳細は、「データ型」を参照してください。
-
データ型が戻り値を保持できる十分な大きさでない場合、このファンクションはデフォルトで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
によって、一致が見つからない場合に戻される値が決まります。
例
次の問合せは、プロパティ名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