如何在 mongoDB 中对 $lookup 结果应用条件? [英] How to apply condition on $lookup result in mongoDB?
问题描述
参考我之前的问题，我有一个关于 $lookup 的问题，并添加了一些条件.您可以从以下链接描述中获得足够的关于问题的参考.
<块引用>照片:
{_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}专辑:
{_id: 1, album_name: "我的专辑 1", album_description: "专辑描述 1", emoji_id: 1},{_id:2，专辑名称:我的专辑2"，专辑描述:专辑描述2"，emoji_id:2}，{_id:3，专辑名称:我的专辑3"，专辑描述:专辑描述3"，emoji_id:3}，{_id:4，专辑名称:我的专辑4"，专辑描述:专辑描述4"，emoji_id:4}，{_id:5，专辑名称:我的专辑 5"，专辑描述:专辑描述 5"，emoji_id:5}表情符号:
{_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"}测试记录分页:
2
假设我在照片集合中添加了另一个标志字段，现在我只想计算标志为 1 的那些照片.
我尝试在查询中的 $lookup 之后立即添加 $match，但它失败了，它不排除 flag=0 的照片，并且在计数器中也没有标记条件.
<块引用>当前输出10 张照片中有 3 张照片设置了标志 0.我们不能考虑标志为 0 的照片.所以预期总数是 7 张照片，但计数返回 10 张照片，尽管我在照片中应用了标志条件.
当前查询:
db.专辑.总计的([{$查找:{来自:照片"，本地字段:_id"，外国字段:专辑ID"，如:照片"}},{$匹配:{照片.标志":1}},{$查找:{来自:表情符号"，localField:"album_emoji",外国字段:_id"，如:表情符号"}},{$项目:{专辑名称:"$专辑名称",专辑描述:$专辑描述"，专辑表情符号:"$emoji.image_name",照片:$照片"，total_photos: {$size: "$photo"}}}]).toArray();<块引用>
预期输出:
<预><代码>[{_id":1，"album_name": "专辑 1","album_description": "专辑描述 1",专辑表情符号":[1.jpg"],"total_photos" : 3,照片" : [{_id":1，专辑编号":1，"photo_description": "说明 1","photo_name": "1.jpg",标志":0，},{_id":2，专辑编号":1，"photo_description": "说明 2","photo_name": "2.jpg",标志":0，},{_id":1，专辑编号":1，"photo_description": "说明 3","photo_name": "3.jpg",标志":0，}]}]
<块引用>
当前输出:
<预><代码>[{_id":1，"album_name": "专辑 1","album_description": "专辑描述 1",专辑表情符号":[1.jpg"],"total_photos" : 5,照片" : [{_id":1，专辑编号":1，"photo_description": "说明 1","photo_name": "1.jpg",标志":1，},{_id":2，专辑编号":1，"photo_description": "说明 2","photo_name": "2.jpg",标志":1，},{_id":3，专辑编号":1，"photo_description": "说明 3","photo_name": "3.jpg",标志":1，},{_id":4，专辑编号":1，"photo_description": "说明 4","photo_name": "4.jpg",标志":0，},{_id":5，专辑编号":1，"photo_description": "说明 5","photo_name": "5.jpg",标志":0，}]}]
在$lookup"之后不能对对象方法使用$match"，因为$lookup"的返回值是数组值.你最好在查找后添加$unwind"函数，然后将其分组.
示例查询
db.专辑.总计的([{$查找:{来自:照片"，本地字段:_id"，外国字段:专辑ID"，如:照片"}},{preserveNullAndEmptyArrays : 真，路径:$照片"},{$匹配:{照片.标志":1}},{$组:{_ID : {id : "$_id",专辑名称:$专辑名称"，专辑描述:$专辑描述"，emoji_id: "$emoji_id"},照片: {$push : "$照片"}}}{$查找:{来自:表情符号"，localField:"_id.album_emoji",外国字段:_id"，如:表情符号"}},{$项目:{专辑名称:"$专辑名称",专辑描述:$专辑描述"，表情符号:"$emoji",照片:$照片"，total_photos: {$size: "$photo"}}}])或者使用$filter".
db.专辑.总计的([{$查找:{来自:照片"，本地字段:_id"，外国字段:专辑ID"，如:照片"}},{$项目:{id : "$_id",专辑名称:$专辑名称"，专辑描述:$专辑描述"，emoji_id: "$emoji_id",照片: {$过滤器:{输入:$照片"，如:photo_field"，条件 : {$eq: ["$$photo_field.flag",1]}}}}},{$查找:{来自:表情符号"，localField:"album_emoji",外国字段:_id"，如:表情符号"}},{$项目:{专辑名称:"$专辑名称",专辑描述:$专辑描述"，表情符号:"$emoji",照片:$照片"，total_photos: {$size: "$photo"}}}])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.
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
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.
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.
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.
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,
}
]
}
]
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.
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".
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"}
}
}
])
这篇关于如何在 mongoDB 中对 $lookup 结果应用条件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
