MongoDB - query with two collections

Question

I am trying to get hold of MongoDB and have been experimenting with some basic queries on a single collection but I would like to use two collections in my queries by linking them somehow. I have the below document with information of a movie,

Movies:

{
      id:  1, 
      title: "abc", 
      release_data: "xxxx",
      IMDBURL: "qwe", 
      genre: ["xx","yy's","zz"]
}

and the below type of document in another collection which has information about a user and an embedded document with the movies he has rated as well as the rating itself.

Users:

{
      id:  1, 
      age: xx, 
      gender: "Y", 
      occupation: "abc", 
      zip_code: "asd", 
      movies:[
      { movie: 1, rating: 5 } , 
      { movie: 2, rating: 3 }, 
      { movie: 3, rating: 4 }, 
      { movie: 4, rating: 3 }
      ]
}

How would I make a query that returns the title of a movie that has been rated a 5 at least one time? Thank you.

[Does this help?](http://stackoverflow.com/questions/34967482/lookup-on-objectids-in-an-array) — styvane, Feb 20 '16 at 20:14
[This is more helpful](http://stackoverflow.com/questions/21067464/mongodb-join-like-query-with-two-collections-and-a-where-clause) — user159941, Feb 20 '16 at 22:36

score 1 · Answer 1 · answered Feb 20 '16 at 22:45

MongoDB: No JOINS, no transactions

Funny enough, I have never needed either of them. As with your example, you basically have to ask yourself what you need to have answered and model your data accordingly.

Which movies have been rated at least five times?
For those movies, what are the names?

Given your data model, you don't even get away with ordinary queries: you need an aggregation. With the following dataset:

{ "_id" : ObjectId("56c8e58ee99e5c4e87ec3a4e"), "age" : 22, "gender" : "Y", "occupation" : "abc", "zip_code" : "asd", "movies" : [ { "movie" : 1, "rating" : 5 }, { "movie" : 2, "rating" : 3 }, { "movie" : 3, "rating" : 4 }, { "movie" : 4, "rating" : 3 } ] }
{ "_id" : ObjectId("56c8e598e99e5c4e87ec3a4f"), "age" : 22, "gender" : "Y", "occupation" : "abc", "zip_code" : "asd", "movies" : [ { "movie" : 1, "rating" : 5 }, { "movie" : 2, "rating" : 3 }, { "movie" : 3, "rating" : 4 } ] }
{ "_id" : ObjectId("56c8e599e99e5c4e87ec3a50"), "age" : 22, "gender" : "Y", "occupation" : "abc", "zip_code" : "asd", "movies" : [ { "movie" : 1, "rating" : 5 }, { "movie" : 2, "rating" : 3 }, { "movie" : 3, "rating" : 4 } ] }
{ "_id" : ObjectId("56c8e59ae99e5c4e87ec3a51"), "age" : 22, "gender" : "Y", "occupation" : "abc", "zip_code" : "asd", "movies" : [ { "movie" : 1, "rating" : 5 }, { "movie" : 2, "rating" : 3 }, { "movie" : 3, "rating" : 4 } ] }
{ "_id" : ObjectId("56c8e59be99e5c4e87ec3a52"), "age" : 22, "gender" : "Y", "occupation" : "abc", "zip_code" : "asd", "movies" : [ { "movie" : 1, "rating" : 5 }, { "movie" : 2, "rating" : 3 }, { "movie" : 3, "rating" : 4 } ] }

You can find the movies matching your criteria with the following aggregation

db.movies.aggregate([
  { $unwind:"$movies"},
  { $group:{ _id:"$movies.movie", ratings:{ $sum:1 }}},
  { $match:{ ratings:{ $gte:5}}},
  { $project:{ _id:1 }}
])

Which will return documents looking like this

{ "_id" : 3 }
{ "_id" : 2 }
{ "_id" : 1 }

matching the sample data above. Now, with those, you can look up the movie names in the according collection.

The aggregation, dissected

db.movies.aggregate([

{ $unwind:"$movies"},: Array contents can nor be inspected directly in a way helpful to us here, so we need to deconstruct the array. For each array item, the parent document is basically copied.
{ $group:{ _id:"$movies.movie", ratings:{ $sum:1 }}},: We increment the counter ratings, each time a particular movie is found. So each time a movie rating is found for a particular movie, the counter is incremented.
{ $match:{ ratings:{ $gte:5}}},: we only want documents which have been rated at least 5 times
{ $project:{ _id:1 }}: Last but not least, since we only want the IDs of the movies, we remove everything else.

])

To see what the individual stage does, simply remove the stages following it.

Thank you very much for your thorough answer. I would like the data obtained from the aggregation to be stored in a var movieIds. This way I can iterate through it with movieIds.length() and compare id values with the movies collection. But the movieIds won't have the length() method like it would if it was a product of a find(). Just to be consistent, the aggregation you posted above takes place in db.users and not db.movies. — user159941, Feb 20 '16 at 23:40
Well, MY collection was called db.movies ;) And obvisouly, I don't get your use case. Why store something in a document when you can store it in a dedicated collection using the $out stage, answering one answer properly?. — Markus W Mahlberg, Feb 21 '16 at 00:05

MongoDB - query with two collections

1 Answers1

The aggregation, dissected