Lookup in mongodb node js

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 this syntax:

{
   $lookup:
     {
       from: ,
       localField: ,
       foreignField: ,
       as: 
     }
}

The $lookup takes a document with these fields:

The operation would correspond to the following pseudo-SQL statement:

SELECT *, <output array field>
FROM collection
WHERE <output array field> IN (
   SELECT *
   FROM <collection to join>
   WHERE <foreignField> = <collection.localField>
);

See these examples:

• Perform a Single Equality Join with $lookup

• Use $lookup with an Array

• Use $lookup with $mergeObjects

MongoDB 3.6 adds support for:

• Executing a pipeline on a joined collection.

• Multiple join conditions.

• Correlated and uncorrelated subqueries.

In MongoDB, a correlated subquery is a pipeline in a $lookup stage that references document fields from a joined collection. An uncorrelated subquery does not reference joined fields.

Note

Starting in MongoDB 5.0, for an uncorrelated subquery in a $lookup pipeline stage containing a $sample stage, the $sampleRate operator, or the $rand operator, the subquery is always run again if repeated. Previously, depending on the subquery output size, either the subquery output was cached or the subquery was run again.

MongoDB correlated subqueries are comparable to SQL correlated subqueries, where the inner query references outer query values. An SQL uncorrelated subquery does not reference outer query values.

MongoDB 5.0 also supports concise correlated subqueries.

To perform correlated and uncorrelated subqueries with two collections, and perform other join conditions besides a single equality match, use this $lookup syntax:

{
   $lookup:
      {
         from: ,
         let: { : , …, :  },
         pipeline: [  ],
         as: 
      }
}

The $lookup stage accepts a document with these fields:

pipeline

Specifies the pipeline to run on the joined collection. The pipeline determines the resulting documents from the joined collection. To return all documents, specify an empty pipeline [].

The pipeline cannot include the $out stage or the $merge stage. Starting in v6.0, the pipeline can contain the Atlas Search $search stage as the first stage inside the pipeline. To learn more, see Atlas Search Support.

The pipeline cannot directly access the joined document fields. Instead, define variables for the joined document fields using the let option and then reference the variables in the pipeline stages.

Note

To reference variables in pipeline stages, use the "$$" syntax.

The let variables can be accessed by the stages in the pipeline, including additional $lookup stages nested in the pipeline.

• A $match stage requires the use of an $expr operator to access the variables. The $expr operator allows the use of aggregation expressions inside of the $match syntax.

  Starting in MongoDB 5.0, the $eq, $lt, $lte, $gt, and $gte comparison operators placed in an $expr operator can use an index on the from collection referenced in a $lookup stage. Limitations:

  • Multikey indexes are not used.

  • Indexes are not used for comparisons where the operand is an array or the operand type is undefined.

  • Indexes are not used for comparisons with more than one field path operand.

• Other (non-$match) stages in the pipeline do not require an $expr operator to access the variables.