-
Type:
Improvement
-
Resolution: Won't Do
-
Priority:
Minor - P4
-
None
-
Affects Version/s: 4.2.0
-
Component/s: Aggregation Framework
-
None
I always dread having to $group after an $unwind. I find myself commonly performing $unwind so I can do operations on the children like $lookup.
A common simplified scenario might be getting the total inventory count for items in a basket:
db.basket.insertMany([
{
person: 'andrea',
basket: [
{name: 'apple', qty: 2},
{name: 'banana', qty: 3},
{name: 'cucumber', qty: 1},
]
}, {
person: 'bob',
basket: [
{name: 'banana', qty: 20}
]
}, {
person: 'curtis',
basket: []
}
]);
db.item.insertMany([
{name: 'apple', location: 'California', inventory: 1},
{name: 'banana', location: 'California', inventory: 2},
{name: 'cucumber', location: 'California', inventory: 3},
{name: 'apple', location: 'Colorado', inventory: 11},
{name: 'banana', location: 'Colorado', inventory: 12},
{name: 'cucumber', location: 'Colorado', inventory: 13}
]);
Now, to get this, the aggregation is:
db.basket.aggregate([
{
$unwind: {
path: '$basket',
preserveNullAndEmptyArrays: true
}
},
{$lookup: {
from: 'item',
let: {
's_name': '$basket.name'
},
as: 'basket._item',
pipeline: [
{$match: {$expr: {$eq: ['$name', '$$s_name']}}},
{$group: {
_id: null,
total_inventory: {$sum: '$inventory'}
}},
{$project: {
_id: 0
}}
]
}},
{$unwind: {
path: '$basket._item',
preserveNullAndEmptyArrays: true
}},
{
$group: {
_id: {
'_id': '$_id'
},
person: {
$first: '$person'
},
basket: {
$push: '$basket'
}
}
}
]);
Now, the 1st $unwind and $lookup stages are fine. The 2nd $unwind stage is a pet peeve of mine (I wish there was just a $lookupOne operator, but that's beside the point).
So that leaves us with the $group stage. You can see the pattern for $grouping after an $unwind is just
pathOfThingUnwound: {$push: '$pathOfThingUnwound'}
The problem, annoying thing, however, is having to do all of the $first operations. It gets quite cumbersome to keep track of all of the fields that are in the document up to that point. I imagine the stage `$addFields` was created from this same reasoning (using a $project and repeating everything all over again is tedious).
Is there a solution?