MongoDB: Querying collection and filtering by array in another collection

Question

I have two collections, users and a record of users activity. My users collection is simple:

    { 
        "_id" : ObjectId("5f0b29c78f491172cfe8b049"), 
        "created" : ISODate("2020-07-12T15:18:31.319+0000"), 
        "lastLogin" : ISODate("2020-07-12T15:18:31.319+0000"), 
        "name" : {
            "first" : "Pedro", 
            "last" : "Perez"
        },
        "city" : "New York"
}

And the records collection:

{ 
    "_id" : ObjectId("5f0fb901b320f5ec21269279"), 
    "userId" : ObjectId("5f0b29c78f491172cfe8b04a"), 
    "record" : [
        {
            "user" : ObjectId("5f0b29c78f491172cfe8b049"), 
            "name" : "Pedro", 
            "type" : "like"
        }, 
        {
            "user" : ObjectId("5f0b29c78f491172cfe8b04b"), 
            "name" : "Rivaldo", 
            "type" : "fold"
        }
    ]
}

In this case, userId is a foreign key for the user document.

I, as the user (Ex _id : 20) I am performing the query. I want to query the users collection and find users with certain fields (city, name, etc), and also, who doesn't contain any registry of me (user _id: 20) in his records array.

Any idea how to do it? I have tried with $lookup and other operators but without luck. Thanks in advance

Valijon Valijon · Accepted Answer · 2020-07-16T06:56:52

If you need to check if any filtered user doesn't exist inside the records field, you can perform this simple aggregation:

db.users.aggregate([
  {
    $match: {
      "name.last": "Perez"
    }
  },
  {
    $lookup: {
      from: "articles",
      localField: "_id",
      foreignField: "records.user",
      as: "users"
    }
  },
  {
    $match: {
      users: {
        $size: 0
      }
    }
  }
  //Remove extra field
  //,{
  //  $unset: "users"
  //}
])

MongoPlayground

MongoDB: Querying collection and filtering by array in another collection

1 Answers