How to use some sort of $sum inside $match

Working with Data
, ,
1

Hello there, so what i need to do is match the sum of the quantity inside the documents
this is the schema of the document

{
	"_id" : ObjectId("63baa42ae4d7dbffcea784a1"),
	"items" : [
		{
			"product" : ObjectId("637f223fe8d959c7a381ea6c"),
			"variant" : "637f223fe8d959c7a381ea70",
			"defaultPrice" : 103,
			"quantity" : 5,
		},
		{
			"product" : ObjectId("637f223fe8d959c7a381ea6c"),
			"variant" : "637f223fe8d959c7a381ea6d",
			"defaultPrice" : 3,
			"quantity" : 6,
		},
		{
			"product" : ObjectId("63808202b212793c115b8bed"),
			"variant" : "",
			"defaultPrice" : 2,
			"quantity" : 10,
		}
	]
}

And for exp i need to filter all documents that has 21 as the sum of the “items.quantity”.

What to do here ?
Thanks in advance

2

Hi,

You can execute this find request.

db.collection.find({$expr: {
    $gte: [{$sum: "$items.quantity"}, 21]
}})

you can use $expr to use aggregation expression in find command

this allows you to use the $sum expression with a field in the document as a parameter

2 Likes
3

To go further,

My the previous request implies a COLLSCAN.
If you plan to executed that kind of request regularly on a large volume of data.

I recommand you to do something to improve your request respond time.

Create an index on a total field.

You can precalculated your total field.
Or if you use mongodb atlas use trigger, to calculate the total field on update/insert

Or by using changestream on insert/update to update the total field.

Then you can simply run a request like

db.collection.find({total: {$gte: 21}});

Then you exploit your newly created index and have a faster respond time.