add fields where condition match to nested array

问题:

I have following users collection

[{
    "_id" : ObjectId("5afadfdf08a7aa6f1a27d986"),
    "firstName" : "bruce",
    "friends" : [ ObjectId("5afd1c42af18d985a06ac306"),ObjectId("5afd257daf18d985a06ac6ac") ]
},
{
    "_id" : ObjectId("5afbfe21daf4b13ddde07dbe"),
    "firstName" : "clerk",
    "friends" : [],
}]

and have friends collection

[{
    "_id" : ObjectId("5afd1c42af18d985a06ac306"),
    "recipient" : ObjectId("5afaab572c4ec049aeb0bcba"),
    "requester" : ObjectId("5afadfdf08a7aa6f1a27d986"),
    "status" : 2,
},
{
    "_id" : ObjectId("5afd257daf18d985a06ac6ac"),
    "recipient" : ObjectId("5afadfdf08a7aa6f1a27d986"),
    "requester" : ObjectId("5afbfe21daf4b13ddde07dbe"),
    "status" : 1,
}]

suppose I have an user logged in with _id: "5afaab572c4ec049aeb0bcba" and this _id matches the recipient of the friends

Now I have to add a field friendsStatus which contains the status from friends collection... And if does not matches the any recipient from the array then its status should be 0

So when I get all users then my output should be

[{
        "_id" : ObjectId("5afadfdf08a7aa6f1a27d986"),
        "firstName" : "bruce",
        "friends" : [ ObjectId("5afd1c42af18d985a06ac306") ],
        "friendStatus": 2
},
{
        "_id" : ObjectId("5afbfe21daf4b13ddde07dbe"),
        "firstName" : "clerk",
        "friends" : [],
        "friendStatus": 0
}]

Thanks in advance!!!

回答1:

If you have MongoDB 3.6 then you can use $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 ]
    }
  }}
])

For earlier versions, it's ideal to actually use $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]
      }
    }
  }}
])

There is "one difference" from the most optimal form here in that the pipeline optimization does not actually "roll-up" the $match condition into the $lookup itself:

{
  "$lookup" : {
    "from" : "friends",
    "as" : "friends",
    "localField" : "friends",
    "foreignField" : "_id",
    "unwinding" : {
      "preserveNullAndEmptyArrays" : true
    }
  }
},
{
  "$match" : {   // <-- outside will preserved array

Because of the 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.

So the only purpose of 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.

And not really advisable because of that BSON Limit, but there is also applying $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
      ]
    }
  }}
])

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 ObjectId value for "recipient".

Not really sure what you are expecting for "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.

The governing condition in each case is $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.

All output the same thing:

{
        "_id" : ObjectId("5afadfdf08a7aa6f1a27d986"),
        "firstName" : "bruce",
        "friends" : [
                ObjectId("5afd1c42af18d985a06ac306")
        ],
        "friendsStatus" : 2
}
{
        "_id" : ObjectId("5afbfe21daf4b13ddde07dbe"),
        "firstName" : "clerk",
        "friends" : [ ],
        "friendsStatus" : 0
}