I have a collection of documents with the following structure:
{
_id: 1,
array: [
{value: 10 },
{value: 11 },
{value: 12 }
]
}
I want make an aggregate query on the collection:
get the proportion of each item. (i.e. for example the proportion of item 1 would be value of item 1 divided by the sum of the values of all three items.
Note: I want to do this within a single query.
The basic idea here is to $unwind the array, $group the document and then apply to each array member. This works better for MongoDB 2.6 or greater due to the $map operator:
db.collection.aggregate([
{ "$unwind": "$array" },
{ "$group": {
"_id": "$_id",
"array": { "$push": "$array" },
"total": { "$sum": "$array.value" }
}},
{ "$project": {
"array": {
"$map": {
"input": "$array",
"as": "el",
"in": {
"value": "$$el.value",
"prop": {
"$divide": [ "$$el.value", "$total" ]
}
}
}
}
}}
])
Or with earlier versions:
db.collection.aggregate([
{ "$unwind": "$array" },
{ "$group": {
"_id": "$_id",
"array": { "$push": "$array" },
"total": { "$sum": "$array.value" }
}},
{ "$unwind": "$array" },
{ "$group": {
"_id": "$_id",
"array": {
"$push": {
"value": "$array.value",
"prop": {
"$divide": [ "$array.value", "$total" ]
}
}
}
}}
])
In either case, if you are not actually "aggregating" anything beyond the document, it is far more efficient to do this calculation in client code. The $unwind here can get very costly due to what it does.
Also if you just stored the "total" as another element, then the simple $project is all that you need, which comes at very little cost by itself. Keeping a total on updates is just simple usage of the $inc operator as you $push new elements to the array.
$map. I did not know about it.
Here is the aggregation pipeline you need:
[
{$unwind: '$array'},
{
$group: {
_id: '$_id',
array: {$push: '$array'},
sum: {$sum: '$array.value'}
}
},
{$unwind: '$array'},
{
$project: {
_id: 1,
'array.value': 1,
'array.proportion': {
$divide: ['$array.value', '$sum']
}
}
}
]
