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

..include:: /includes/sample-data-usage.rst

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