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: <some group._id>
}

groups:

{
  _id,
  name: String,
  _parent: <another group._id>,
}

lists: (these are "itemlists" which are owned by a user):

{
  _id,
  name: String,
  userId: <some user._id>
}

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 (thanks to Simon Tretter and lots of research) - please don't mind the Meteor agnostic syntax, you get the idea:

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: 'users._id',
            foreignField: 'userId',
            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: '$users._id',
            name: { $first: "$users.name" },
            type: { $first: "$users.type" },
            children: {
                $push: "$users.lists"
            }
        }
    },
    { $sort: { "users.name": 1 } },
    // until here I have one document per user, with their lists inside the "children" key - all good!
    // now I have to group the users inside their groups ...
    // NOT WORKING: returns completely wrong stuff
    { $group: {
      _id: '$_id',
      name: { $first: "$name" },
      type: { $first: "$type" },
      children: {
        $push: "$users"
      }
    } },
    { $sort: { name: 1 } }
]);

I hope someone can get my on the right track ... the best similar Stack Overflow question I could find didn't help me that much.

Answer 1

db.groups.aggregate(
[
  { $match: { _parent: groupId } },
  {
     $lookup: {
            from: 'users',
            localField: '_id',
            foreignField: 'group',
            as: 'users'
     }
  },  
  { $unwind: "$users" },
  { $lookup: {
    from: 'lists',
    localField: 'userId',
    foreignField: 'users._id',
    as: 'users.lists'
   }
  },
  { $group: {
    _id: "$_id",
    groupname: { $first: "$name" },
    users: {
      $push: "$users"
   }
  } }
])

Answer 2

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 } }
]);

Answer 3

You'll have to use the group (_id: {_id: "$_id", name: "$name", type: "$type", user_id: "$users._id" }) information in the $group after your comment.

Something like

{
    $group: {
        _id: {
            _id: "$_id",
            name: "$name",
            type: "$type",
            user_id: "$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"
        },
        users: {
            $push: {
                 _id:"$user_id",
                 name: "$name",
                 type: "$type",
                children: "$children"
            }
        }
    }
}