CODEWITHSUNDEEP
mongodbmongodb-query
Mario Perez
Mario Perez

Reputation: 93

MongoDb: Match value on foreign collection

I have the collection USER:

{
    "_id" : ObjectId("5d64d2bf48dd17387d77d27a"),
    "name" : "John",
    "notifications" : {
        "email" : false,
        "sms" : true
    }
},
{
    "_id" : ObjectId("5da9586911e192081ee1c6be"),
    "name" : "Mary",
    "notifications" : {
        "email" : false,
        "sms" : false
    }
}

And this other collection ALERT:

{
    "_id" : ObjectId("5d54f04dbe5e6275e53a551e"),
    "active" : true,
    "user_id" : ObjectId("5d64d2bf48dd17387d77d27a")
},
{
    "_id" : ObjectId("5d54f04dbe5e6275e53a551f"),
    "active" : false,
    "user_id" : ObjectId("5d64d2bf48dd17387d77d27a")
},
{
    "_id" : ObjectId("5d54f04dbe5e6275e53a552e"),
    "active" : true,
    "user_id" : ObjectId("5da9586911e192081ee1c6be")
},
{
    "_id" : ObjectId("5d54f04dbe5e6275e53a552f"),
    "active" : true,
    "user_id" : ObjectId("5da9586911e192081ee1c6be")
}

I want a MongoDB query that lists the documents on collection ALERT that have property "active" as TRUE and whose matching USER has element "sms" on property "notifications" as TRUE too.

Upvotes: 1

Views: 607

Answers (1)

varman
varman

Reputation: 8894

You can use Uncorelated sub queries in $lookup

  • $match to get the "notifications.sms": true
  • $lookupto join two collections. We are assigning uId = _id from USER collection. Inside the pipeline, we use $match to find the active :true, and _id=uId

here is the script

db.USER.aggregate([
  {
    "$match": {
      "notifications.sms": true
    }
  },
  {
    "$lookup": {
      "from": "ALERT",
      "let": {
        uId: "$_id"
      },
      "pipeline": [
        {
          $match: {
            $and: [
              {
                active: true
              },
              {
                $expr: {
                  $eq: [
                    "$user_id",
                    "$$uId"
                  ]
                }
              }
            ]
          }
        }
      ],
      "as": "joinAlert"
    }
  }
])

Working Mongo playground

Upvotes: 1

Related Questions