如何在 mongoDB 中对 $lookup 结果应用条件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38954687/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to apply condition on $lookup result in mongoDB?
提问by Dipak chavda
With the reference of my previous question, I have a question about $lookup with add some conditions. You may get enough reference about question from below link description.
参考我之前的问题，我有一个关于 $lookup 的问题，并添加了一些条件。您可以从以下链接描述中获得有关问题的足够参考。
Photo:
照片：
{_id: 1, photo_name: '1.jpg', photo_description: 'description 1', album_id: 1, flag:1 },
{_id: 2, photo_name: '2.jpg', photo_description: 'description 2', album_id: 1, flag:1 },
{_id: 3, photo_name: '3.jpg', photo_description: 'description 3', album_id: 1, flag:1 },
{_id: 4, photo_name: '4.jpg', photo_description: 'description 4', album_id: 2, flag:0 },
{_id: 5, photo_name: '5.jpg', photo_description: 'description 5', album_id: 2, flag:0 },
{_id: 6, photo_name: '6.jpg', photo_description: 'description 6', album_id: 2}
Album:
专辑：
{_id: 1, album_name: "my album 1", album_description: "album description 1", emoji_id: 1},
{_id: 2, album_name: "my album 2", album_description: "album description 2", emoji_id: 2},
{_id: 3, album_name: "my album 3", album_description: "album description 3", emoji_id: 3},
{_id: 4, album_name: "my album 4", album_description: "album description 4", emoji_id: 4},
{_id: 5, album_name: "my album 5", album_description: "album description 5", emoji_id: 5}
Emoji:
表情符号：
{_id: 1, emoji_name: "1.jpg"},
{_id: 2, emoji_name: "2.jpg"},
{_id: 3, emoji_name: "3.jpg"},
{_id: 4, emoji_name: "4.jpg"},
{_id: 5, emoji_name: "5.jpg"},
{_id: 6, emoji_name: "6.jpg"},
{_id: 7, emoji_name: "7.jpg"},
{_id: 8, emoji_name: "8.jpg"}
Testing record pagination :
测试记录分页：
2
2
Suppose I add one another field of flag in photo collection and now I want to get count only those photos whose flag is one.
假设我在照片集合中添加了另一个标志字段，现在我只想计算标志为 1 的那些照片。
I tried to add $match immediately after $lookup in query, but it fails, It doesn't exclude photos whose flag=0 and also in counter it does not flag condition.
我试图在查询中的 $lookup 之后立即添加 $match，但它失败了，它不排除 flag=0 的照片，并且在计数器中它也没有标记条件。
Present Output There are 3 photos out of 10 photos having set flag 0. And we could not consider those photos whose flag is 0. So expected total is 7 photos but count returns 10 photos though I applied condition of flag in photos.
当前输出 10 张照片中有 3 张设置了标志 0。我们不能考虑那些标志为 0 的照片。所以预期总数是 7 张照片但计数返回 10 张照片，尽管我在照片中应用了标志条件。
Present Query:
当前查询：
db
.album
.aggregate([
{
$lookup:{
from:"photo",
localField:"_id",
foreignField:"album_id",
as:"photo"
}
},
{
$match:{
"photo.flag": 1
}
},
{
$lookup:{
from:"emoji",
localField:"album_emoji",
foreignField:"_id",
as:"emoji"
}
},
{
$project:{
album_name:"$album_name",
album_description:"$album_description",
album_emoji:"$emoji.image_name",
photo:"$photo",
total_photos: {$size: "$photo"}
}
}
])
.toArray();
Expected output:
预期输出：
[
{
"_id" : 1,
"album_name" : "Album 1",
"album_description" : "Album description 1",
"album_emoji" : [
"1.jpg"
],
"total_photos" : 3,
"photo" : [
{
"_id" : 1,
"album_id" : 1,
"photo_description" : "description 1",
"photo_name" : "1.jpg",
"flag" : 0,
},
{
"_id" : 2,
"album_id" : 1,
"photo_description" : "description 2",
"photo_name" : "2.jpg",
"flag" : 0,
},
{
"_id" : 1,
"album_id" : 1,
"photo_description" : "description 3",
"photo_name" : "3.jpg",
"flag" : 0,
}
]
}
]
Present output:
当前输出：
[
{
"_id" : 1,
"album_name" : "Album 1",
"album_description" : "Album description 1",
"album_emoji" : [
"1.jpg"
],
"total_photos" : 5,
"photo" : [
{
"_id" : 1,
"album_id" : 1,
"photo_description" : "description 1",
"photo_name" : "1.jpg",
"flag" : 1,
},
{
"_id" : 2,
"album_id" : 1,
"photo_description" : "description 2",
"photo_name" : "2.jpg",
"flag" : 1,
},
{
"_id" : 3,
"album_id" : 1,
"photo_description" : "description 3",
"photo_name" : "3.jpg",
"flag" : 1,
},
{
"_id" : 4,
"album_id" : 1,
"photo_description" : "description 4",
"photo_name" : "4.jpg",
"flag" : 0,
},
{
"_id" : 5,
"album_id" : 1,
"photo_description" : "description 5",
"photo_name" : "5.jpg",
"flag" : 0,
}
]
}
]
回答by Darryl Fabian
You can't use "$match" with a object method after "$lookup", because return value of "$lookup" are array values. You better add "$unwind" function after the look up then group it.
您不能在“$lookup”之后将“$match”与对象方法一起使用，因为“$lookup”的返回值是数组值。您最好在查找后添加“$unwind”函数，然后对其进行分组。
Example Query
示例查询
db
.album
.aggregate([
{
$lookup:{
from:"photo",
localField:"_id",
foreignField:"album_id",
as:"photo"
}
},
{
preserveNullAndEmptyArrays : true,
path : "$photo"
},
{
$match:{
"photo.flag": 1
}
},
{
$group : {
_id : {
id : "$_id",
album_name: "$album_name",
album_description: "$album_description",
emoji_id: "$emoji_id"
},
photo: {
$push : "$photo"
}
}
}
{
$lookup:{
from:"emoji",
localField:"_id.album_emoji",
foreignField:"_id",
as:"emoji"
}
},
{
$project:{
album_name:"$album_name",
album_description:"$album_description",
emoji:"$emoji",
photo:"$photo",
total_photos: {$size: "$photo"}
}
}
])
Or use "$filter".
或者使用“$filter”。
db
.album
.aggregate([
{
$lookup:{
from:"photo",
localField:"_id",
foreignField:"album_id",
as:"photo"
}
},
{
$project: {
id : "$_id",
album_name: "$album_name",
album_description: "$album_description",
emoji_id: "$emoji_id",
photo: {
$filter : {
input: "$photo",
as : "photo_field",
cond : {
$eq: ["$$photo_field.flag",1]
}
}
}
}
},
{
$lookup:{
from:"emoji",
localField:"album_emoji",
foreignField:"_id",
as:"emoji"
}
},
{
$project:{
album_name:"$album_name",
album_description:"$album_description",
emoji:"$emoji",
photo:"$photo",
total_photos: {$size: "$photo"}
}
}
])
回答by Rohit Basu
Your query is wrong. What is localField:"album_emoji", there is no field named album_emojiin Albumcollection. The field name is album_idin Albumcollection which is a foreign key to _idin Emojicollection.
您的查询是错误的。是什么localField:"album_emoji"，有没有现场命名album_emoji的Album集合。字段名称是album_id在Album收集这是一个外键_id的Emoji集合。
If you are using version 3.5, the correct query is (I assume Album, Photo, Emojiare the names of three collections):
如果使用的是3.5版本，正确的查询是（我假设Album，Photo，Emoji是三个集合的名称）：
db.Album.aggregate([
{
$lookup:{
from:"Photo",
localField:"_id",
foreignField:"album_id",
as:"photo"
}
},
{
$match:{
"photo.flag": 1
}
},
{
$lookup:{
from:"Emoji",
localField:"emoji_id",
foreignField:"_id",
as:"emoji"
}
},
{
$project:{
album_name:"$album_name",
album_description:"$album_description",
album_emoji:"$emoji.emoji_name",
photo:"$photo",
total_photos: {$size: "$photo"}
}
}
]).pretty()
And the result is as follows:
结果如下：
/* 1 */
{
"_id" : 1,
"album_name" : "my album 1",
"album_description" : "album description 1",
"album_emoji" : [
"1.jpg"
],
"photo" : [
{
"_id" : 1,
"photo_name" : "1.jpg",
"photo_description" : "description 1",
"album_id" : 1,
"flag" : 1
},
{
"_id" : 2,
"photo_name" : "2.jpg",
"photo_description" : "description 2",
"album_id" : 1,
"flag" : 1
},
{
"_id" : 3,
"photo_name" : "3.jpg",
"photo_description" : "description 3",
"album_id" : 1,
"flag" : 1
}
],
"total_photos" : 3
}
