0

I have some Product documents that each contain a list of ProductVariation sub-documents. I need to find all the Product docs where ALL their child ProductVariation docs have zero quantity.

Schemas look like this:

var Product = new mongoose.Schema({
    name: String,
    variations: [ProductVariation]
});

var ProductVariation = new mongoose.Schema({
    type: String,
    quantity: Number,
    price: Number
});

I am a little new to mongodb, so even sure where to start here.

Adam Harte
  • 10,369
  • 7
  • 52
  • 85

1 Answers1

1

Try using $not wrapped around { "$gt" : 0 }: 

> db.products.find()
{ "_id" : ObjectId("5b7cae558ff28edda6ba4a67"), "name" : "widget", "variations" : [ { "type" : "color", "quantity" : 0, "price" : 10 }, { "type" : "size", "quantity" : 0, "price" : 5 } ] }
{ "_id" : ObjectId("5b7cae678ff28edda6ba4a68"), "name" : "foo", "variations" : [ { "type" : "color", "quantity" : 2, "price" : 15 }, { "type" : "size", "quantity" : 0, "price" : 5 } ] }
{ "_id" : ObjectId("5b7cae7f8ff28edda6ba4a69"), "name" : "bar", "variations" : [ { "type" : "color", "quantity" : 0, "price" : 15 }, { "type" : "size", "quantity" : 1, "price" : 5 } ] }

> db.products.find({"variations.quantity": { "$not" : { "$gt" : 0 } } })
{ "_id" : ObjectId("5b7cae558ff28edda6ba4a67"), "name" : "widget", "variations" : [ { "type" : "color", "quantity" : 0, "price" : 10 }, { "type" : "size", "quantity" : 0, "price" : 5 } ] }

It can also take advantage of an index on { "variations.quantity" : 1 }.

Adam Harrison
  • 3,323
  • 2
  • 17
  • 25
  • I copied your data, and `db.products.find({"variations.quantity": { "$not" : { "$gt" : 0 } } })` seems to be returning back all three products for me. Maybe because they all contain Variations with a zero quantity, rather than returning products that ONLY have zero quantity variations – Adam Harte Aug 22 '18 at 02:30
  • Hmm, actually, when I duplicate your data exactly it seems to work, but my data is a little different, so that is my fault. Turns out I am not using sub-documents, but rather linking to other documents (not sure what the correct term for this is). But my Product schema looks more like: `{name:String, variations:[{type:Schema.ObjectId, ref:'ProductVariation'}]}` – Adam Harte Aug 22 '18 at 02:41
  • Looks like you're using the Populate feature of Mongoose (https://mongoosejs.com/docs/populate.html). I'll play around with it and see what I can come up with. – Adam Harrison Aug 22 '18 at 03:55