Mongoose - How to query subdocument by property value

Question

I'm trying to get an array of unpaid orders. The order subdocument has a property isPaid which defines whether or not the order has been paid.

In my view, I want to display only orders which have not been paid.

Here is my schema:

var order = new Schema({
   content: [{
        product: {type: String, required: true},
        quantity: {type: Number},
        vatRate: {type: Number, required: true},
        price: {type: Number}
    }],
    isPaid: {type: Boolean, default: false},
    number: {type: Number}
});

var clientSchema = new Schema({
[...]
information: {
    code: {type: String, required: true},
    name: {type: String, required: true}
},
orders: [order],
[...]
});

I started with, but without success

clientModel.find(
    {
       "information.code": clientCode, 
       "orders.isPaid": false
    }, function (err, client) { ... });

Then, I've done many tries, with $all, $elemMatch without success. Most of the time, it returns all the orders, paid or unpaid. I don't know why. I need some help, please :)

Answer 1

One approach you could take is using the aggregation framework to get the desired array. Consider the following pipeline which first uses the $match operator to filter the documents that will go to the next stage, the $project step. This only produces the desired orders array which would be filtered using $filter and the condition to apply the filter uses the comparison operator $eq on the subdocument isPaid.

In the end the pipeline would look like this:

const pipeline = [
    { '$match': {
        'information.code': clientCode, 
        'orders.isPaid': false
    } },
    { '$project': {
        'orders': {
            '$filter': {
                'input': '$orders',
                'cond': {
                    '$eq': ['$$this.isPaid', false]
                }
            }
        }
    } }
]

or if the MongoDB server version does not support $filter (older drivers), the next step after the initial match would be $unwind.

This step deconstructs the orders array field from the input documents to output a document for each element. Each output document is the input document with the value of the array field replaced by the element.

The next step uses the $match operator to then do a further filtering on the deconstructed subdocuments which will then be grouped (using $group) by the identifier _id expression and applies the accumulator expression $push (on the orders subdocument) to each group that returns the desired array.

const pipeline = [
    { '$match': {
        'information.code': clientCode, 
        'orders.isPaid': false
    } },
    { '$unwind': '$orders' },
    { '$match': {       
        'orders.isPaid': false
    } },
    { '$group': {
        '_id': '$_id',
        'orders': {
            '$push': '$orders'
        }
    } }
]

clientModel.aggregate(pipeline).exec(function (err, res){
    if (err) return handleError(err);
    console.log(res); // [ { orders: [...] } ]
});

Or using the aggregation pipeline builder:

clientModel.aggregate()
    .match({'information.code': clientCode, 'orders.isPaid': false})
    .project({
        'orders': {
            '$filter': {
                'input': '$orders',
                'cond': {
                    '$eq': ['$$this.isPaid', false]
                }
            }
        }
    })
    .exec(function (err, res) {
        if (err) return handleError(err);
        console.log(res); // [ { orders: [...] } ]
    });

or for older versions

clientModel.aggregate()
    .match({'information.code': clientCode, 'orders.isPaid': false})
    .unwind('orders')
    .match({'orders.isPaid': false })
    .group({'_id': '$_id', 'orders': { '$push': '$orders' } })
    .select('-_id orders')
    .exec(function (err, res) {
        if (err) return handleError(err);
        console.log(res); // [ { orders: [...] } ]
    });

Answer 2

.populate() allows you to specify a match query. Example from another SO answer:

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
  });
});```