$lookup format from week 2

I was able to get past last week with the help of another discussion thread, but I wanted to understand further.

What is the difference between the following pipelines other than that the first one is incorrect? What are they doing differently?

[
  {
    $lookup: {
      from: 'comments',
      localField: '_id',
      foreignField: 'movie_id',
      as: 'comments'
    }
  },
  {
    $sort: { 'comments.date': -1 }
  }
]

and

[
  {
     $lookup: {
         from: 'comments',
         let: {
           id: '$_id'
         },
         pipeline: [{
            $match: {
              $expr: {
                $eq: [
                  $movie_id, '$$id'
                ]
              }
            }
          },
          {
             $sort: {
                date: -1
             }
          }
       ],
       as: 'comments'
    },
  }
]

Thanks!

1 Like

Hey @Samuel_A_Araiza_54686

With the example you provided there is no difference between the outcome of the two pipelines other than syntax, which $lookup provides for 2 different use cases; simple equality match, with the first syntax and join conditions and uncorrelated sub-queries for the second syntax.

See this example & this example of the second syntax’s capabilities

And from the docs

Equality Match
To perform an equality match between a field from the input documents with a field from the documents of the “joined” collection, the $lookup stage has the following syntax:

{
   $lookup:
     {
       from: <collection to join>,
       localField: <field from the input documents>,
       foreignField: <field from the documents of the "from" collection>,
       as: <output array field>
     }
}

Join Conditions and Uncorrelated Sub-queries
New in version 3.6.
To perform uncorrelated subqueries between two collections as well as allow other join conditions besides a single equality match, the $lookup stage has the following syntax:

{
   $lookup:
     {
       from: <collection to join>,
       let: { <var_1>: <expression>, …, <var_n>: <expression> },
       pipeline: [ <pipeline to execute on the collection to join> ],
       as: <output array field>
     }
}
3 Likes

Thank you! I’ll try out a few combinations on Compass later tomorrow =]