1. JSON開発者ガイド
  2. JSONデータの問合せ
  3. SQL/JSONファンクションJSON_VALUE

19 SQL/JSONファンクションJSON_VALUE

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

  • json_valueが単一のスカラーのJSON値を対象にする場合、スカラーのSQL値を返します。返されるスカラー値のSQLデータ型を指定できます。デフォルトは、VARCHAR2(4000)です。

    json_valueの対象がJSONスカラー値nullである場合は、SQLの戻り型がどれであってもSQLのNULLが返されます。(たとえば、デフォルトの戻り型VARCHAR2(4000)の場合に、SQL文字列'null'は返されません。)つまり、特に、json_valueを使用してJSON値nullと値が存在しないことの区別はできません。この場合、SQLのNULLは両方の事例を表します。

  • json_valueがJSON配列を対象にしている場合、SQLコレクション型(VARRAYまたはネストした表)を戻り型として指定すると、json_valueはそのコレクション型のインスタンスを返します。

    対象のJSON配列の要素は、返されたコレクション型インスタンスの要素を提供します。スカラーのJSON配列要素は、返されたコレクション・インスタンスにスカラーのSQL値を生成します(前を参照)。オブジェクトであるJSON配列要素(次を参照)または配列は再帰的に処理されます。

  • json_valueがJSONオブジェクトを対象にしている場合、ユーザー定義のSQLオブジェクト型を戻り型として指定すると、json_valueはそのオブジェクト型のインスタンスを返します。

    対象のJSONオブジェクトのフィールド値は、返されたオブジェクト型インスタンスの属性値を提供します。対象のJSONオブジェクトのフィールド名は、SQLオブジェクト属性のSQL名と比較されます。スカラー・フィールド値は、返されたオブジェクト型インスタンスにスカラーのSQL値を生成します(前を参照)。配列であるフィールド値(前を参照)またはオブジェクトが再帰的に処理されます。

    最終的に、これはスカラーのSQLオブジェクト属性の名前と比較されるスカラー値を持つJSONフィールドの名前です。大/小文字を区別して名前が正確に一致しない場合は、問合せコンパイル時に不一致エラーが処理されます。

また、json_valueを使用して、JSONデータで使用する関数ベースのBツリー索引を作成することもできます。JSONデータの索引を参照してください。

ファンクションjson_valueには、必須の引数が2つあり、いくつかのオプションの句を受け入れます。

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

ノート:

一般に、JSON文字列からNVARCHAR2、NCLOBおよびNCHAR以外の型のSQL文字データを生成し、その変換先データ型の文字セットがUnicodeベースでない場合、その変換において、そのSQL型の文字セットで表せない文字については文字セット変換で情報の損失が発生する可能性があります。

最初の引数は、適切にキャストされた表またはビューの列の値、PL/SQL変数、バインド変数のいずれかになります。SQL式の評価の結果は、パス式を評価するためのコンテキスト項目として使用されます。

json_valueの2番目の引数はSQL/JSONパス式であり、その後にはオプションの句RETURNING、PASSING、ON ERROR、ON EMPTYおよびON MISMATCHが続きます。このパス式では単一のスカラー値を対象とする必要があり、そうでない場合、エラーが発生します。

デフォルトのエラー処理動作はNULL ON ERRORであり、これは、エラーが発生しても値が戻されない、つまり、エラーが発生しないことを意味します。特に、パス式が配列などの非スカラー値を対象としている場合、デフォルトではエラーは発生しません。エラーが発生するようにするには、ERROR ON ERRORを使用します。

パス式配列ステップで、指定されている位置が1つのみである場合、データと照合されます。それ以外の場合は一致しません(デフォルトではNULLが返されます)。

ノート:

特定のJSONオブジェクト内のフィールド名は、それぞれが一意である必要はありません。同じフィールド名を繰り返すことができます。Oracle Databaseで行われるストリーム評価では、特定のフィールド名を持つ1つのオブジェクト・メンバーのみが常に使用され、同じフィールド名を持つ他のメンバーは無視されます。このような複数のメンバーのうちどれが使用されるかは指定されていません。

JSONオブジェクトの一意フィールドと重複フィールドを参照してください。

関連項目:

json_valueの詳細は、Oracle Database SQL言語リファレンスを参照してください。

関連項目

親トピック: JSONデータの問合せ

19.1 SQL/JSONファンクションJSON_VALUEとブール型のJSON値の使用

JSONには、ブール型の値trueおよびfalseがあります。SQL/JSONファンクションjson_valueによってパス式がJSONのtrueまたはfalseに評価されると、BOOLEAN値またはVARCHAR2値('true'または'false')か、NUMBER値(trueの場合は1、falseの場合は0)を返すことができます。

デフォルトでは、json_valueは文字列VARCHAR2 (文字列)値を返します。ターゲット・データがJSONのブール値の場合、デフォルトでは、文字列の値'true'または'false'が返されます。例19-1にこれを示します。この問合せは、文字列'true'を返します。

RETURNING句を使用すると、戻りデータ型を指定できます。例19-2に、SQLのBOOLEAN値(trueまたはfalse)を返すためのRETURNING BOOLEANの使用を示します。この問合せは、trueを返します。例19-3に、PL/SQLの場合の同じ内容を示すとともに、ERROR ON ERROR句の使用を示します。

デフォルトでは、ターゲット・データがJSONのブール値の場合、RETURNING NUMBERによってエラーが発生します。ただし、句ALLOW BOOLEAN TO NUMBER CONVERSIONを含めると、エラーは発生しません。その場合、trueのJSON値に対して1が返され、false値に対して0が返されます。例19-4に、これを示します。この問合せは1を返します。

SQL/JSONファンクションjson_tableは、json_valueなどの他のSQL/JSON問合せファンクションを一般化します。これを使用してJSONのブール値を投影する場合、json_valueが暗黙的に使用され、生成されるSQL値はデフォルトでVARCHAR2値として返されます。したがって、デフォルトでは投影列のデータ型はVARCHAR2です。

ただし、json_valueの場合と同様に、JSONのブール値はBOOLEAN値として投影できます。また、列にNUMBERデータ型を指定して、ALLOW BOOLEAN TO NUMBER CONVERSION句を含めることで、NUMBER値として投影できます。

例19-1 JSON_VALUE: VARCHAR2として戻されるJSONのブール値

VARCHAR2を返すことは、json_valueファンクションのデフォルト動作です。 

SELECT json_value(po_document, '$.AllowPartialShipment')
  FROM j_purchaseorder;

例19-2 JSON_VALUE: BOOLEANとしてSQLに戻されるJSONのブール値

この例では、ブールJSONデータに応じたSQL BOOLEAN値を返します。(BOOLEANデータ型は、リリース23c以降のOracle SQLで使用できます)。 

SELECT json_value(po_document, '$.AllowPartialShipment'
                  RETURNING BOOLEAN)
  FROM j_purchaseorder;

例19-3 JSON_VALUE: BOOLEANとしてPL/SQLに返されるJSONのブール値

この例では、句ERROR ON ERRORを使用して、エラーがあった場合にエラーを発生します。(その後、ユーザーの例外処理コードがエラーを処理できます。) 

DECLARE
  b     BOOLEAN;
  jdata CLOB;
BEGIN
  SELECT po_document INTO jdata FROM j_purchaseorder
    WHERE rownum = 1;
  b := json_value(jdata, '$.AllowPartialShipment'
                  RETURNING BOOLEAN
                  ERROR ON ERROR);
END;
/ 

例19-4 JSON_VALUE: NUMBERとしてSQLに返されるJSONのブール値

この例では、句ALLOW BOOLEAN TO NUMBER CONVERSIONを使用してSQLのNUMBER値1 (true)を返します。この句を指定しないと、RETURNING NUMBERによってブール型のJSONデータのエラーが発生します。 

SELECT json_value(po_document, '$.AllowPartialShipment'
                  RETURNING NUMBER
                  ALLOW BOOLEAN TO NUMBER CONVERSION)
  FROM j_purchaseorder;

関連項目

親トピック: SQL/JSONファンクションJSON_VALUE

19.2 JSON_VALUEを使用したユーザー定義オブジェクト型またはコレクション型インスタンスのインスタンス化

SQL/JSONファンクションjson_valueを使用して、ユーザー定義のSQLオブジェクト型またはコレクション型のインスタンスをインスタンス化できます。これを行うには、パス式にJSONオブジェクトまたは配列を指定し、RETURNING句でオブジェクト型またはコレクション型をそれぞれ指定します。

対象のJSON配列の要素は、返されたコレクション型インスタンスの要素を提供します。JSON配列要素は、コレクション型要素に1対1で対応している必要があります。対応しない場合は、不一致エラーが発生します。オブジェクトであるJSON配列要素(次を参照)または配列は再帰的に処理されます。

対象のJSONオブジェクトのフィールドは、返されたオブジェクト型インスタンスの属性値を提供します。JSONフィールドは、オブジェクト型属性に1対1で対応している必要があります。対応しない場合は、不一致エラーが発生します。

対象のJSONオブジェクトのフィールド名は、オブジェクト属性のSQL名と比較されます。配列またはオブジェクトであるフィールド値は再帰的に処理されるため、最終的に、これはスカラーのSQLオブジェクト属性の名前と比較されるスカラー値を持つJSONフィールドの名前です。名前が一致しない場合(デフォルトでは大/小文字を区別しない)、不一致エラーが発生します。

すべての名前が一致する場合、対応するデータ型の互換性がチェックされます。型の非互換性がある場合は、不一致エラーが発生します。表17-2に、互換性のあるスカラー・データ型を明示します。その他の型の組合せには互換性がないため、不一致エラーが発生します。

次のいずれかに該当する場合、問合せコンパイル時に不一致エラーが発生します。デフォルトでは、不一致エラーは無視されますが、json_valueの起動に1つ以上のON MISMATCH句を含めることで、このエラー処理を変更できます。

  • 対象のJSONオブジェクトのフィールドまたは対象のJSON配列の要素は、指定されたオブジェクト型インスタンスの属性または指定されたコレクション型インスタンスの要素に、数と種類が対応していません。

  • 対象のJSONオブジェクトのフィールドが、指定されたオブジェクト型インスタンスの属性と同じ名前ではありません。デフォルトでは、この照合では大文字/小文字が区別されません。

  • JSON値のJSONおよびOracle SQLのスカラー・データ型と対応するオブジェクト属性値またはコレクション要素値には、表17-2によると互換性がありません。

json_valueを使用すると、PL/SQLおよびSQLでオブジェクト型またはコレクション型のインスタンスを返せます。ただし、レコード型または索引表型のインスタンスを返す場合、NULL ON MISMATCH句とNULL ON EMPTY句の動作はわずかに異なります。これは、これらの型の値を不可分的にNULLにできないためです。詳細は、それらのドキュメントを参照してください。

例19-5 JSON_VALUEを使用したJSONデータからのユーザー定義オブジェクト・インスタンスのインスタンス化

この例では、SQLオブジェクト型shipping_tおよびaddr_tを定義します。オブジェクト型shipping_tには、それぞれVARCHAR2(30)型およびaddr_t型を持つ属性nameおよびaddressがあります。

オブジェクト型addr_tには、属性streetおよびcityがあります。

この例では、json_valueを使用して、フィールドShippingInstructionsの値であるJSONオブジェクトを選択し、SQLオブジェクト型shipping_tのインスタンスを返します。オブジェクト型属性の名前は、JSONオブジェクト・フィールド名と大/小文字を区別せずに照合されるため、たとえば、SQLオブジェクト型shipping_tの属性address (ADDRESSと同じ)はJSONフィールドaddressと一致します。

(ここでは、わかりやすいように、問合せの出力をフォーマット出力しています。)

CREATE TYPE addr_t AS OBJECT
  (street VARCHAR2(100),
   city   VARCHAR2(30));

-- Create after type addr_t, because that's referred to here.
--
CREATE TYPE shipping_t AS OBJECT
  (name    VARCHAR2(30),
   address addr_t); 

-- Query data to return shipping_t instances:
SELECT json_value(po_document, '$.ShippingInstructions'
                  RETURNING shipping_t)
  FROM j_purchaseorder;

JSON_VALUE(PO_DOCUMENT,'$.SHIPPINGINSTRUCTIONS'RETURNING
--------------------------------------------------------
SHIPPING_T('Alexis Bull',
           ADDR_T('200 Sporting Green',
                  'South San Francisco'))
SHIPPING_T('Sarah Bell',
           ADDR_T('200 Sporting Green',
                  'South San Francisco'))

例19-6 JSON_VALUEを使用したJSONデータからのコレクション型インスタンスのインスタンス化

この例では、SQLコレクション型items_tおよびSQLオブジェクト型part_tおよびitem_tを定義します。コレクション型items_tのインスタンスは、item_tインスタンスのVARRAYです。オブジェクト型item_tの属性partは、それ自体のSQLオブジェクト型part_tです。

次に、json_valueを使用してJSONを選択します

(ここでは、わかりやすいように、問合せの出力をフォーマット出力しています。)

CREATE TYPE part_t AS OBJECT
  (description VARCHAR2(30),
   unitprice   NUMBER);
 
CREATE TYPE item_t AS OBJECT
  (itemnumber NUMBER,
   part       part_t);
  
CREATE TYPE items_t AS VARRAY(10) OF item_t;

-- Query data to return items_t collections of item_t objects
SELECT json_value(po_document, '$.LineItems' RETURNING items_t)
  FROM j_purchaseorder;

JSON_VALUE(PO_DOCUMENT,'$.LINEITEMS'RETURNINGITEMS_TUSIN
--------------------------------------------------------
ITEMS_T(ITEM_T(1, PART_T('One Magic Christmas', 19.95)),
        ITEM_T(2, PART_T('Lethal Weapon', 19.95)))
ITEMS_T(ITEM_T(1, PART_T('Making the Grade', 20)),
        ITEM_T(2, PART_T('Nixon', 19.95)),
        ITEM_T(3, PART_T(NULL, 19.95)))

関連項目

関連項目:

json_valueの詳細は、Oracle Database SQL言語リファレンスを参照してください。

親トピック: SQL/JSONファンクションJSON_VALUE

19.3 JSON_TABLEとしてのJSON_VALUE

SQL/JSONファンクションjson_valueは、関数json_tableの特別な事例であるとみなすことができます。

例19-7に、この同等性を示します。2つのSELECT文で得られる結果は同じになります。

この対応は、おそらくjson_valueについてより深く理解する手助けとなるのみでなく、どちらの関数を使用しても同じ結果が得られることを意味しているため、実質的に重要な意味を持ちます。

特に、json_valueを複数回使用する場合、またはこれをjson_existsまたはjson_query (これらもjson_tableを使用して表すことができます)と組み合せて使用して同じデータにアクセスする場合、json_tableを1回呼び出す方が、データが解析されるのが1回のみであるという利点があります。

このため、オプティマイザがjson_exists、json_valueおよびjson_queryの複数の呼出し(任意の組合せ)を、より少ないjson_tableの呼出しに自動的にリライトすることがよく起こります。

ノート:

SQLヒントNO_JSON_TABLE_TRANSFORMを使用すると、json_exists、json_valueおよびjson_queryの複数の呼出し(任意の組合せ)が、より少ない数の、json_tableの呼出しにリライトされなくなります。

例19-7 JSON_TABLEを使用して表されたJSON_VALUE


SELECT json_value(column, json_path 
                  RETURNING data_type error_hander ON ERROR)
  FROM table;

SELECT jt.column_alias
  FROM table,
       json_table(column, '$' error_handler ON ERROR
         COLUMNS ("COLUMN_ALIAS" data_type PATH json_path)) AS "JT";

関連項目

親トピック: SQL/JSONファンクションJSON_VALUE


脚注の凡例

脚注1: 入力データがJSONデータ型であり、基になるJSON言語スカラー型がバイナリである場合のみ、戻り型としてRAWを使用できます。それ以外の場合は、エラーが処理(対処)されます。