skip to Main Content

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


  1. The $push aggregation operator is not valid in a nested value. To get your desired result, use a separate $group for each level, like:

    {$group:{
      _id:{
           _id:"$_id", 
           module:{_id:"$modules._id", name:"$modules.name"},
           item:{_id:"$garantyItems._id", name:"$garantyItems.name"}
      },
      notes:{$push:{ _id:"$notes._id", name:"$notes.name"}}
    }},
    {$group:{
      _id:{
            _id:"$_id._id",
            module:"$_id.module",
      },
      items:{$push:{_id:"$_id.item._id",name:"$_id:item.name",notes:"$notes"}}
    }},
    {$group:{
      _id:"$_id._id",
      modules:{$push:{_id:"$_id.module._id",name:"$_id.module.name",items:"$items"}}
    }}
    
    Login or Signup to reply.
  2. 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.

    db.offers.aggregate([
      {
        $match: {
          _id: ObjectId("64a6c6ed3f24ff001b74cf9d")
        }
      },
      {
        $lookup: {
          from: "modules",
          localField: "_id",
          foreignField: "offerId",
          pipeline: [
            {
              $lookup: {
                from: "items",
                localField: "_id",
                foreignField: "moduleId",
                pipeline: [
                  {
                    $lookup: {
                      from: "notes",
                      localField: "_id",
                      foreignField: "itemId",
                      pipeline: [
                        {
                          $unset: "itemId"
                        }
                      ],
                      as: "notes"
                    }
                  },
                  {
                    $unset: "moduleId"
                  }
                ],
                as: "items"
              }
            },
            {
              $unset: "offerId"
            }
          ],
          as: "modules"
        }
      }
    ])
    

    Demo @ Mongo Playground

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search