Find documents matching ObjectIDs in a foreign array

Question

I have a collection Users:

{
  _id: "5cds8f8rfdshfd"
  name: "Ted"
  attending: [ObjectId("2cd9fjdkfsld")]
}

I have another collection Events:

{
  _id: "2cd9fjdkfsld"
  title: "Some Event Attended"
},
{
  _id: "34dshfj29jg"
  title: "Some Event NOT Attended"
}

I would like to return a list of all events being attended by a given user. However, I need to do this query from the Events collection as this is part of a larger query.

I have gone through the following questions:

$lookup on ObjectId's in an array - This question has the array as a local field; mine is foreign
MongoDB lookup when foreign field is an array of objects - The array is of objects themselves
MongoDB lookup when foreign field is an array

I have tried various ways of modifying the above answers to fit my situation but have been unsuccessful. The second answer from the third question gets me closest but I would like to filter out unmatching results rather than have them returned with a value of 0.

My desired output:

[
  {
    _id: "2cd9fjdkfsld"
    title: "Some Event Attended"
  },
]

What does your desired output document look like? – dnickless Feb 21 '19 at 19:33 — dnickless, Feb 21 '19 at 19:33

score 1 · Accepted Answer · answered Feb 21 '19 at 19:45

1

One option would be like this:

db.getCollection('Events').aggregate({
    $lookup: // join
    {
        from: "Users", // on Users collection
        let: { eId: "$_id" }, // keep a local variable "eId" that points to the currently looked at event's "_id"
        pipeline: [{
            $match: { // filter where
                "_id": ObjectId("5c6efc937ef75175b2b8e7a4"), // a specific user
                $expr: { $in: [ "$$eId", "$attending" ] } // attends the event we're looking at
            }
        }],
        as: "users" // push all matched users into the "users" array
    }
}, {
    $match: { // remove events that the user does not attend
        "users": { $ne: [] }
    }
})

You could obviously get rid of the users field by adding another projection if needed.

answered Feb 21 '19 at 19:45

dnickless

10,733
1
19
34

Which title? The event title? You could simply add another pipeline stage `$project: { "title": 0 }` – dnickless Feb 21 '19 at 20:31
Sorry, I was testing on the remote server. Yes, the event title...adding a projection to the pipeline does not do anything. Do I need to unwind or something first? – Matt Feb 21 '19 at 20:47
What does your result document look like? If it has a `title` field on the root level and you add the projection stage mentioned above at the end of your pipeline then this will definitely eliminate the field from the output. – dnickless Feb 21 '19 at 20:52
Oh, it was that; I thought I'd tried that but I guess I had still been on remote. Thanks. – Matt Feb 21 '19 at 21:01

Find documents matching ObjectIDs in a foreign array

1 Answers1