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'
を戻します。
関連項目:
json_exists
の詳細は、Oracle Database SQL言語リファレンスを参照してください。
トピック:
- JSON_EXISTSでのフィルタの使用
SQL/JSON条件json_exists
は、照合データが格納された文書を選択するために、1つ以上のフィルタ式を持つパス式と一緒に使用できます。フィルタを使用することで、様々な条件を満たす特定のフィールドを持つ文書があるかどうかをテストできます。 - JSON_TABLEとしてのJSON_EXISTS
SQL/JSON条件json_exists
は、SQL/JSONファンクションjson_table
の特別な事例であるとみなすことができます。
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)))');
関連項目
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;