Mongo multiple $lookup and $group in aggregate

Question

So I am trying to "join" 3 different mongo collections in a query. So what I need is multiple `$lookup` and `$group` statements in a mongo aggregation. My 3 collections look like this: users: (Pseudocode) ``` { _id, username: String, group: } ``` groups: ``` { _id, name: String, _parent: , } ``` lists: (these are "itemlists" which are owned by a user): ``` { _id, name: String, userId: } ``` So what I would like to do is, given some group id or `null` (for groups with no parent - groups on the highest level) - get all `groups` inside of this group, find all `users` in these groups and also their `lists`. So in the end I would need something like this: ``` [ { _id: someGroupId, name: someGroupName, type: "group", users: [ { _id: someUserId, name: someUserName, type: "user", lists: [ ... and the same again for the lists (type: "list") ] }, ... another user ] }, ... another group ] ``` I hope you understand what I mean! What I have now (…

Accepted Answer

Thanks to the help of @Veeram and a tiny bit of tweaking I ended up with the following working code:

```
Groups.aggregate([
    { $match: { _parent: groupId } },
    { $sort: { name: 1 } },
    {
        $lookup: {
            from: 'users',
            localField: '_id',
            foreignField: 'group',
            as: 'users'
        }
    },
    {
        $unwind: {
            path: "$users",
            preserveNullAndEmptyArrays: true
        }
    },
    {
        $lookup: {
            from: 'lists',
            localField: 'userId',
            foreignField: 'users._id',
            as: 'users.lists'
        }
    },
    {
        $unwind: {
            path: "$users.lists",
            preserveNullAndEmptyArrays: true
        }
    },
    {
        $match: {
            $or: [
                { "users.lists": { $exists: false } },
                { "users.lists.supplier_id": supplierId }
            ]
        }
    },
    { $sort: { "users.lists.name": 1 } },
    {
        $project: {
            "name": 1,
            "type": { $literal: 'group' },
            "users._id": 1,
            "users.name": { $ifNull: ["$users.profile.company.name", "$users.username"] },
            "users.type": { $literal: 'user' },
            "users.lists._id": 1,
            "users.lists.name": 1,
            "users.lists.item_count": 1,
            "users.lists.type": { $literal: 'list' }
        }
    },
    {
        $group: {
            _id: {
                _id: "$_id",
                name: "$name",
                type: "$type",
                user_id: "$users._id"
            },
            user_id: {
                $first: "$users._id"
            },
            name: {
                $first: "$users.name"
            },
            type: {
                $first: "$users.type"
            },
            children: {
                $push: "$users.lists"
            }
        }
    },
    { $sort: { name: 1 } },
    {
        $group: {
            _id: "$_id._id",
            name: {
                $first: "$_id.name"
            },
            type: {
                $first: "$_id.type"
            },
            children: {
                $push: {
                    _id: "$user_id",
                    name: "$name",
                    type: "$type",
                    children: "$children"
                }
            }
        }
    },
    { $sort: { name: 1 } }
]);
```

Mongo multiple $lookup and $group in aggregate

Solution

Problem

Related problems