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 · Accepted Answer · 2020-07-16 06:56:52Z

2

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

answered Jul 16, 2020 at 6:56

Valijon

13.2k4 gold badges40 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

carrasc0 Over a year ago

Hello @Valijon. Yes more or less is the idea, but there is a detail. I want to check that a certain user (passed by parameter, by _id) doesn't exist in the record array, not the current user of the lookup. So, if that certain user doesn't exist in the array and the match fields are correct, then I return the current user. Probably I did not explain myself correctly in the question.

Valijon Over a year ago

@carrasc0 it would be nice if you explain that detail with example, because the current user doesn't exist there I've interpreted as filtered user

carrasc0 Over a year ago

No problem. This is the case: I, as the user 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 20) in his records array. When I say "current user" I mean the one who is performing the query, that's the one I need to check doesn’t exist in the records array of the filtered user. My apologies for the bad explanation.

Valijon Over a year ago

@carrasc0 Edit your post please

Stack_monster · Accepted Answer · 2023-02-03 07:03:20Z

1

Okay, so first of all, create a foreign key that is easier to read so that even while you copy-paste it, it isn't so hard.

db.users.aggregate([{$lookup:{from:"records", localField:"_id", foreignField:"userId", as:"users"}}])

function will work on this. It seems that you haven't noticed that your "userId" of the records collection does not match with the "_id" field of users collection.

Look at your _id : ObjectId("5f0b29c78f491172cfe8b049")

Look at your userId : ObjectId("5f0b29c78f491172cfe8b04a")

I hope you noticed that the 049 has been replaced with 04a

Check it, you'll find your answer :)

edited Feb 3, 2023 at 7:03

answered Dec 1, 2022 at 5:58

Stack_monster

113 bronze badges

Collectives™ on Stack Overflow

MongoDB: Querying collection and filtering by array in another collection

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related