Setting condition in aggregate pipeline not working in mongodb

Question

I have got two collections as follows:

Users:

_id                                     |   email
ObjectId("5ee493b0989d0f271cdc41c1")    |   joulie@gmail.com
ObjectId("5ee493b0989d0f271cdc41c3")    |   newelle@gmail.com
ObjectId("5ee493b0989d0f271cdc41c5")    |   john@gmail.com
ObjectId("5ee493b0989d0f271cdc41c7")    |   larry@gmail.com

Members:

group_id                                | user_id  
ObjectId("5ee5e346fae4a21e28a81d91")    | ObjectId("5ee493b0989d0f271cdc41c1")
ObjectId("5ee5e346fae4a21e28a81d92")    | ObjectId("5ee493b0989d0f271cdc41c5")

I'm trying to search for email in Users collection and check if that the user associated with that email belongs to a specific group by comparing the supplied group_id parameter with group_id in Members collection.

For example, If the passed search parameters are: email: joulie, group_id: 5ee5e346fae4a21e28a81d91

Expected Output:
    [
        { "email":"joulie@gmail.com", 
          "member_info": [
            {"group_id":"5ee5e346fae4a21e28a81d91", "user_id":"5ee493b0989d0f271cdc41c1"}
            ]
        }
   ]

For example, If the passed search parameters are:email: newelle, group_id: 5ee5e346fae4a21e28a81d91

Expected Output:
        [
            { "email":"newelle@gmail.com", 
              "member_info": []
            }
       ]

I hope the question makes sense.

I have followed a similar solution from this first one and this second one threads, but none of these seems to work for me.

Here's what I've tried so far:

db.users.aggregate([
      { "$match" : {email: { $regex: email +'.*'}}},
      {
        $lookup: {
          from: "members",
          let: { user_id: "$_id" },
          pipeline: [
            { $match: {
              $expr: { $and: 
                [
                  { $in: [ group_id, "$group_id" ] },
                  { $eq: [ "$$user_id", "$user_id" ] }
                ] 
              }
            }}
          ],
          as: "members"
        }
      }
])

Any help or guideline will be really appreciated. Thanks in advance!

What does the output that you're currently getting look like? — Montgomery Watts, Jul 12 '20 at 03:19
Neither the group_id you are passing in, nor the group_id in the document are arrays, so the `$in` operator is probably failing. — Joe, Jul 12 '20 at 05:16
You're right @Joe I didn't realize that. But, after replacing $in with $eq still gives empty array [] for members field when there's a matching row. Did I miss something? — nottherealironman, Jul 12 '20 at 07:10
It works for me: https://mongoplayground.net/p/Ta8zpo60SDT, what are you doing differently? — Joe, Jul 12 '20 at 07:13
Actually, I was simply comparing **group_id** instead of **ObjectId(group_id)**. Thank you for your help @Joe. It's working now — nottherealironman, Jul 12 '20 at 07:36

score 0 · Accepted Answer · answered Jul 12 '20 at 08:02

I have followed the instructions pointed out by @Joe in the comment section and it worked for me. If anyone is still stuck in a similar issue then you can use the below-updated query:

db.users.aggregate([
      {
        "$match": {
          email: {
            $regex: email +'.*'
          }
        }
      },
      {
        $lookup: {
          from: "members",
          let: {
            user_id: "$_id"
          },
          pipeline: [
            {
              $match: {
                $expr: {
                  $and: [
                    {
                      $eq: [
                        ObjectId(group_id),
                        "$group_id"
                      ]
                    },
                    {
                      $eq: [
                        "$$user_id",
                        "$user_id"
                      ]
                    }
                  ]
                }
              }
            }
          ],
          as: "members"
        }
      },
]);

The first issue was I used $in operator inside $expr when I was simply comparing a field holding Id instead of an array. I changed it to $eq.

Secondly, I was simply comparing group_id variable (of type string) inside expr. Therefore, I replaced it with ObjectId(group_id) and it worked for me. Also, don't forget to import ObjectId at the beginning (i.e. var ObjectId = require('mongodb').ObjectID)

Setting condition in aggregate pipeline not working in mongodb

1 Answers1