Use a View to Join Two Collections

Use $lookup to create a view over two collections. Applications can query the view without constructing or maintaining complex pipelines.

Example

The examples on this page use data from the sample_mflix sample dataset. For details on how to load this dataset into your self-managed MongoDB deployment, see Load the sample dataset. If you made any modifications to the sample databases, you may need to drop and recreate the databases to run the examples on this page.

Create a Joined View

db.createView( "movieComments", "movies", [
   { $match: { year: { $gte: 2014 } } },
   {
      $lookup:
         {
            from: "comments",
            localField: "_id",
            foreignField: "movie_id",
            as: "movieComments"
         }
   },
   {
      $project:
         {
            _id: 0,
            title: 1,
            year: 1,
            numComments: { $size: "$movieComments" }
         }
   }
] )

In the example:

The $match stage filters the movies collection to documents released in 2014 onward.
The $lookup stage uses the _id field in the movies collection to join documents in the comments collection that have a matching movie_id field.
The matching documents are added as an array in the movieComments field.
The $project stage selects a subset of the available fields, including numComments, which is the count of comments for each movie.

Query the View

Query the view for the five movies with the most comments:

db.movieComments.aggregate( [
   {
      $group:
         {
            _id: "$title",
            totalComments: { $sum: "$numComments" }
         }
   },
   { $sort: { totalComments: -1 } },
   { $limit: 5 }
] )

[
  { _id: '<title>', totalComments: <num> },
  { _id: '<title>', totalComments: <num> },
  { _id: '<title>', totalComments: <num> },
  { _id: '<title>', totalComments: <num> },
  { _id: '<title>', totalComments: <num> }
]

Back

Create & Query

Use Default Collation