MongoDBでサブクエリ的な検索をする方法(aggregate・集計)
はじめに
最近、業務で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