How to search on two separate keys within an array of objects in mongo with mongoose

Question

I need to be able to all doc from a collection with the user id's macthes any of the comment id's in the sub array of docs.

This is the schema:

var postSchema = mongoose.Schema({
    user_id             : String,
    users_full_name     : String,
    username            : String,
    start_time          : String,
    post                : String,
    comments            : [{
        user_id         : String,
        start_time      : Date,
        comment         : String,
        cancelled       : Boolean,
        cancelled_time  : Date
    }]
});

When there was just one doc in the array this worked fine:

modelPost.find({
    "comment.user_id": user_id,
    "comment.cancelled": {$ne:true}
}).sort({"post.start_time": -1}).find(function( err, posts ){
    if( err ){
        console.log( err );
    }

    return callback( posts );
});

But as soon as i put another object in the comments array the query doesn't return anything.

It does work however when i remove the "comment.cancelled": {$ne:true} bit. But I need this bit...

The comments object could have 3 objects in it, however two could belong to one user with one of the two cancelled eg:

user_id             : 123,
users_full_name     : 'Bob Smith',
username            : 'bob.smith',
start_time          : some date,
post                : 'some very interesting post on something cool',
comments            : [{
    user_id         : 654,
    start_time      : some date,
    comment         : 'a nice comment',
    cancelled       : true,
    cancelled_time  : some time,
},{
    user_id         : 654,
    start_time      : some date,
    comment         : 'a nice comment'
},{
    user_id         : 701,
    start_time      : some date,
    comment         : 'a nice comment',
    cancelled       : true,
    cancelled_time  : some time,
},{
    user_id         : 888,
    start_time      : some date,
    comment         : 'another nice comment'
}]

When the document is filled like this the above query yields no results. When i take the "comment.cancelled": {$ne:true} it simple returns all comments belonging to the given user id, included the ones with cancelled = true.

It is as if the "comment.cancelled": {$ne:true} is being cross-ref against the entire array, it finds on object in the array with comment.cancelled=true thus does not return the entire post doc.

With the above example, i if the user id in the query was 654 the query would return the full post. However if the id in the query was 701 it would return no results. And obv. 888 would return the post.

Currently I have to do post query op to filter the results. in the '701' scenario.

so what do you mean by "do post query op to filter the results in the '701' scenario". As long as one of comments has no cancelled field or value is false and matched the given user_id, then this post will be matched? — Allen Chou
– Allen Chou, Commented Jul 26, 2015 at 4:41

Blakes Seven · Accepted Answer · 2015-07-26 05:02:47Z

In order to match multiple conditions on array elements with properties you need the $elemMatch operator. This makes sure that "both" conditions are met on the "same" array element:

modelPost.find({ 
  "comments": { 
    "$elemMatch": {
        "user_id": user_id,
        "cancelled": { "$exists": false }
    }
  }
}.sort({"post.start_time": -1}).exec(function( err, posts ){

})

Also the correct way to match your "cancelled" property that "may not be there is using $exists.

Without $elemMatch, all the "dot notation" form here is really doing is confirming that those conditions were met by "some" element of the array, meaning an element in the array has the required "user_id" and also does not have a "cancelled" property. Since at least one of the array elements is "cancelled" this does not work as a condition.

Think of $elemMatch as it's own query, applied to "each" of the array elements to test if the conditions contained there are true. In the case of your logical "AND" operation, then "both" conditions need to be met on the same array element, it is correctly returns true where this applies.

Also note that the "sort" on the array time property is just applying to the "largest value" ( descending order ) in the array and is sorting documents and not the array entry. If you expect the result to be "sorted" by the array element that matches your condition then you need to work this out with the aggregation framework instead, but that is another question

Wow great answer Blake Seven, thank you very much! Absolutely spot on!

Collectives™ on Stack Overflow

How to search on two separate keys within an array of objects in mongo with mongoose

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related