25

I'm having some trouble querying a document by values matching inside the document after population by mongoose.

My schemas are something like this:

var EmailSchema = new mongoose.Schema({
  type: String
});

var UserSchema = new mongoose.Schema({
  name: String,
  email: [{type:Schema.Types.ObjectId, ref:'Email'}]
});

I would like to have all users which have a email with the type = "Gmail" for example.

The following query returns empty results:

Users.find({'email.type':'Gmail').populate('email').exec( function(err, users)
    {
      res.json(users);
    });

I have had to resort to filtering the results in JS like this: 

users = users.filter(function(user)
        {
          for (var index = 0; index < user.email.length; index++) {
            var email = user.email[index];
            if(email.type === "Gmail")
            {
              return true;
            }
          }
          return false;
        });

Is there any way to query something like this straight from mongoose?

monokh
  • 1,970
  • 3
  • 22
  • 31

3 Answers3

37

@Jason Cust explained it pretty well already - in this situation often the best solution is to alter the schema to prevent querying Users by properties of documents stored in separate collection.

Here's the best solution I can think of that will not force you to do that, though (because you said in the comment that you can't).

Users.find().populate({
  path: 'email',
  match: {
    type: 'Gmail'
  }
}).exec(function(err, users) {
  users = users.filter(function(user) {
    return user.email; // return only users with email matching 'type: "Gmail"' query
  });
});

What we're doing here is populating only emails matching additional query (match option in .populate() call) - otherwise email field in Users documents will be set to null.

All that's left is .filter on returned users array, like in your original question - only with much simpler, very generic check. As you can see - either the email is there or it isn't.

bardzusny
  • 3,788
  • 7
  • 30
  • 30
  • 10
    I would just note a bit of concern if `users` is a large collection with this approach. – Jason Cust Jul 13 '15 at 18:43
  • 1
    @JasonCust: indeed! That's probably why still the best option would be to alter the schema properly (which can be not-so-fun if, for example, you already have working application and some data in the DB). I also thought about alternative solution: 1. Fetch `_id`s of properly filtered e-mails ( `Emails.find()` ), 2: Load only users with e-mails matching those ( `Users.find()` with `$in` operator). I can post example of this solution too if you're interested. On one hand, no loading of all users documents. On the other hand, it seems even more hack-ish/workaround-ish. – bardzusny Jul 13 '15 at 20:07
  • 1
    But this will not work with pagination. I am also stuck in this situation where i have to paginate the data as well. So what happens now is on page 1 I am getting empty array but one page 2 i am getting filtered data. Can anyone help in this?? – Tarun Chauhan May 30 '20 at 04:55
  • I wasted too many hours too access fields of populated path. Although I can log populated field "User" correctly, If I try to access field in it, "user.firstName" , directly, it throws error. Property 'firstName' does not exist on type 'string'. Because its a reference type on both TS interface and model. //Logs correctly `{ user:{ firstName:"Hasan", lastName:"Mehmet" } }` I'm just tired and gonna filter the result array as you do. – yalcinozer Jun 08 '21 at 12:07
10

Mongoose's populate function doesn't execute directly in Mongo. Instead after the initial find query returns a set a documents, populate will create an array of individual find queries on the referenced collection to execute and then merge the results back into the original documents. So essentially your find query is attempting to use a property of the referenced document (which hasn't been fetched yet and therefore is undefined) to filter the original result set.

In this use case it seems more appropriate to store emails as a subdocument array rather than a separate collection to achieve what you want to do. Also, as a general document store design pattern this is one of the use cases that makes sense to store an array as a subdocument: limited size and very few modifications.

Updating your schema to:

var EmailSchema = new mongoose.Schema({
  type: String
});

var UserSchema = new mongoose.Schema({
  name: String,
  email: [EmailSchema]
});

Then the following query should work:

Users.find({'email.type':'Gmail').exec(function(err, users) {
  res.json(users);
});
Jason Cust
  • 10,743
  • 2
  • 33
  • 45
  • 2
    I appreciate the explanation about how populate works, that's very insightful. The example I gave was a simplification, my actual use case does require referenced documents so I really have to find a solution to this without changing the model. – monokh Jul 11 '15 at 17:45
  • 3
    @Rubelet Currently there is no concept of a `join` in MongoDB so MongooseJS won't be able to do more than what it is currently designed to do in this case. I think a concern with attempting to query the collection this way is that if it's a large collection the population would be a costly operation to do. If this is how the schema must be I would look into using a stream and filtering to avoid causing a bottleneck during the population. – Jason Cust Jul 11 '15 at 19:54
0

I couldn't find any other solution other than using Aggregate. It will be more troublesome, but we will use Lookup.

{
       $lookup:
         {
           from: <collection to join>,
           localField: <field from the input documents>,
           foreignField: <field from the documents of the "from" collection>,
           as: <output array field>
         }
}
undefined string
  • 1
  • 1