Convert populate to use $lookup

[niftylettuce]

Before:

lib.db.model('Stock').find(stockQuery).populate('product') // ...

After:

            lib.db.model('Stock').collection.aggregate([
              { $match: stockQuery },
              {
                $lookup: {
                  from: 'products',
                  localField: 'product',
                  foreignField: '_id',
                  as: 'product'
                }
              },
              {
                $project: {
                  product: {
                    $arrayElemAt: [ '$product', 0 ]
                  }
                }
              },
              { $sort: sort },
              { $limit: req.query.limit },
              { $skip: req.offset }
            ], callback);

Can we normalize this functionality for population using aggregate and $lookup into a rewrite of the populate function (so it would still function the same, but it'd use $lookup instead?

[niftylettuce]

By default $lookup returns an array of results, and therefore that is why for a single document DB Ref I've put in the $arrayElemAt operator.

[niftylettuce]

Note that $lookup is only available in MongoDB 3.2

[vkarpov15]

Its worth considering, but not for the forseeable future because I'd imagine the majority of mongoose users don't use mongodb 3.2. Also, the only rationale that I could think of for doing this would be "it improves performance considerably." While that might be the case, we'd have to run a benchmark to see.

[macrostart]

+1 for using $lookup internally.

Also, how about implementing this conditionally? This way mongoose users who work with MongoDB 3.2+ could already benefit from the (suspected) performance improvements.

[vkarpov15]

Possible, but it's not really a high priority atm. The difficult part is that $lookup only works with the aggregation framework, so .find().populate() needs to get converted into an aggregation query, which seems unwieldy.

[avchugaev]

+1 for $lookup.

[santimacia]

Is there real benefits of using $lookup insteal of populate? that's the point, however mongodb3.2 it's still to new for the majority of users.

[vkarpov15]

Performance in some cases - populate() requires 2 distinct queries to mongodb and thus 2 sequential round trips over the network, $lookup only takes 1. However, there's ways to structure your schema so populate-like operations can be done in parallel, so $lookup doesn't help as much as you might think.

[santimacia]

Another thing to consider about $lookup within arrays

http://stackoverflow.com/questions/34967482/lookup-on-objectids-in-an-array

[vkarpov15]

Ah yeah that would be tricky to implement - we'd need to be smart enough to build an aggregation pipeline that includes $unwind for every nested array.

[TrejGun]

+1

[pswoodworth]

+1 on principle, but does anyone have any good benchmarking data? Good news, though: it looks like the issue with $lookup on arrays will be resolved as of mongo 3.3.4: https://jira.mongodb.org/browse/SERVER-22881

[faller]

+1

[sidgate]

populate works with multiple database. How would $lookup work then?

[vkarpov15]

@sidgate that's a great point, it actually would not work at all and that's a pretty core feature for mongoose. I'm gonna close this issue because neither mongodb 3.2 nor 3.4 are going to support cross-db $lookup...

[niftylettuce]

For anyone interested in an ES7 version of this, here you go!

// get paginated result of job postings using $lookup
  // <https://github.com/Automattic/mongoose/issues/3683>
  let [ positions, itemCount ] = await Promise.all([
    Positions.aggregate([
      { $match },
      {
        $lookup: {
          from: 'users',
          localField: 'user',
          foreignField: '_id',
          as: 'user'
        }
      },
      {
        $project: {
          user: {
            $arrayElemAt: [ '$user', 0 ]
          }
        }
      },
      { $sort },
      { $limit: ctx.query.limit },
      { $skip: ctx.paginate.skip  }
    ]),
    Positions.count($match).exec()
  ]);

Per https://github.com/crocodilejs/crocodile-node-mvc-framework

I'm also using koa-ctx-paginate with this at https://github.com/koajs/ctx-paginate

[vkarpov15]

Good stuff @niftylettuce but async/await is not ES6 :p

[niftylettuce]

Typo 😆 🚀

[vkarpov15]

Hopefully it'll make it into ES2017 :rocket:

[kintel]

This doesn't change the complications of implementing this., but just a comment on how $lookup could "improve performance considerably":

Using populate on a cursor tends to be unusably slow due to the amount of single queries being performed.

One way around this could be to build a batched cursor, populate per batch, and then unpack each document in that batch and provide it as a readable stream.

[vkarpov15]

That could work. Unfortunately, $lookup still executes a separate query for each individual doc, so you're saving a round trip by using $lookup but using approximately the same db processing time and hogging the connection. I like the cursor batch idea though :+1:

[lykmapipo]

@vkarpov15 What is the state of allow population by lookup. Can we use it whenever possible and revert to normal population whenever necessary.

[vkarpov15]

@lykmapipo not planning on doing this currently because $lookup doesn't support lookups between different databases, so this would break the populate API. Yeah we could fall back to doing conventional populate in cross-db cases but that would add a lot of code and be more work than it's really worth IMO.

[niftylettuce]

Can we revisit this? Why do we need to do cross database lookups?

[warrenday]

Seems like closing this issue due to cross-db issues is a cheap excuse, the feature would be excellent.

[niftylettuce]

Per my chat with @vkarpov15 on Slack, can someone in the community create a performance test with these requirements to compare $lookup vs populate?

6 concurrent $lookups with 2 fast findOne() queries VS. 6 populates with 2 fast findOne() queries

[zbjornson]

Here's a handful of benchmarks with MongoDB 3.4 (v3.2 was similar). Numbers are the median of 15 replicates.

findOne

query	1x concurrency	6x	15x
primary query	0.911 ms	9.226	19.55
primary query, then 2nd query for relationship (~.populate())	1.201 ms	5.782*	11.44
$lookup	0.598 ms	1.763	4.575

*Note: not sure why this is faster than first row; might be cached at some layer, but it's consistent.

find returning array of 140 docs

query	1x concurrency
primary query	3.401 ms
primary query, then 2nd query for relationship (~.populate())	6.872 ms
$lookup	8.705 ms

Code: https://gist.github.com/zbjornson/746a477ee715685fb60963d9e3c5d2d1. The foreignField was indexed, and I verified that the index was used. The collection had about 60k documents in it. I used the mongodb driver directly for the benchmark to show the best possible case.

Looks like it helps with latency for individual documents, but is detrimental for arrays (low-throughput). In the MongoDB profiler it looks like each $lookup goes out as a separate query, so that makes sense.

[tunatoksoz]

There are things that I found that works with populate but not with lookup. I think a model that looks like the following doesn't work well with populate (i can't find the $lookup i wrote for it, though).

Schema({ nestedObject: { item1: { name: 'tuna' }, list: [{ type: ObjectId, ref: 'AnotherModel' }] } });

I will try to find a more concrete example, but that was my recollection of my attempts at replacing a nested populate.

[vkarpov15]

@tunatoksoz yeah if you want to use $lookup with an array you need to add a $unwind.

@zbjornson yeah that doesn't surprise me too much. $lookup does end up as a separate query for each doc because of how aggregation works, so the performance impact isn't quite as cut-and-dried. I'd imagine the concurrency impact is even bigger on .find()...

[Ncifra]

Seems like closing this issue due to cross-db issues is a cheap excuse, the feature would be excellent.

Just wanted to weigh in that the cross-db feature has been a time saver for us, and I think it's one of those things that really makes mongoose an ODM, an extra layer of complexity with additional features from the base driver.

[vkarpov15]

For future reference, here's an example of where $lookup gets super slow:

  const db = await mongodb.MongoClient
    .connect('mongodb://localhost:27017/test', {
      useNewUrlParser: true,
      poolSize: 1 // Only 1 operation can run at a time
    })
    .then(client => client.db())

  await db.dropDatabase()

  for (let i = 0; i < 5000; ++i) {
    await db.collection('Foo').insertOne({ _id: i })
  }
  console.log('Inserted foo docs')

  for (let i = 0; i < 5000; ++i) {
    await db.collection('Bar').insertOne({ _id: i, fooId: i })
  }
  console.log('Inserted bar docs')

  // This aggregation takes 150ms on my laptop, That's because this query executes one full
  // collection scan of 'Bar' for every document in 'Foo', or 20000 collection scans
  const promise = db.collection('Foo').aggregate([
    { $lookup: { from: 'Bar', localField: '_id', foreignField: 'fooId', as: 'bars' } }
  ]).toArray()

Versus what populate does:

  let startTime = Date.now()
  const foos = await db.collection('Foo').find().toArray()
  // "Found foos in 28ms"
  console.log(`Found foos in ${Date.now() - startTime}ms`)

  startTime = Date.now()
  const bars = await db.collection('Bar').find({ fooId: { $in: foos.map(f => f._id) } }).toArray()
  // "Found bars in 15ms"
  console.log(`Found bars in ${Date.now() - startTime}ms`)
  const barMap = bars.reduce((map, b) => map.set(b.fooId.toString(), b), new Map())

  for (const foo of foos) {
    foo.bar = barMap.get(foo._id.toString())
  }

$lookup compounds index misses, populate() minimizes their impact because it only executes one query for each populate(), regardless of the number of docs being populated.