Mongoose aggregate query to filter value from the ref collection

Question

I have 2 collections as follow:

event

{
    "_id" : ObjectId("61f272dd1fac703fec69105a"),
    "eventActivity" : [  
        ObjectId("61f76703196ea94bd43fa92e"),
    ]
}

event-activity

{
    "_id" : ObjectId("61f76703196ea94bd43fa92e"),
    "activity" : ObjectId("61f2a69bfe99e07db083de50"),
}

Based on the collections above, event has eventActivity field which refers to event-activity collection. I'm trying to filter the event by the value of event-activity.activity.

So if for example my filtration selection has activity in an array ['61d6b2060d6fe32d9853ad40', '61f2a69bfe99e07db083de50'], it will return the event. If the filtration selection has activity id ['61d6b2060d6fe32d9853ad40'], it should not return any event as there is no event with that activity id from event-activity

I can't really understand how the aggregate lookup work but I tried this and it doesn't work.

event.aggregate([
    {"$lookup":{
        "from":"event-activity",
        "localField":"activity",
        "foreignField":"_id",
        "as":"event-activity"
     }},
     {
         "$match":{
             "event-activity.activity":{
                 "$in":["61d6b2060d6fe32d9853ad40","61f2a69bfe99e07db083de50"]
              }
          }
      }
])

I referred to the manual here

Or can it be done by find() instead?

Answer 1

Query

• you can use lookup with pipeline and put the match inside
• if the lookup result is empty you can remove or keep the document based on your needs, with something like this {"$match":{"$expr":{"$ne":["$activities", []]}}}

Test code here

event.aggregate(
[{"$lookup":
  {"from":"event-activity",
   "localField":"eventActivity",
   "foreignField":"_id",
   "pipeline":
   [{"$match":
     {"activity":
      {"$in":
       [ObjectId("61d6b2060d6fe32d9853ad40"),
        ObjectId("61f2a69bfe99e07db083de50")]}}}],
   "as":"activities"}}])