title: Mongoose.aggregate(pipeline) link multiple collections using $unwind, $lookup, $group
body: <p>I'm new to the aggregate feature with <code>mongodb</code> and <code>mongoose</code> and have been having difficulty getting the desired results after passing data through my pipeline.</p>
  <p>Below I have simplified the using a fictional example models</p>
  <h2>The scenario</h2>
  <p>I have 3 Models (<code>Ship</code>, <code>Yatch</code>, and <code>Sailboat</code>) that share the interface and extend from a base class. A 4th model, <code>Captain</code>, which has an array <code>watercraftContexts</code> that contain objects used to reference the types of <code>watercrafts</code> associated to each <code>Captain</code>.</p>
  <h3>Example Mongo Data/Schema setup</h3>
  <pre><code>// Model Name: 'Captain', Collection Name: 'captains'
  {
    name: 'Jack Sparrow',                // Captian name
    license: 'SYS-123',                  // License Number
    classes: ['sail', 'yatch', 'ship'],  // Array of allowed operational vessel types
    watercraftContexts: [
      {
         _id: ObjectId('xxxxxxxxxxxxxxx'), // Foreign Model ID
         type: 'Sailboat',                 // Model Name
         ref: 'sailboats'.                 // Collection Name
      },
      {
         _id: ObjectId('xxxxxxxxxxxxxxx'), // Foreign Model ID
         type: 'Yatch',                    // Model Name
         ref: 'yatches'.                   // Collection Name
      },
      {
         _id: ObjectId('xxxxxxxxxxxxxxx'), // Foreign Model ID
         type: 'Ship',                     // Model Name
         ref: 'ships'.                     // Collection Name
      }
    ]
  }
  </code></pre>
  <p>As you can see, the array of objects has been setup to use the <code>mongoose.populate()</code> method with the <code>ref</code> and <code>_id</code> fields and I've implemented a <code>virtual getter</code> <code>watercrafts</code> to hydrate with the <code>populate()</code> feature (code not posted).</p>
  <p>A new field is created as <code>watercrafts</code> with an array of all objects from the 3 different associated collections when using mongoose.Model queries.</p>
  <h3>The Problem</h3>
  <p>I also need a way to <code>aggregate</code> against this data to produce similar results since the Model methods are not available in the <code>aggregate pipline</code>.</p>
  <p>Here is the query generated from my programmatic mongo aggregate:</p>
  <pre><code>[ { '$match':
       { _id:
          { '$in':
             [ ObjectId('5f77bc653887221a703415e1'),
               ObjectId('5f77bc653887221a703415df'),
               ObjectId('5f77bc653887221a703415e0'),
               ObjectId('5f77bc653887221a703415e5') ] } } },
    { '$unwind': '$watercraftContexts' },
    { '$lookup':
       { from: 'ships',
         localField: 'watercraftContexts._id',
         foreignField: '_id',
         as: 'watercrafts.ships' } },
    { '$unwind': '$watercraftContexts' },
    { '$lookup':
       { from: 'yatches',
         localField: 'watercraftContexts._id',
         foreignField: '_id',
         as: 'watercrafts.yatches' } },
    { '$unwind': '$watercraftContexts' },
    { '$lookup':
       { from: 'sailboats',
         localField: 'watercraftContexts._id',
         foreignField: '_id',
         as: 'watercrafts.sailboats' } },
    { '$group':
       { _id: '$_id',
         watercrafts:
          { '$addToSet':
             { '$concatArrays':
                [ '$watercrafts.ships',
                  '$watercrafts.yatches',
                  '$watercrafts.sailboats' ] } }
  </code></pre>
  <p>I'm constructing a mongoose aggregate like so:</p>
  <pre><code>const Captain = mongoose.model('Captain')
  const aggregate = Captain.aggregrate()
  
  // Dynamically create Aggregate Pipeline in another function
  const captains = await Captain.find({})
  const captainIds = captains.map(capt =&gt; capt._id)
  
  // Match sub-set of documents (in actual project)
  aggregate.match({ _id: { $in: captainIds } })
  
  // Collection names to apply $lookup aggregate
  const collectionNames = ['sailboats', 'yatches', 'ships']
  
  // Unwind and Lookup for each polymorphic child class's collection
  collectionNames.forEach(collection =&gt; {
    // Separate watercraftContexts into individual records for lookup
    aggregate.unwind('watercraftContexts')
    // Inner Join collection data on record
    aggregate.lookup({
      from: collection,
      localField: '$watercrafContexts._id',
      foreignField: '_id',
        // Object keyed by collection name with array of collection records
        // to avoid overwrite of previous collection aggregate lookup
      as: `watercrafts.${collection}`  
    })
  })
  
  // Re-group the records by Captain Object Id
  const aggregateAssociationPaths = collectionNames.map(collection =&gt; 
    // Mongo Path to each collection $lookup
    `$watercrafts.${collection}`
  )
  
  // Re-assemble $unwind and $group by Captain's ObjectId
  aggregate.group({
    _id: '$_id',
    $addToSet: {
      // 
      $concatArrays: aggregateAssociationPaths 
    }
  })
  
  /***  !!!   !!!   !!!   !!!   !!!   !!!   !!!   !!!   !!!   !!!   !!!   !!!   !!!  !!!  ***
   *                                                                                        *
   *    WHAT DO I DO NEXT TO GET ALL THE CAPTAIN DATA WITH THE AGGREGATED `watercrafts`
   *                                                                                        *
   ***  !!!   !!!   !!!   !!!   !!!   !!!   !!!   !!!   !!!   !!!   !!!   !!!   !!!  !!!  ***/
  
  // Execute Aggregation
  const captiansWithWatercraftsAssociations = await aggregate
  </code></pre>
  <p>My data is up to this point looks like this and the group isn't working with mongoose:</p>
  <pre><code>[ { _id: 5f77bc653887221a703415df,
      watercrafts:
       [ { _id: 5f77bc653887221a703415d3,
           class: 'sail',
           name: 'Gone with the Wind',
           __v: 0 } ] },
    { _id: 5f77bc653887221a703415e0,
      watercrafts:
       [ { _id: 5f77bc653887221a703415d4,
           class: 'yatch',
           name: 'Liquid Gold',
           __v: 0 } ] },
    { _id: 5f77bc653887221a703415e1,
      watercrafts:
       [ { _id: 5f77bc653887221a703415d5,
           class: 'ship',
           name: 'Jenny',
           __v: 0 } ] },
    { _id: 5f77bc653887221a703415e5,
      watercrafts:
       [ { _id: 5f77bc653887221a703415dd,
           class: 'yatch',
           name: 'Audrey',
           __v: 0 } ] },
    { _id: 5f77bc653887221a703415e5,
      watercrafts:
       [ { _id: 5f77bc653887221a703415dc,
           class: 'sail',
           name: 'Swell Shredder',
           __v: 0 } ] },
    { _id: 5f77bc653887221a703415e5,
      watercrafts:
       [ { _id: 5f77bc653887221a703415de,
           class: 'ship',
           name: 'Jenny IV',
           __v: 0 } ] } ]
  </code></pre>
  <p>Thanks for the support</p>