Joins - Mongodb

Question

I'm trying to join two collections. My first collection has a string field, which contains id of a document in the second collection. Like this:

coll A:
{ show_id: "5c5bf36bfb6fc06f4f57930c"}

coll B:
{ _id: { $oid: "5c5bf36bfb6fc06f4f57930c" } }

I was not able to come up with right $lookup. Any help please.

Could you please provide the code snippet and what actually are you intending to perform on these collections ? — Code Reactor, Mar 22 '19 at 08:08
I am trying to develop a movie ticket booking app. In that 'bookings' collections will have movie show id which need to be joined with 'shows' collection. While displaying booked tickets history bookings collection and shows collection need to be joined. — KRL, Mar 23 '19 at 10:03

score 0 · Answer 1 · answered Mar 24 '19 at 17:11

Okay, since I do not have the actual schema :) ... I will try to explain making some simple assumptions. Please read below carefully!

Firstly, you will need a shows collection, and to make it clean and simple, lets assume it only has two fields: _id (identifier) and showTime.. (Its better)

Secondly, you would need a movies collection, lets assume it has 3 fields: _id (identifier), title and [showID] (showID is array of objectIDs referring to show collection)

And Thirdly, bookings collection, so it should have: _id, movieID (objectID of a particular movie document) and showID (ojectID of a particular show document).

So, your models would be:

bookings{
_id,
movieID,
showID
}

movies{
_id,
title,
[showID]
}

shows{
_id,
showTime
}

Here is the sample mock data present in the mongodb:

 //shows collection
{
  _id: 1
  showTime:"3 PM"
}

{
  _id: 2
  showTime:"6 PM"
}


//movies collection
{
  _id: 1
  title:"Matrix"
  showID:[1,2]
}

{
  _id: 2
  title:"John Wick"
  showID:[1,2]
}

//bookings collection
{
  _id:1
  movieID:1
  showID:1
}  


{
  _id:2
  movieID:1
  showID:2
}

{
  _id:3
  movieID:2
  showID:2
}

So,lets say you want to see a particular booking history, then you could perform the following code to see the relation (joins) between bookings, movies and shows:

db.bookings.aggregate([

    // Join with shows collection
    {
        $lookup:{
            from: "shows",       // shows collection
            localField: "showID",   // field name of bookings collection
            foreignField: "_id", // field name of show collection
            as: "shows_info"         // alias for show collection
        }
    },
    {   $unwind:"$shows_info" },     // $unwind used for getting data in object or for one record only

    // Join with movies collection
    {
        $lookup:{
            from: "movies", 
            localField: "movieID", 
            foreignField: "_id",
            as: "movies_info"
        }
    },
    {   $unwind:"$movies_info" },

    // query for your matching condition here, for example in this case, it would be _id of bookings collection
    {
        $match:{
            $and:[{"_id" : 1}] // here if you replace 1 with 2, you would get data of booking id 2 and so on...
        }
    },

    // define which fields you want to fetch
    {   
        $project:{
            _id : 1,
            movieID : 1, // field of bookings collection
            showID : 1, // field of bookings collection
            movieTitle : "$movies_info.title",// field of movies collection
            showTime : "$shows_info.showTime",// field of shows collection
        } 
    }
]);

Here are some result outputs:

I hope this might solve your problem... IMHO, it would be better if you are to perform data modelling too to provide better vision to the way you handle collections, look into mongoose. If you want detailed description on how and why the $unwind and $lookup was used in such a way, then please go through the official docs of mongodb or you could even look into this questionfor a clear reference. I hope this helped you !

Joins - Mongodb

1 Answers1