19.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つの出現を参照します。

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

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

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

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

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

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

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

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

• 1つ目の問合せでは、フィルタの有効範囲はコンテキスト項目、つまり、発注書全体です。@はコンテキスト項目を参照します。

• 2つ目の問合せでは、フィルタの有効範囲はLineItems配列(とその各要素、暗黙的)です。@はその配列の要素を参照します。

• 3つ目の問合せでは、フィルタの有効範囲は、LineItems配列の1つの要素のPartフィールドです。@はPartフィールドを参照します。

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

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

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

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

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

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

例19-5 JSON_EXISTS: 条件が存在するパス式を使用したパス式

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

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

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