I use Mongo 4.4.
I’m trying to make a mongo aggregation that would return me a document with nested arrays. To specify the context:
I have offers, which contain modules, which contain items, which themselves contain notes. I have a collection for each entity:
offers: {
_id: '64a6c6ed3f24ff001b74cf9d',
name: 'offer name',
}
modules: {
_id: '64a6c6ed3f24ff001b74cfab',
name: 'module name',
offerId: '64a6c6ed3f24ff001b74cf9d',
}
items: {
_id: '5e984aea5e6df000399e6b97',
name: 'module name',
moduleId: '64a6c6ed3f24ff001b74cfab',
}
notes: {
_id: '64c78caf0e5791001b15833c',
name: 'note name',
itemId: '5e984aea5e6df000399e6b97',
}
With my query, I want to retrieve an offer by _id, and this query returns the offer with its modules, the items for each module, and the notes for each item. Basically an object like this:
offer: {
_id: "64a6c6ed3f24ff001b74cf9d",
name: "offer name",
modules: [
{
_id: "64a6c6ed3f24ff001b74cfab",
name: "module name",
items: [
{
_id: "5e984aea5e6df000399e6b97",
name: "item name",
notes: [
{
_id: "64c78caf0e5791001b15833c",
name: "note name"
},
{
// other note
},
...
]
},
{
// other item
},
...
]
},
{
// other module
},
...
]
}
I wrote this query:
db.getCollection("offers").aggregate([
{
$match: {
_id: ObjectId('64a6c6ed3f24ff001b74cf9d'),
}
},
{
$lookup: {
from: 'modules',
localField: '_id',
foreignField: 'offerId',
as: 'modules',
},
},
{
$unwind: '$modules',
},
{
$lookup: {
from: 'items',
localField: 'modules._id',
foreignField: 'moduleId',
as: 'items',
},
},
{
$unwind: '$garantyItems',
},
{
$lookup: {
from: 'notes',
localField: 'garantyItems._id',
foreignField: 'itemId',
as: 'notes',
},
},
{
$group: {
_id: "$_id",
name: { $first: "$name" },
modules: {
$push: {
_id: "$modules._id",
name: "$modules.name",
items: {
$push: {
_id: "$garantyItems._id",
name: "$garantyItems.name",
notes: {
$push: {
_id: "$notes._id",
name: "$notes.name",
}
},
}
},
}
}
}
},
{
$project: {
_id: 1,
name: 1,
modules: 1,
}
},
]);
But when I run it, I get this error:
Unrecognized expression '$push'
Is it impossible to do nested $pushes? In this case how to achieve the desired result?
Thanks in advance.
2
Answers
The
$push
aggregation operator is not valid in a nested value. To get your desired result, use a separate $group for each level, like:As per @user20042973 mentioned in the comment, nested
$lookup
stages should be easier in joining multiple collections in the nested arrays and customizing its projection.Demo @ Mongo Playground