Create same query using $lookup

Working with Data Node.js Frameworks
, , ,
1

Hi guys, I’m trying to create an existing query built in mongoose but using just aggregate pipeline, my current query using populate looks similar to this:

model
.find({ deleted: false, movie_id: id })
.populate('movie_id')
.populate('character_id');

Using the pipeline I built this one:

model.aggregate([
      {
        $match: {
          movie_id: id,
          deleted: false,
        },
      },
      {
        $lookup: {
          from: 'movies',
          localField: 'movie_id',
          foreignField: '_id',
          as: 'customer_id',
        },
      },
      {
        $unwind: {
          path: '$movie_id',
          preserveNullAndEmptyArrays: true,
        },
      },
      {
        $lookup: {
          from: 'characters',
          localField: 'character_id',
          foreignField: '_id',
          as: 'character_id',
        },
      },
      {
        $unwind: {
          path: '$character_id',
          preserveNullAndEmptyArrays: true,
        },
      },
    ]);

Am I right with this?

2

Hello @PedroFumero , Welcome to the MongoDB community developer forum,

Yes almost correct, is there any error or are you asking to just check if we can do it other way as well?
I would suggest you can use $addFields and $arrayElemAt operators to get the first element from an array, instead of $unwind stage

  • $arrayElemAt to get specific position’s element from an array so we have selected the first element from the result of the lookup
  • You can also use a similar operator $first to get the first element from an array, just make sure for MongoDB support version
model.aggregate([
    {
        $match: {
            movie_id: id,
            deleted: false,
        }
    },
    {
        $lookup: {
            from: 'movies',
            localField: 'movie_id',
            foreignField: '_id',
            as: 'customer_id',
        },
    },
    {
        $lookup: {
            from: 'characters',
            localField: 'character_id',
            foreignField: '_id',
            as: 'character_id',
        },
    },
    {
        $addFields: {
            customer_id: {
                $arrayElemAt: ["$customer_id", 0]
            },
            character_id: {
                $arrayElemAt: ["$character_id", 0]
            }
        }
    }
]);
3

Thanks a lot! I have a question, in terms of performance is it better to apply $arrayElemAt instead of unwind? I’m looking to improve performance of original query, and btw using pipeline it performs better, but is even better $arrayElemAt instead of $unwind?, thanks in advance.

4

Not sure but not much difference if it is limited to a single element, But yes I can say number of more stages impacts the performance, here we have improved the query by eliminating the extra $unwnid stage, and we have covered both the operations in the single stage $addFields.

You can check explain() function to check the performance of the query.

You can also read the really good explanation of Query Optimization.