😀

MongoDBでサブクエリ的な検索をする方法（aggregate・集計）

に公開
MongoDB
SQL
SPA
DocumentDB
aggregate
tech

はじめに

最近、業務でMongoShellを叩いて、データを検索するのが多くありました。
その際、DocumentDBでサブクエリ的な検索をしたいと思ったのですが、なかなかジャストな記事がなかったので、自身の備忘録として作成しようと思います。

コレクションの結合

検索するにあたり、必要になるのはコレクションを結合し、結合結果から検索（抽出）をかけ、必要なフィールドを返すようにするという手順を踏む必要があります。
まずは、結合ですがSQLでいうとinner joinやouter joinなどにあたります。

結合の方法は以下のようにします。

db.collection.aggregate([{$lookup: {from: "別のコレクション名", localField: "結合するフィールド名(db.collectionで指定したcollectionのフィールド名)", foreignField: "結合されるフィールド名(lookupしたコレクションのフィールド名)", as: "結合結果のフィールド名" }}]);

例えば、userコレクションとpostコレクションを結合する場合は、以下のようになります。

db.user.aggregate([{$lookup: {from: 'post', localField: '_id', foreignField: 'userId', as: 'post'}}])

結果としては結合した結果なので、以下のようにuserとpostの両コレクションの全フィールドが出力されます。

[
 {
    _id: ObjectId("XXXXXXXXXXXXXXXXXXXX"),
    userName: 'hoge',
    email: 'hoge@hoge.co.jp',
    createDate: ISODate("2023-03-11T11:00:00.000Z"),
    updateDate: ISODate("2023-03-11T11:00:00.000Z"),
    post: [
      {
        _id: ObjectId("XXXXXXXXXXXXXXXXXXXXXXXXXX"),
        userId: ObjectId("XXXXXXXXXXXXXXXXXXXXXXX"),
        text: "fugafuga",
        createDate: ISODate("2023-03-10T11:00:00.000Z"),
        updateDate: ISODate("2023-03-10T11:00:00.000Z")
      }
    ]
  }
]

lookupした結果を絞り込む

上記の結合のみだと、「userのidがXの投稿を取得したい」などの検索要件を達成することができません。
このような場合は、$match演算子を使用して絞り込むことができます。
これは、SQLのWhere句に相当します。

db.user.aggregate([{$lookup: {from: 'post', localField: '_id', foreignField: 'userId', as: 'post'}},  {$match: {_id: ObjectId("XXXXXXXXXXXXXXXXXXXX")}]);

このようにすることで、該当するuserとpostのデータを取得することができます。
また、$elemMatch演算子を使用することでpostコレクションのuserIdを使用しての絞り込みも可能となります。

db.user.aggregate([{$lookup: {from: 'post', localField: '_id', foreignField: 'userId', as: 'post'}},  {$match: {post: {$elemMatch: {userId: ObjectId("XXXXXXXXXXXXXXXXXXXXX")}}}}])

上記の2つは、同じ結果を返します。

また、$match演算子を、$lookup演算子の前に置くことで、最初に抽出した結果に対して結合することも可能です。

db.user.aggregate([{$match: {_id: ObjectId("XXXXXXXXXXXXXXXXX")}}, {$lookup: {from: 'post', localField: '_id', foreignField: 'userId', as: 'post'}}]);

これでも同様の結果を返すことができます。
各自、検索要件に合わせて$match演算子を使用してみてください。

出力するフィールドを決める

今までのやり方だと、不要なフィールドまで出力してしまいます。
そこで、特定のフィールドのみ出力するために$project演算子を使用します。
これは、SQLのSELECTに続く値を記述する部分に相当します。

以下、例です。

db.user.aggregate([{$match: {_id: ObjectId("XXXXXXXXXXXXXXXXX")}}, {$lookup: {from: 'post', localField: '_id', foreignField: 'userId', as: 'post'}}, {$project: {_id: 1, userName:1, post:{text: 1}}}]);

このようにすることで、userコレクションの_id、userName、postコレクションのtextのみ出力することができます。
また、$project演算子の数値はブール値(true・false)で書くことも可能です。
ちなみに、1がtrueで0がfalseに該当します。（わざわざ全フィールドでこれを指定する必要はないかもしれませんが・・・)

検索結果から条件に合致するものを除外する

上記の要件を満たすには、$not演算子と$in演算子を使用します。
これはSQLのnot inにあたります。

db.user.aggregate([{$match: {_id: ObjectId("XXXXXXXXXXXXXXXXX")}}, {$lookup: {from: 'post', localField: '_id', foreignField: 'userId', as: 'post'}}, {$project: {_id: 1, userName:1, post:{text: 1}}}, {$match: {"post.text": {$not: {$in: ["foo"]}}}}]);

このようにすることで、postコレクションのtextフィールドに「foo」を含む値を除外して出力することができます。

件数を出力

aggregateした結果をcountするには、$count演算子を使用します。
これはまんまですが、SQLでいうところのSELECT COUNT(*)に相当します。
例えば、先ほどの$not演算子を使用したものだと以下のようになります。

db.user.aggregate([{$match: {_id: ObjectId("XXXXXXXXXXXXXXXXX")}}, {$lookup: {from: 'post', localField: '_id', foreignField: 'userId', as: 'post'}}, {$project: {_id: 1, userName:1, post:{text: 1}}}, {$match: {"post.text": {$not: {$in: ["foo"]}}}}, {$count: "count"}]);

これを実行すると、countフィールドが作られ、そこの値に件数が格納されているデータが出力されます。

ソートする

ソートするには、$sort演算子を使用します。
SQLでいうとorder byに相当します。

db.user.aggregate([{$match: {_id: ObjectId("XXXXXXXXXXXXXXXXX")}}, {$lookup: {from: 'post', localField: '_id', foreignField: 'userId', as: 'post'}}, {$project: {_id: 1, userName:1, post:{text: 1}}}, {$match: {"post.text": {$not: {$in: ["foo"]}}}}, {$sort: {"createDate": -1}}]);

これをするとcreateDateで並べ替えることができます。
なお、-1が降順、1が昇順となります。

#　おわりに
他にも＄limit演算子や$group演算子などがあり、表示件数を指定したり、グループ化する方法もあるので、使用してみてください。

Discussion