MongoDBで$lookupを使って2つのコレクションを集約し、フィールド値の大小を比較して抽出する方法

MongoDBで2つのコレクションを集約してフィールド値を比較するには？

MongoDBで2つのコレクションを結合（集約）し、「片方のコレクションのフィールド値が、もう片方の対応するフィールド値よりも大きい」ドキュメントだけを抽出したいケースはよくあります。このような場合には、集計パイプラインの $lookup でコレクションを結合し、$redact で条件判定を行うのが効果的です。

以下では、実際にサンプルデータを作成しながら、具体的な手順を順番に解説します。

ステップ1：最初のコレクション（demo446）を作成する

まず、商品名（ProductName）と価格（ProductPrice）を持つドキュメントを、コレクション「demo446」に挿入します。

> db.demo446.insert([
...     { "ProductName": "Product1", "ProductPrice": 60 },
...     { "ProductName": "Product2", "ProductPrice": 90 }
... ])
BulkWriteResult({
    "writeErrors" : [ ],
    "writeConcernErrors" : [ ],
    "nInserted" : 2,
    "nUpserted" : 0,
    "nMatched" : 0,
    "nModified" : 0,
    "nRemoved" : 0,
    "upserted" : [ ]
})

※ 新しいバージョンの MongoDB では、複数ドキュメントの挿入に insertMany() を使うことが推奨されています。

ステップ2：挿入したドキュメントを確認する

find() メソッドを使うと、コレクション内のすべてのドキュメントを表示できます。

> db.demo446.find();

実行結果は次のとおりです。

{ "_id" : ObjectId("5e790766bbc41e36cc3caec3"), "ProductName" : "Product1", "ProductPrice" : 60 }
{ "_id" : ObjectId("5e790766bbc41e36cc3caec4"), "ProductName" : "Product2", "ProductPrice" : 90 }

ステップ3：2つ目のコレクション（demo447）を作成する

続いて、比較対象となる2つ目のコレクション「demo447」を作成します。同じ商品名に対して、異なる価格のドキュメントを用意します。

> db.demo447.insert([
...
...     { "ProductName": "Product1", "ProductPrice": 40 },
...     { "ProductName": "Product2", "ProductPrice": 70 }
... ])
BulkWriteResult({
    "writeErrors" : [ ],
    "writeConcernErrors" : [ ],
    "nInserted" : 2,
    "nUpserted" : 0,
    "nMatched" : 0,
    "nModified" : 0,
    "nRemoved" : 0,
    "upserted" : [ ]
})

ステップ4：2つ目のコレクションの内容を確認する

こちらも find() メソッドで内容を確認しておきましょう。

> db.demo447.find();

実行結果は次のとおりです。

{ "_id" : ObjectId("5e790789bbc41e36cc3caec5"), "ProductName" : "Product1", "ProductPrice" : 40 }
{ "_id" : ObjectId("5e790789bbc41e36cc3caec6"), "ProductName" : "Product2", "ProductPrice" : 70 }

ステップ5：$lookupと$redactで集約・条件抽出を行う

ここからが本題です。次のクエリでは、「demo446 の ProductPrice が、demo447 の ProductPrice × rate ＋ 3 よりも大きい」という条件を満たすドキュメントだけを抽出しています。

> var rate = 1;
> db.demo446.aggregate([
... { "$match": { "ProductPrice": { "$exists": true }, "ProductName": { "$exists": true } } },
... {
...     "$lookup": {
...         "from": "demo447",
...         "localField": "ProductName",
...         "foreignField": "ProductName",
...         "as": "demo447"
...     }
... },
... { "$unwind": "$demo447" },
... {
...     "$redact": {
...         "$cond": [
...             {
...                 "$gt": [
...                     "$ProductPrice", {
...                         "$add": [
...                             { "$multiply": [ "$demo447.ProductPrice",rate ] },
...                             3
...                         ]
...                     }
...                 ]
...             },
...             "$$KEEP",
...             "$$PRUNE"
...         ]
...     }
... }
... ])

パイプラインの各ステージの役割は次のとおりです。

• $match：ProductPrice と ProductName の各フィールドが存在するドキュメントだけに絞り込みます。
• $lookup：ProductName をキーとして、demo447 コレクション内の一致するドキュメントを結合します。
• $unwind：$lookup によって生成された配列を展開し、1件ずつのドキュメントとして扱えるようにします。
• $redact：$cond の条件式の結果に応じて、ドキュメントを保持（$$KEEP）するか除外（$$PRUNE）するかを判定します。

実行結果は次のとおりです。

{ "_id" : ObjectId("5e790766bbc41e36cc3caec3"), "ProductName" : "Product1", "ProductPrice" : 60, "demo447" : { "_id" : ObjectId("5e790789bbc41e36cc3caec5"), "ProductName" : "Product1", "ProductPrice" : 40 } }
{ "_id" : ObjectId("5e790766bbc41e36cc3caec4"), "ProductName" : "Product2", "ProductPrice" : 90, "demo447" : { "_id" : ObjectId("5e790789bbc41e36cc3caec6"), "ProductName" : "Product2", "ProductPrice" : 70 } }

結果の解説

変数 rate は 1 に設定されているため、判定条件は「demo446.ProductPrice ＞ demo447.ProductPrice × 1 ＋ 3」となります。それぞれの商品について確認してみましょう。

• Product1：60 ＞ 40 × 1 ＋ 3 = 43 → 条件を満たすため保持されます。
• Product2：90 ＞ 70 × 1 ＋ 3 = 73 → 条件を満たすため保持されます。

その結果、両方のドキュメントが demo447 の情報と結合された状態で出力されました。もし条件を満たさないドキュメントが存在すれば、$$PRUNE によって自動的に除外されます。

まとめ

$lookup でコレクションを結合し、$unwind で配列を展開した後、$redact の $cond 内で $gt・$add・$multiply などの演算子を組み合わせることで、コレクション間のフィールド値の大小比較を実現できます。なお、MongoDB 3.6 以降では、$redact の代わりに $expr を使った $match ステージでも同様の処理が可能です。環境や可読性に応じて使い分けるとよいでしょう。