title: Mongo multiple $lookup and $group in aggregate
body: <p>So I am trying to &quot;join&quot; 3 different mongo collections in a query. So what I need is multiple <code>$lookup</code> and <code>$group</code> statements in a mongo aggregation.</p>
  <p>My 3 collections look like this:</p>
  <p><strong>users:</strong> (Pseudocode)</p>
  <pre><code>{
    _id,
    username: String,
    group: &lt;some group._id&gt;
  }
  </code></pre>
  <p><strong>groups:</strong></p>
  <pre><code>{
    _id,
    name: String,
    _parent: &lt;another group._id&gt;,
  }
  </code></pre>
  <p><strong>lists:</strong> (these are &quot;itemlists&quot; which are owned by a user):</p>
  <pre><code>{
    _id,
    name: String,
    userId: &lt;some user._id&gt;
  }
  </code></pre>
  <p>So what I would like to do is, given some group id or <code>null</code> (for groups with no parent - groups on the highest level) - get all <code>groups</code> inside of this group, find all <code>users</code> in these groups and also their <code>lists</code>.</p>
  <p>So in the end I would need something like this:</p>
  <pre><code>[
    {
      _id: someGroupId,
      name: someGroupName,
      type: &quot;group&quot;,
      users: [
        {
          _id: someUserId,
          name: someUserName,
          type: &quot;user&quot;,
          lists: [
            ... and the same again for the lists (type: &quot;list&quot;)
          ]
        },
        ... another user
      ]
    },
    ... another group
  ]
  </code></pre>
  <p>I hope you understand what I mean!</p>
  <p>What I have now (thanks to Simon Tretter and lots of research) - please don't mind the Meteor agnostic syntax, you get the idea:</p>
  <pre><code>Groups.aggregate([
      { $match: { _parent: groupId } },
      { $sort: { name: 1 } },
      {
          $lookup: {
              from: 'users',
              localField: '_id',
              foreignField: 'group',
              as: 'users'
          }
      },
      {
          $unwind: {
              path: &quot;$users&quot;,
              preserveNullAndEmptyArrays: true
          }
      },
      {
          $lookup: {
              from: 'lists',
              localField: 'users._id',
              foreignField: 'userId',
              as: 'users.lists'
          }
      },
      {
          $unwind: {
              path: &quot;$users.lists&quot;,
              preserveNullAndEmptyArrays: true
          }
      },
      {
          $match: {
              $or: [
                  { &quot;users.lists&quot;: { $exists: false } },
                  { &quot;users.lists.supplier_id&quot;: supplierId }
              ]
          }
      },
      { $sort: { &quot;users.lists.name&quot;: 1 } },
      {
          $project: {
              &quot;name&quot;: 1,
              &quot;type&quot;: { $literal: 'group' },
              &quot;users._id&quot;: 1,
              &quot;users.name&quot;: { $ifNull: [&quot;$users.profile.company.name&quot;, &quot;$users.username&quot;] },
              &quot;users.type&quot;: { $literal: 'user' },
              &quot;users.lists._id&quot;: 1,
              &quot;users.lists.name&quot;: 1,
              &quot;users.lists.item_count&quot;: 1,
              &quot;users.lists.type&quot;: { $literal: 'list' }
          }
      },
      {
          $group: {
              _id: '$users._id',
              name: { $first: &quot;$users.name&quot; },
              type: { $first: &quot;$users.type&quot; },
              children: {
                  $push: &quot;$users.lists&quot;
              }
          }
      },
      { $sort: { &quot;users.name&quot;: 1 } },
      // until here I have one document per user, with their lists inside the &quot;children&quot; key - all good!
      // now I have to group the users inside their groups ...
      // NOT WORKING: returns completely wrong stuff
      { $group: {
        _id: '$_id',
        name: { $first: &quot;$name&quot; },
        type: { $first: &quot;$type&quot; },
        children: {
          $push: &quot;$users&quot;
        }
      } },
      { $sort: { name: 1 } }
  ]);
  </code></pre>
  <p>I hope someone can get my on the right track ... the best <a href="https://stackoverflow.com/questions/28387603/mongodb-aggregate-nested-subdocuments">similar Stack Overflow question</a> I could find didn't help me that much.</p>