Reputation: 247
I have a Shop collection with a document like this:
{
_id:'xyz'
sales:[
{
_id:'aaa'
productId:'ppp',
quantity: 3
},
{
_id:'bbb'
productId:'qqq'
quantity: 4
},
]
}
When i query an element in sales array (_id:'aaa') i need to get its quantity and the sum of quantity in the whole sales array ie quantity:3, totalQuantity: 7
Same wise, when i query another element (_id:'bbb') i need to get quantity: 4, totalQuantity: 7
I have tried using aggregate, as follows:
Shop.aggregate([
{$match: {'_id': ObjectId('xyz'),'sales._id':ObjectId('aaa')}},
{$group: {
_id: "$_id",
totalQuantity: {
"$sum": {
"$sum": "$sales.quantity"
}
}
}
])
But this doesn't work.
How can I achieve this?
Upvotes: 0
Views: 125
Reputation: 17898
We can get the totalQuantity using $sum.
We can use $filter to find the required item, and access the quantity.
db.collection.aggregate([
{
$match: {
_id: "xyz"
}
},
{
$project: {
totalQuantity: {
$sum: "$sales.quantity"
},
quantity: {
$filter: {
input: "$sales",
cond: {
$eq: [
"$$this._id",
"aaa"
]
}
}
}
}
},
{
$addFields: {
quantity: {
$sum: "$quantity.quantity"
}
}
}
])
This will give the following result for the "aaa" value:
[
{
"_id": "xyz",
"quantity": 3,
"totalQuantity": 7
}
]
And for a not found value, the quantity will be 0:
[
{
"_id": "xyz",
"quantity": 0,
"totalQuantity": 7
}
]
Upvotes: 1