SQL/JSON条件JSON

14 SQL/JSON条件JSON_EXISTS

SQL/JSON条件json_existsでは、SQL/JSONパス式を行フィルタとして使用して、JSON文書の内容に基づいて行を選択できます。条件json_existsは、SELECT文のCASE式またはWHERE句で使用できます。

条件json_existsは、JSONデータ内に特定の値が存在するかどうかをチェックします。値が存在する場合はtrueが戻され、存在しない場合はfalseが戻されます。より正確に言うと、json_existsでは、対象とするデータが1つ以上のJSON値と一致する場合はtrueが戻されます。一致するJSON値がない場合は、falseが戻されます。

また、json_existsを使用して、JSONデータで使用するビットマップ索引を作成することもできます。例24-1を参照してください。

エラー・ハンドラERROR ON ERROR、FALSE ON ERRORおよびTRUE ON ERRORが適用されます。デフォルトはFALSE ON ERRORです。このハンドラが有効になるのはエラーが発生したときですが、通常、エラーが発生するのは、特定のJSONデータが(緩慢な構文を使用した)整形式でない場合です。条件is jsonおよびis not jsonの事例とは異なり、条件json_existsでは、検査するデータが整形式のJSONデータであることを予期しています。

json_existsの2番目の引数はSQL/JSONパス式であり、オプションでPASSING句とオプションのエラー句が付加されます。

json_existsで使用されるSQL/JSONパス式のオプションのフィルタ式では、SQL/JSON変数を参照できます。これらの変数の値は、PASSING句にバインドすることでSQLによって渡されます。このような変数でサポートされるSQLデータ型は、VARCHAR2、NUMBER、BINARY_DOUBLE、DATE、TIMESTAMPおよびTIMESTAMP WITH TIMEZONEです。

注意:

JSON値nullに適用されたSQL/JSON条件json_existsは、SQL文字列'true'を戻します。

14.1 JSON_EXISTSでのフィルタの使用

SQL/JSON条件json_existsは、照合データが格納された文書を選択するために、1つ以上のフィルタ式を持つパス式と一緒に使用できます。フィルタを使用することで、様々な条件を満たす特定のフィールドを持つ文書があるかどうかをテストできます。

SQL/JSON条件json_existsは、SQL/JSONパス式と一致するデータが含まれる文書に対してtrueを戻します。パス式にフィルタが含まれる場合、そのパスと一致するデータにフィルタが適用されます。json_existsが目的のデータが含まれる文書に対してtrueを戻すためには、そのデータはフィルタも満たす必要があります。

フィルタは、直前のパスに適用され、(a)特定の文書にそのパスと一致するデータがあるかどうかと、(b)その一致するデータがフィルタを満たすかどうかがテストされます。これらの両方の条件が該当する場合は、json_existsは文書に対してtrueを戻します。

フィルタの直前のパス式が、そのフィルタで使用されるパターンの有効範囲を定義します。フィルタ内のアット・マーク(@)は、そのパスの対象となるデータを参照します。これを、フィルタのカレント項目と呼びます。たとえば、パス式$.LineItems?(@.Part.UPCCode == 85391628927)の@は、配列LineItemsの1つの出現を参照します。

例14-1 JSON_EXISTS: フィルタのないパス式

この例では、部品説明にUPCコードのエントリが含まれる明細項目を持つ発注書文書を選択します。

SELECT po.po_document FROM j_purchaseorder po
  WHERE json_exists(po.po_document, '$.LineItems.Part.UPCCode');

例14-2 JSON_EXISTS: パス式のフィルタ内のカレント項目と有効範囲

この例では、部品に値85391628927のUPCコードが含まれる明細項目を持つ文書を選択するための、3つの同等の方法を示します。

SELECT po.po_document FROM j_purchaseorder po
  WHERE json_exists(po.po_document, '$?(@.LineItems.Part.UPCCode == 85391628927)');

SELECT po.po_document FROM j_purchaseorder po
  WHERE json_exists(po.po_document, '$.LineItems?(@.Part.UPCCode == 85391628927)');

SELECT po.po_document FROM j_purchaseorder po
  WHERE json_exists(po.po_document, '$.LineItems.Part?(@.UPCCode == 85391628927)');

1つ目の問合せでは、フィルタの有効範囲はコンテキスト項目、つまり、発注書全体です。@はコンテキスト項目を参照します。
2つ目の問合せでは、フィルタの有効範囲はLineItems配列(とその各要素、暗黙的)です。@はその配列の要素を参照します。
3つ目の問合せでは、フィルタの有効範囲は、LineItems配列の1つの要素のPartフィールドです。@はPartフィールドを参照します。

例14-3 JSON_EXISTS: カレント項目に応じたフィルタ条件

この例は、UPCコードが85391628927の部品が含まれる明細項目およびオーダー数量が3を超える明細項目の両方を持つ、発注書文書を選択します。この場合の各フィルタの有効範囲(つまり、カレント項目)は、コンテキスト項目です。各フィルタ条件は、個別に(同じ文書に)適用されます。2つの条件は、必ずしも同じ明細項目に適用されるとは限りません。

SELECT po.po_document FROM j_purchaseorder po
  WHERE json_exists(po.po_document, '$?(@.LineItems.Part.UPCCode == 85391628927
                                        && @.LineItems.Quantity > 3)');

例14-4 JSON_EXISTS: フィルタの有効範囲の限定

この例は例14-3と似ていますが、その動作はまったく異なります。この例は、UPCコードを持ち、さらにオーダー数量が3を超える部品を持つ、明細項目が含まれる発注書文書を選択します。フィルタのカレント項目の有効範囲はより低いレベルになり、コンテキスト項目ではなく、1つのLineItems配列要素になります。つまり、json_existsでtrueが戻されるためには、1つの明細項目が両方の条件を満たす必要があります。

SELECT po.po_document FROM j_purchaseorder po
  WHERE json_exists(po.po_document, '$.LineItems?(@.Part.UPCCode == 85391628927
                                                  && @.Quantity > 3)');

例14-5 JSON_EXISTS: 条件を持つパス式を使用したパス式

この例では、フィルタのある部分の有効範囲を限定し、別の部分の有効範囲は文書(コンテキスト項目)レベルになるようにする方法を示します。この例は、値が"ABULL"のUserフィールドを持つ発注書文書と、UPCコードを持ち、さらにオーダー数量が3を超える部品を持つ明細項目が含まれる発注書文書を選択します。つまり、ここでは、例14-4で選択したのと同じ文書と、ユーザー"ABULL"が含まれるすべての文書が選択されます。パス式の述語existsへの引数は、特定の明細項目を指定するパス式です。この述語は、一致が見つかる(つまり、そのような明細項目が存在する)場合にtrueを返します。

(この例または類似のものをSQL*Plusで使用する場合は、SET DEFINE OFFを最初に使用する必要があります。このようにすることで、SQL*Plusで&& existsが置換変数として解釈されずに、定義するように求めるプロンプトが表示されます。)

SELECT po.po_document FROM j_purchaseorder po
  WHERE json_exists(po.po_document,
                    '$.(@.User == "ABULL"
                        && exists(@.LineItems?(@.Part.UPCCode == 85391628927
                                               && @.Quantity > 3)))');

関連項目

基本的なSQL/JSONパス式の構文

14.2 JSON_TABLEとしてのJSON_EXISTS

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

例14-6に、この対応を示します。2つのSELECT文で得られる結果は同じになります。

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

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

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

例14-6 JSON_TABLEを使用して表されたJSON_EXISTS

SELECT select_list
  FROM table WHERE json_exists(column, json_path error_handler ON ERROR);
       
SELECT select_list
  FROM table,
       json_table(column, '$' error_handler ON ERROR
         COLUMNS ("COLUMN_ALIAS" NUMBER EXISTS PATH json_path)) AS "JT"
  WHERE jt.column_alias = 1;

関連項目

JSON_TABLEによるSQL/JSON問合せファンクションおよび条件の一般化