Azure Cosmos DB for NoSQL でのサブクエリ

[アーティクル]
08/22/2024

適用対象: NoSQL

サブクエリは、Azure Cosmos DB for NoSQL 内の別のクエリ内に入れ子になったクエリです。サブクエリは "内部クエリ" または "内部 SELECT" とも呼ばれます。サブクエリを含むステートメントは通常、"外部クエリ" と呼ばれます。

サブクエリの種類

サブクエリには、主に次の 2 種類があります。

相関: 外部クエリからの値を参照するサブクエリ。サブクエリは、外部クエリが処理する行ごとに 1 回評価されます。
非相関: 外部クエリから独立しているサブクエリ。外部クエリに依存せずに単独で実行できます。

Note

Azure Cosmos DB は相関サブクエリのみをサポートします。

サブクエリは、返される行と列の数に基づいてさらに分類できます。次の 3 種類があります。

テーブル: 複数の行と複数の列を返します。
複数値: 複数の行と 1 つの列を返します。
スカラー: 1 つの行と 1 つの列を返します。

Azure Cosmos DB for NoSQL のクエリは常に 1 つの列 (単純な値または複雑な項目のどちらか) を返します。したがって、複数値サブクエリとスカラーサブクエリのみを適用できます。複数値サブクエリは、関係式として FROM 句でのみ使用できます。スカラーサブクエリは、SELECT または WHERE 句でスカラー式として、あるいは FROM 句で関係式として使用できます。

複数値サブクエリ

複数値サブクエリは項目のセットを返し、常に FROM 句内で使用されます。次の目的に使用されます。

JOIN (自己結合) 式を最適化する。
コストが高い式を 1 回評価して複数回参照する。

自己結合式を最適化する

複数値サブクエリは、WHERE 句内のすべての "クロス結合" の後ではなく、それぞれの select-many 式の後に述語をプッシュすることによって JOIN 式を最適化できます。

次のクエリを考えてみます。

SELECT VALUE
    COUNT(1)
FROM
    products p
JOIN 
    t in p.tags
JOIN 
    q in p.onHandQuantities
JOIN 
    s in p.warehouseStock
WHERE 
    t.name IN ("winter", "fall") AND
    (q.quantity BETWEEN 0 AND 10) AND
    NOT s.backstock

このクエリの場合、インデックスは、name が "winter" または "fall" のいずれかのタグを持ち、少なくとも 1 つの quantity が 0 から 10 までの間で、少なくとも 1 つのウェアハウスの backstock が false であるすべての項目と一致します。ここでの JOIN 式は、あらゆるフィルターが適用される前に、一致する項目ごとに、tags、onHandQuantities、warehouseStock の各配列の全項目の "外積" を実行します。

WHERE 句はその後、<c, t, n, s> タプルごとにフィルター述語を適用します。たとえば、一致する項目で、3 つの配列のそれぞれに 10 個の項目があるとした場合、1 x 10 x 10 x 10 (つまり、1,000) タプルに展開されます。ここでサブクエリを使用すると、次の式と結合する前に、結合された配列項目をフィルターで除外するために役立ちます。

このクエリは前のものと同等ですが、サブクエリを使用します。

SELECT VALUE
    COUNT(1)
FROM
    products p
JOIN 
    (SELECT VALUE t FROM t IN p.tags WHERE t.name IN ("winter", "fall"))
JOIN 
    (SELECT VALUE q FROM q IN p.onHandQuantities WHERE q.quantity BETWEEN 0 AND 10)
JOIN 
    (SELECT VALUE s FROM s IN p.warehouseStock WHERE NOT s.backstock)

タグ配列内の 1 つの項目のみがフィルターに一致し、quantity 配列と stock 配列の両方に 5 つの項目があるとします。 JOIN 式は、最初のクエリの 1,000 項目とは異なり、1 x 1 x 5 x 5 (25) 項目に展開されます。

1 回評価して複数回参照する

サブクエリは、ユーザー定義関数 (UDF)、複雑な文字列、算術式などのコストが高い式でクエリを最適化するために役立ちます。サブクエリを JOIN 式と一緒に使用することで、式を 1 回しか評価しないが複数回参照するという処理ができます。

次の UDF (getTotalWithTax) が定義されているとします。

function getTotalWithTax(subTotal){
  return subTotal * 1.25;
}

次のクエリは UDF getTotalWithTax を複数回実行します。

SELECT VALUE {
    subtotal: p.price,
    total: udf.getTotalWithTax(p.price)
}
FROM
    products p
WHERE
    udf.getTotalWithTax(p.price) < 22.25

次に示すのは、UDF を 1 回しか実行しない同等のクエリです。

SELECT VALUE {
    subtotal: p.price,
    total: totalPrice
}
FROM
    products p
JOIN
    (SELECT VALUE udf.getTotalWithTax(p.price)) totalPrice
WHERE
    totalPrice < 22.25

ヒント

JOIN 式の外積の動作に留意してください。 UDF 式が undefined と評価される可能性がある場合、値を直接返すのではなくサブクエリからのオブジェクトを返すことによって、JOIN 式の結果が常に 1 行になることを保証してください。

外部参照データとの結合を模倣する

"測定単位" のように、ほとんど変更されない静的データを参照することが必要になる場合がよくあります。クエリ内の各項目の静的データを重複しないようにするのが理想的です。この重複を回避して個々の項目のサイズを小さく保つことで、ストレージを節約し、書き込みパフォーマンスを高めることができます。サブクエリを使用して、静的参照データのコレクションとの内部結合セマンティクスを模倣することができます。

たとえば、次のような測定のセットを考えます。

	名前	乗数	基本単位
`ng`	ナノグラム	`1.00E-09`	グラム
`µg`	マイクログラム	`1.00E-06`	グラム
`mg`	ミリグラム	`1.00E-03`	グラム
`g`	グラム	`1.00E+00`	グラム
`kg`	キログラム	`1.00E+03`	グラム
`Mg`	メガグラム	`1.00E+06`	グラム
`Gg`	ギガグラム	`1.00E+09`	グラム

次のクエリは、このデータとの結合を模倣し、単位の名前を出力に追加します。

SELECT
    s.id,
    (s.weight.quantity * m.multiplier) AS calculatedWeight,
    m.unit AS unitOfWeight
FROM
    shipments s
JOIN m IN (
    SELECT VALUE [
        {unit: 'ng', name: 'nanogram', multiplier: 0.000000001, baseUnit: 'gram'},
        {unit: 'µg', name: 'microgram', multiplier: 0.000001, baseUnit: 'gram'},
        {unit: 'mg', name: 'milligram', multiplier: 0.001, baseUnit: 'gram'},
        {unit: 'g', name: 'gram', multiplier: 1, baseUnit: 'gram'},
        {unit: 'kg', name: 'kilogram', multiplier: 1000, baseUnit: 'gram'},
        {unit: 'Mg', name: 'megagram', multiplier: 1000000, baseUnit: 'gram'},
        {unit: 'Gg', name: 'gigagram', multiplier: 1000000000, baseUnit: 'gram'}
    ]
)
WHERE
    s.weight.units = m.unit

スカラーサブクエリ

スカラーサブクエリ式は、1 つの値に評価されるサブクエリです。スカラーサブクエリ式の値は、サブクエリのプロジェクション (SELECT 句) の値です。スカラー式が有効な多くの場面で、スカラーサブクエリ式を使用できます。たとえば、SELECT 句と WHERE 句両方の任意の式でスカラーサブクエリを使用できます。

スカラーサブクエリの使用が常にクエリの最適化に寄与するとは限りません。たとえば、スカラーサブクエリをシステムまたはユーザー定義関数に引数として渡しても、リソース単位 (RU) 消費または待機時間を削減する上でのメリットはありません。

スカラーサブクエリは、さらに次のように分類できます。

単純式スカラーサブクエリ
集計スカラーサブクエリ

単純式スカラーサブクエリ

単純式スカラーサブクエリは、集約式を含まない SELECT 句を持つ相関サブクエリです。これらのサブクエリは、コンパイラによって 1 つのより大きな単純式に変換されるため、最適化のベネフィットをもたらしません。内部クエリと外部クエリの間に相関コンテキストはありません。

最初の例として、この単純なクエリを考えてみましょう。

SELECT
    1 AS a,
    2 AS b

単純式スカラーサブクエリを使用して、このクエリを書き直すことができます。

SELECT
    (SELECT VALUE 1) AS a, 
    (SELECT VALUE 2) AS b

どちらのクエリも同じ出力を生成します。

[
  {
    "a": 1,
    "b": 2
  }
]

次のクエリ例では、一意識別子とプレフィックスを単純式スカラーサブクエリとして連結します。

SELECT 
    (SELECT VALUE Concat('ID-', p.id)) AS internalId
FROM
    products p

この例では単純式スカラーサブクエリを使用して、各項目の関連フィールドのみを返します。クエリは各項目に対して何かを出力しますが、サブクエリ内のフィルターを満たしている場合に、射影フィールドのみが含まれます。

SELECT
    p.id,
    (SELECT p.name WHERE CONTAINS(p.name, "glove")).name
FROM
    products p

[
  {
    "id": "aaaaaaaa-0000-1111-2222-bbbbbbbbbbbb",
    "name": "Winter glove"
  },
  {
    "id": "bbbbbbbb-1111-2222-3333-cccccccccccc"
  },
  {
    "id": "cccccccc-2222-3333-4444-dddddddddddd"
  }
]

集計スカラーサブクエリ

集計スカラーサブクエリは、1 つの値に評価されるそのプロジェクションまたはフィルターに集計関数が含まれるサブクエリです。

最初の例として、次のフィールドを持つ項目を考えてみましょう。

{
  "name": "Snow coat",
  "inventory": [
    {
      "location": "Redmond, WA",
      "quantity": 50
    },
    {
      "location": "Seattle, WA",
      "quantity": 30
    },
    {
      "location": "Washington, DC",
      "quantity": 25
    }
  ]
}

次に示すのは、1 つの集計関数式がそのプロジェクションに含まれるサブクエリです。このクエリでは、各項目のすべてのタグがカウントされます。

SELECT
    p.name,
    (SELECT VALUE COUNT(1) FROM i IN p.inventory) AS locationCount
FROM
    products p

[
  {
    "name": "Snow coat",
    "locationCount": 3
  }
]

次に、フィルターを含む同じサブクエリを示します。

SELECT
    p.name,
    (SELECT VALUE COUNT(1) FROM i IN p.inventory WHERE ENDSWITH(i.location, "WA")) AS washingtonLocationCount
FROM
    products p

[
  {
    "name": "Snow coat",
    "washingtonLocationCount": 2
  }
]

次に示すのは、複数の集計関数式が含まれる別のサブクエリです。

SELECT
    p.name,
    (SELECT
        COUNT(1) AS locationCount,
        SUM(i.quantity) AS totalQuantity
    FROM i IN p.inventory) AS inventoryData
FROM
    products p

[
  {
    "name": "Snow coat",
    "inventoryData": {
      "locationCount": 2,
      "totalQuantity": 75
    }
  }
]

最後に示すのは、プロジェクションとフィルターの両方に集計サブクエリが含まれるクエリです。

SELECT
    p.name,
    (SELECT VALUE AVG(q.quantity) FROM q IN p.inventory WHERE q.quantity > 10) AS averageInventory
FROM
    products p
WHERE
    (SELECT VALUE COUNT(1) FROM i IN p.inventory WHERE i.quantity > 10) >= 1

[
  {
    "name": "Snow coat",
    "averageInventory": 35
  }
]

このクエリのより最適な記述方法は、サブクエリで結合し、SELECT 句と WHERE 句の両方でサブクエリの別名を参照することです。このクエリのほうが効率的なのは、JOIN ステートメントの中でサブクエリを実行するだけでよく、プロジェクションとフィルターの両方で実行する必要がないからです。

SELECT
    p.name,
    inventoryData.inventoryAverage
FROM
    products p
JOIN
    (SELECT 
        COUNT(1) AS inventoryCount, 
        AVG(i.quantity) as inventoryAverage 
    FROM i IN p.inventory 
    WHERE i.quantity > 10) AS inventoryData
WHERE
    inventoryData.inventoryCount >= 1

EXISTS 式

Azure Cosmos DB for NoSQL のクエリエンジンでは、EXISTS 式がサポートされています。この式は、Azure Cosmos DB for NoSQL に組み込まれている集計スカラーサブクエリです。 EXISTS は、サブクエリ式を取り、サブクエリが行を返す場合は true を返します。それ以外の場合は、 falseを返します。

クエリエンジンはブール式と他のスカラー式を区別しないため、SELECT 句と WHERE 句の両方で EXISTS を使用できます。この動作は、ブール式がフィルターのみに制限されている T-SQL とは異なります。

EXISTS サブクエリにより undefined の 1 つの値が返された場合、EXISTS は false に評価されます。たとえば、何も返されない次のクエリを考えてみましょう。

SELECT VALUE
    undefined

EXISTS 式と前のクエリをサブクエリとして使用する場合、式は false を返します。

SELECT
    EXISTS (SELECT VALUE undefined)

[
  {
    "$1": false
  }
]

前のサブクエリの VALUE キーワードを省略すると、サブクエリは 1 つの空のオブジェクトを持つ配列に評価されます。

SELECT
    undefined

[
  {}
]

その時点で、オブジェクト ({}) が技術的に存在するため、EXISTS 式は true に評価されます。

SELECT 
    EXISTS (SELECT undefined)

[
  {
    "$1": true
  }
]

ARRAY_CONTAINS の一般的なユースケースでは、配列内の項目の存在によって項目をフィルター処理します。この場合は、"outerwear" という名前の項目が tags 配列に含まれているかどうかチェックしています。

SELECT
    p.name,
    p.tags
FROM
    products p
WHERE
    ARRAY_CONTAINS(p.tags, "outerwear")

同じクエリで EXISTS を別のオプションとして使用できます。

SELECT
    p.name,
    p.tags
FROM
    products p
WHERE
    EXISTS (SELECT VALUE t FROM t IN p.tags WHERE t = "outerwear")

さらに、ARRAY_CONTAINS がチェックできるのは、配列内のいずれかの要素に値が等しいかどうかだけです。さらに複雑なフィルターが配列プロパティに対して必要な場合は、代わりに JOIN を使用します。

複数の項目がそれぞれ accessories 配列を含むセット内の項目の例について考えてみましょう。

{
  "name": "Unobtani road bike",
  "accessories": [
    {
      "name": "Front/rear tire",
      "type": "tire",
      "quantityOnHand": 5
    },
    {
      "name": "9-speed chain",
      "type": "chains",
      "quantityOnHand": 25
    },
    {
      "name": "Clip-in pedals",
      "type": "pedals",
      "quantityOnHand": 15
    }
  ]
}

ここで、各項目内の配列の type および quantityOnHand プロパティに基づいてフィルター処理を行う次のクエリを考えます。

SELECT
    p.name,
    a.name AS accessoryName
FROM
    products p
JOIN
    a IN p.accessories
WHERE
    a.type = "chains" AND
    a.quantityOnHand >= 10

[
  {
    "name": "Unobtani road bike",
    "accessoryName": "9-speed chain"
  }
]

コレクション内の項目ごとに、その配列要素を使用して外積を実行します。この JOIN 操作により、配列内のプロパティをフィルター処理できます。ただし、このクエリの RU 消費量は多大です。たとえば、1,000 個の項目で、各配列に 100 個の項目があるとした場合、1,000 x 100 (つまり、100,000) タプルに展開されます。

EXISTS を使用すれば、このようにコストが高い外積を回避できます。この次の例では、クエリは EXISTS サブクエリ内で配列要素をフィルター処理します。配列要素がフィルターに一致した場合、それを射影し、EXISTS は true と評価されます。

SELECT VALUE
    p.name
FROM
    products p
WHERE
    EXISTS (SELECT VALUE 
        a 
    FROM 
        a IN p.accessories
    WHERE
        a.type = "chains" AND
        a.quantityOnHand >= 10)

[
  "Unobtani road bike"
]

クエリでは EXISTS に別名を付けて、プロジェクション内で別名を参照することもできます。

SELECT
    p.name,
    EXISTS (SELECT VALUE
        a 
    FROM 
        a IN p.accessories
    WHERE
        a.type = "chains" AND
        a.quantityOnHand >= 10) AS chainAccessoryAvailable
FROM
    products p

[
  {
    "name": "Unobtani road bike",
    "chainAccessoryAvailable": true
  }
]

ARRAY 式

ARRAY 式を使用して、クエリの結果を配列として射影することができます。この式はクエリの SELECT 句内でのみ使用できます。

これらの例では、少なくとも次の項目を含むコンテナーがあるとします。

{
  "name": "Radimer mountain bike",
  "tags": [
    {
      "name": "road"
    },
    {
      "name": "bike"
    },
    {
      "name": "competitive"
    }
  ]
}

次の最初の例では、この式は SELECT 句内で使用されています。

SELECT
    p.name,
    ARRAY (SELECT VALUE t.name FROM t in p.tags) AS tagNames
FROM
    products p

[
  {
    "name": "Radimer mountain bike",
    "tagNames": [
      "road",
      "bike",
      "competitive"
    ]
  }
]

他のサブクエリと同様、ARRAY 式によるフィルターが可能です。

SELECT
    p.name,
    ARRAY (SELECT VALUE t.name FROM t in p.tags) AS tagNames,
    ARRAY (SELECT VALUE t.name FROM t in p.tags WHERE CONTAINS(t.name, "bike")) AS bikeTagNames
FROM
    products p

[
  {
    "name": "Radimer mountain bike",
    "tagNames": [
      "road",
      "bike",
      "competitive"
    ],
    "bikeTagNames": [
      "bike"
    ]
  }
]

配列式は、サブクエリの FROM 句の後に置くこともできます。

SELECT
    p.name,
    n.t.name AS nonBikeTagName
FROM
    products p
JOIN
    n IN (SELECT VALUE ARRAY(SELECT t FROM t in p.tags WHERE t.name NOT LIKE "%bike%"))

[
  {
    "name": "Radimer mountain bike",
    "nonBikeTagName": "road"
  },
  {
    "name": "Radimer mountain bike",
    "nonBikeTagName": "competitive"
  }
]

次の方法で共有

Azure Cosmos DB for NoSQL でのサブクエリ

サブクエリの種類

複数値サブクエリ

自己結合式を最適化する

1 回評価して複数回参照する

外部参照データとの結合を模倣する

スカラーサブクエリ

単純式スカラーサブクエリ

集計スカラーサブクエリ

EXISTS 式

ARRAY 式

フィードバック

その他のリソース

次の方法で共有

Azure Cosmos DB for NoSQL でのサブクエリ

サブクエリの種類

複数値サブクエリ

自己結合式を最適化する

1 回評価して複数回参照する

外部参照データとの結合を模倣する

スカラー サブクエリ

単純式スカラー サブクエリ

集計スカラー サブクエリ

EXISTS 式

ARRAY 式

関連するコンテンツ

フィードバック

その他のリソース

スカラーサブクエリ

単純式スカラーサブクエリ

集計スカラーサブクエリ