Mongoose aggregate query to filter value from the ref collection-CodePudding

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?

CodePudding user response：

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"}}])

CodePudding user response：

If I've understood correctly you can use this aggregation query:

This query uses a $lookup with a pipeline where the result is given by a match with an $in. So, the join will return the values where the event-activity.activity is in the array event.eventActivity.

db.event.aggregate([
  {
    "$lookup": {
      "from": "event-activity",
      "as": "activities",
      "let": {
        "ea": "$eventActivity"
      },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$in": [
                "$activity",
                "$$ea"
              ]
            }
          }
        }
      ]
    }
  }
])

Example here where I've used integers as activity to see easier the join.