1. SQL言語リファレンス
  2. ファンクション
  3. JSON_VALUE

JSON_VALUE

構文

json_value.epsの説明が続きます
図json_value.epsの説明

(JSON_basic_path_expression: Oracle Database JSON開発者ガイドを参照)

JSON_value_returning_clause::=

json_value_returning_clause.epsの説明が続きます
図json_value_returning_clause.epsの説明

JSON_value_return_type::=

json_value_return_type.epsの説明が続きます
図json_value_return_type.epsの説明

JSON_value_on_error_clause::=

json_value_on_error_clause.epsの説明が続きます
図json_value_on_error_clause.epsの説明

JSON_value_on_empty_clause::=

json_value_on_empty_clause.epsの説明が続きます
図json_value_on_empty_clause.epsの説明

目的

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

  • NUMBER[(precision [, scale])]

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

  • DATE

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

  • TIMESTAMP

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

  • TIME WITH TIME ZONE

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

  • SDO_GEOMETRY

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

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

データ型が戻り値を保持できる十分な大きさでない場合、このファンクションはデフォルトで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