将条件匹配的字段添加到嵌套数组 [英] add fields where condition match to nested array
问题描述
我有以下users收藏
[{
"_id" : ObjectId("5afadfdf08a7aa6f1a27d986"),
"firstName" : "bruce",
"friends" : [ ObjectId("5afd1c42af18d985a06ac306"),ObjectId("5afd257daf18d985a06ac6ac") ]
},
{
"_id" : ObjectId("5afbfe21daf4b13ddde07dbe"),
"firstName" : "clerk",
"friends" : [],
}]
并具有friends集合
[{
"_id" : ObjectId("5afd1c42af18d985a06ac306"),
"recipient" : ObjectId("5afaab572c4ec049aeb0bcba"),
"requester" : ObjectId("5afadfdf08a7aa6f1a27d986"),
"status" : 2,
},
{
"_id" : ObjectId("5afd257daf18d985a06ac6ac"),
"recipient" : ObjectId("5afadfdf08a7aa6f1a27d986"),
"requester" : ObjectId("5afbfe21daf4b13ddde07dbe"),
"status" : 1,
}]
假设我有一个用_id: "5afaab572c4ec049aeb0bcba"登录的用户，并且该_id与friends的recipient相匹配
现在我必须添加一个字段friendsStatus，其中包含friends集合中的status ...如果与数组中的任何recipient不匹配，则其状态应为0
所以当我得到所有用户时，我的输出应该是
[{
"_id" : ObjectId("5afadfdf08a7aa6f1a27d986"),
"firstName" : "bruce",
"friends" : [ ObjectId("5afd1c42af18d985a06ac306") ],
"friendStatus": 2
},
{
"_id" : ObjectId("5afbfe21daf4b13ddde07dbe"),
"firstName" : "clerk",
"friends" : [],
"friendStatus": 0
}]
提前谢谢！
如果您拥有MongoDB 3.6，则可以使用 对于早期版本，理想的是实际使用 这里的最优形式有一个区别" ，因为管道优化实际上并没有汇总" 由于 因此， 由于该 BSON限制，这并不是真正明智的选择，但也有将 无论哪种情况，我们基本上都在联接上添加了附加条件"，不仅在直接相关的字段上，而且还附加了对 不太确定您对 所有输出都相同: I have following and have suppose I have an user logged in with Now I have to add a field So when I get all users then my output should be Thanks in advance!!! If you have MongoDB 3.6 then you can use For earlier versions, it's ideal to actually use There is "one difference" from the most optimal form here in that the pipeline optimization does not actually "roll-up" the Because of the So the only purpose of And not really advisable because of that BSON Limit, but there is also applying In either case we're basically adding the "additional condition" to the join being not just on the directly related field but also with the additional constraint of the queried Not really sure what you are expecting for The governing condition in each case is All output the same thing:
这篇关于将条件匹配的字段添加到嵌套数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！User.aggregate([
{ "$lookup": {
"from": Friend.collection.name,
"let": { "friends": "$friends" },
"pipeline": [
{ "$match": {
"recipient": ObjectId("5afaab572c4ec049aeb0bcba"),
"$expr": { "$in": [ "$_id", "$$friends" ] }
}},
{ "$project": { "status": 1 } }
],
"as": "friends"
}},
{ "$addFields": {
"friends": {
"$map": {
"input": "$friends",
"in": "$$this._id"
}
},
"friendsStatus": {
"$ifNull": [ { "$min": "$friends.status" }, 0 ]
}
}}
])
$unwind ，以确保您不违反 BSON限制:
User.aggregate([
{ "$lookup": {
"from": Friend.collection.name,
"localField": "friends",
"foreignField": "_id",
"as": "friends"
}},
{ "$unwind": { "path": "$friends", "preserveNullAndEmptyArrays": true } },
{ "$match": {
"$or": [
{ "friends.recipient": ObjectId("5afaab572c4ec049aeb0bcba") },
{ "friends": null }
]
}},
{ "$group": {
"_id": "$_id",
"firstName": { "$first": "$firstName" },
"friends": { "$push": "$friends._id" },
"friendsStatus": {
"$min": {
"$ifNull": ["$friends.status",0]
}
}
}}
])
preserveNullAndEmptyArrays选项为true，因此执行完全优化" 操作，该操作实际上将条件应用于外部集合之前" 返回不会发生.unwinding的唯一目的纯粹是为了避免通常从 $match 的其他条件这个阶段.默认的 $unwind (不带选项)假定为false保存并添加matching条件来执行此操作.当然，这将导致没有外国匹配项的文档被排除.$filter 应用于所得的 $lookup :User.aggregate([
{ "$lookup": {
"from": Friend.collection.name,
"localField": "friends",
"foreignField": "_id",
"as": "friends"
}},
{ "$addFields": {
"friends": {
"$map": {
"input": {
"$filter": {
"input": "$friends",
"cond": {
"$eq": [
"$$this.recipient",
ObjectId("5afaab572c4ec049aeb0bcba")
]
}
}
},
"in": "$$this._id"
}
},
"friendsStatus": {
"$ifNull": [
{ "$min": {
"$map": {
"input": {
"$filter": {
"input": "$friends",
"cond": {
"$eq": [
"$$this.recipient",
ObjectId("5afaab572c4ec049aeb0bcba")
]
}
}
},
"in": "$$this.status"
}
}},
0
]
}
}}
])
"recipient"的查询的ObjectId值的约束."friendsStatus"的期望是什么，因为结果是一个数组，并且可能有多个(据我所知)，因此仅应用 $ifNull 在"friends"输出数组中没有要提取的内容的情况下应用该方法，然后只需返回0的结果即可.{
"_id" : ObjectId("5afadfdf08a7aa6f1a27d986"),
"firstName" : "bruce",
"friends" : [
ObjectId("5afd1c42af18d985a06ac306")
],
"friendsStatus" : 2
}
{
"_id" : ObjectId("5afbfe21daf4b13ddde07dbe"),
"firstName" : "clerk",
"friends" : [ ],
"friendsStatus" : 0
}
users collection[{
"_id" : ObjectId("5afadfdf08a7aa6f1a27d986"),
"firstName" : "bruce",
"friends" : [ ObjectId("5afd1c42af18d985a06ac306"),ObjectId("5afd257daf18d985a06ac6ac") ]
},
{
"_id" : ObjectId("5afbfe21daf4b13ddde07dbe"),
"firstName" : "clerk",
"friends" : [],
}]
friends collection[{
"_id" : ObjectId("5afd1c42af18d985a06ac306"),
"recipient" : ObjectId("5afaab572c4ec049aeb0bcba"),
"requester" : ObjectId("5afadfdf08a7aa6f1a27d986"),
"status" : 2,
},
{
"_id" : ObjectId("5afd257daf18d985a06ac6ac"),
"recipient" : ObjectId("5afadfdf08a7aa6f1a27d986"),
"requester" : ObjectId("5afbfe21daf4b13ddde07dbe"),
"status" : 1,
}]
_id: "5afaab572c4ec049aeb0bcba" and this _id matches the recipient of the friendsfriendsStatus which contains the status from friends collection... And if does not matches the any recipient from the array then its status should be 0[{
"_id" : ObjectId("5afadfdf08a7aa6f1a27d986"),
"firstName" : "bruce",
"friends" : [ ObjectId("5afd1c42af18d985a06ac306") ],
"friendStatus": 2
},
{
"_id" : ObjectId("5afbfe21daf4b13ddde07dbe"),
"firstName" : "clerk",
"friends" : [],
"friendStatus": 0
}]
$lookup with a "sub-pipeline"User.aggregate([
{ "$lookup": {
"from": Friend.collection.name,
"let": { "friends": "$friends" },
"pipeline": [
{ "$match": {
"recipient": ObjectId("5afaab572c4ec049aeb0bcba"),
"$expr": { "$in": [ "$_id", "$$friends" ] }
}},
{ "$project": { "status": 1 } }
],
"as": "friends"
}},
{ "$addFields": {
"friends": {
"$map": {
"input": "$friends",
"in": "$$this._id"
}
},
"friendsStatus": {
"$ifNull": [ { "$min": "$friends.status" }, 0 ]
}
}}
])
$unwind in order to ensure you don't breach the BSON Limit:User.aggregate([
{ "$lookup": {
"from": Friend.collection.name,
"localField": "friends",
"foreignField": "_id",
"as": "friends"
}},
{ "$unwind": { "path": "$friends", "preserveNullAndEmptyArrays": true } },
{ "$match": {
"$or": [
{ "friends.recipient": ObjectId("5afaab572c4ec049aeb0bcba") },
{ "friends": null }
]
}},
{ "$group": {
"_id": "$_id",
"firstName": { "$first": "$firstName" },
"friends": { "$push": "$friends._id" },
"friendsStatus": {
"$min": {
"$ifNull": ["$friends.status",0]
}
}
}}
])
$match condition into the $lookup itself:{
"$lookup" : {
"from" : "friends",
"as" : "friends",
"localField" : "friends",
"foreignField" : "_id",
"unwinding" : {
"preserveNullAndEmptyArrays" : true
}
}
},
{
"$match" : { // <-- outside will preserved array
preserveNullAndEmptyArrays option being true then the "fully optimized" action where the condition would actually be applied to the foreign collection "before" results are returned does not happen.unwinding here is purely to avoid what would normally be a target "array" from the $lookup result causing the parent document to grow beyond the BSON Limit. Additional conditions of the $match are then applied "after" this stage. The default $unwind without the option presumes false for the preservation and a matching condition is added instead to do this. This of course would result in the documents with no foreign matches being excluded.$filter to the resulting array of $lookup:User.aggregate([
{ "$lookup": {
"from": Friend.collection.name,
"localField": "friends",
"foreignField": "_id",
"as": "friends"
}},
{ "$addFields": {
"friends": {
"$map": {
"input": {
"$filter": {
"input": "$friends",
"cond": {
"$eq": [
"$$this.recipient",
ObjectId("5afaab572c4ec049aeb0bcba")
]
}
}
},
"in": "$$this._id"
}
},
"friendsStatus": {
"$ifNull": [
{ "$min": {
"$map": {
"input": {
"$filter": {
"input": "$friends",
"cond": {
"$eq": [
"$$this.recipient",
ObjectId("5afaab572c4ec049aeb0bcba")
]
}
}
},
"in": "$$this.status"
}
}},
0
]
}
}}
])
ObjectId value for "recipient"."friendsStatus" since the result is an array and there can possibly be more than one ( as far as I know ) and therefore just applying $min here to extract one value from the array in either case.$ifNull which is applied where there isn't anything in the "friends" output array to extract from and then you simply return the result of 0 where that is the case.{
"_id" : ObjectId("5afadfdf08a7aa6f1a27d986"),
"firstName" : "bruce",
"friends" : [
ObjectId("5afd1c42af18d985a06ac306")
],
"friendsStatus" : 2
}
{
"_id" : ObjectId("5afbfe21daf4b13ddde07dbe"),
"firstName" : "clerk",
"friends" : [ ],
"friendsStatus" : 0
}
