JSON_VALUE

注意:

JSON_VALUEファンクションは、Oracle Database 12cリリース1(12.1.0.2)から使用可能です。

構文

図json_value.gifの説明

JSON_path_expression::=

図json_path_expression.gifの説明

object_step::=

図object_step.gifの説明

array_step::=

図array_step.gifの説明

JSON_value_returning_clause::=

図json_value_returning_clause.gifの説明

JSON_value_return_type::=

図json_value_return_type.gifの説明

JSON_value_on_error_clause::=

図json_value_on_error_clause.gifの説明

用途

JSON_VALUEは、JSONデータの指定されたスカラーJSON値を確認し、SQL値として戻します。

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_path_expression

この句を使用して、JSONパス式を指定します。このファンクションはパス式を使用してexprを評価し、パス式と一致する(パス式を満たす)スカラーJSON値を確認します。パス式はテキスト・リテラルである必要があります。

パス式は、exprで指定される式であるコンテキスト項目を表すドル記号($)から始める必要があります。ドル記号の後、オブジェクト・ステップや配列ステップの可能性があるゼロ以上のステップが続きます。

このファンクションは、コンテキスト項目に対するパス式の最初のステップと一致するかどうかを試行します。最初のステップが一致した場合、このファンクションは最初のステップと一致したJSON値に対して2番目のステップと一致するかどうかを試行します。2番目のステップが一致した場合、このファンクションは2番目のステップと一致したJSON値に対して3番目のステップと一致するかどうかを試行し、以降も同様に続きます。最後のステップがスカラーJSON値と一致する場合、このファンクションはSQL値としてその値を戻します。ドル記号とゼロのステップで構成されるパス式('$')は、全体のコンテキスト項目と一致します。

JSON_value_returning_clauseを指定して、戻りSQL値のデータ型および書式を制御できます。JSON_value_returning_clauseを参照してください。

パス式の任意のステップが一致しない場合または最後のステップがスカラー値以外と一致する場合、このファンクションはデフォルトでnullを戻します。JSON_value_on_error_clauseを使用して、このデフォルトの動作をオーバーライドできます。JSON_value_on_error_clauseを参照してください。

object_step この句を使用して、オブジェクト・ステップを指定します。

simple_nameまたはcomplex_nameを使用して、プロパティ名を指定します。このプロパティ名のメンバーが評価されるJSONオブジェクトにある場合、そのメンバーのプロパティ値が一致するオブジェクト・ステップになります。それ以外の場合、一致しないオブジェクト・ステップになります。両方のタイプの名前は大/小文字を区別します。このため、アルファベットの大/小文字がオブジェクト・ステップおよびJSONデータで一致する場合のみ、一致の結果になります。

simple_nameは、英数字のみで構成され、最初の文字はアルファベット文字にする必要があります。complex_nameは、英数字のみで構成され、最初の文字は英数字にする必要があります。complex_nameは、二重引用符で囲む必要があります。
ワイルド・カード記号であるアスタリスク(*)を使用して、すべてのプロパティ名を指定します。評価されるJSONオブジェクトに少なくとも1つのメンバーが含まれる場合、オブジェクト・ステップはすべてのメンバーの値と一致する結果になります。それ以外の場合、一致しないオブジェクト・ステップになります。

オブジェクト・ステップをJSON配列に適用すると、配列が暗黙的にアンラップされ、配列の要素がオブジェクト・ステップを使用して評価されます。これは、JSONパス式解除といいます。詳細は、『Oracle XML DB開発者ガイド』を参照してください。

評価されるJSON値がJSONオブジェクトでない場合、オブジェクト・ステップは一致しません。

array_step この句を使用して、配列ステップを指定します。

integerを使用して、JSON配列の索引integerの要素を指定します。integer TO integerを使用して、2つのinteger索引値の間(これらの値を含む)の要素の範囲を指定します。指定された要素が評価されるJSON配列にある場合、配列ステップはそれらの要素と一致する結果になります。それ以外の場合、一致しない配列ステップになります。JSON配列の最初の要素は索引0です。
ワイルド・カード記号であるアスタリスク(*)を使用して、JSON配列のすべての要素を指定します。評価されるJSON配列に少なくとも1つの要素が含まれる場合、JSON配列のすべての要素が一致する配列ステップになります。それ以外の場合、一致しない配列ステップになります。

評価されるJSONデータがJSON配列でない場合、データが暗黙的に配列にラップされ、配列ステップを使用して評価されます。これは、JSONパス式解除といいます。詳細は、『Oracle XML DB開発者ガイド』を参照してください。

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)型の値を戻します。

詳細は、「VARCHAR2データ型」を参照してください。
NUMBER[(precision [, scale])]

このデータ型を指定すると、このファンクションで戻されるスカラー値は数値になります。

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

データ型が戻り値を保持できる十分な大きさでない場合、このファンクションはデフォルトで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データがJSONパス式を使用して評価される場合にスカラー値または一致が見つかりません。
戻り値のデータ型が戻り値を保持する十分な大きさではありません。

次の句を指定できます。

NULL ON ERROR - エラーが発生した場合にnullを戻します。これはデフォルトです。
ERROR ON ERROR - エラーが発生した場合に適切なOracleエラーを戻します。
DEFAULT literal ON ERROR - エラーが発生した場合にliteralを戻します。このファンクションで戻される値のデータ型がVARCHAR2である場合、テキスト・リテラルを指定する必要があります。データ型がNUMBERである場合、数値リテラルを指定する必要があります。

例

次の問合せは、プロパティ名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