How to do conditional sum having in Mongodb?-CodePudding

I have three collections which i want to make query on. The equivalent query in SQL is as follows:

  Select T1.movie_title as movie_title 
  from movie T1
  join movie_cast T2
  on T1.movie_id = T2.movie_id
  join actor T3
  on T2.actor_id = T3.actor_id
  group by T1.movie_title
  having sum(case when T3.actor_gender = 'M' then 1 else 0 end) > sum(case when T3.actor_gender = 'F' then 1 else 0 end);

I was wondering how i can make the same query in mongodb? The condition is to find movies that total male actor is greater than female.

sample data

collection =>imdb_movie

{
    "movieid": 1672052,
    "title": "'Breaker' Morant (1980)",
    "year": "1980"
}
{
    "movieid": 1672580,
    "title": "Til There Was You (1997)",
    "year": "1997"
}

collection => imdb_moviestoactors

{
    "movieid": 1672052,
    "actorid": 121878,
    "as_character": "[Lt. Reed]  <21>",
    "leading": 21
}
{
    "movieid": 1672052,
    "actorid": 88848,
    "as_character": "[Col. Ian 'Johnny' Hamilton]  <7>",
    "leading": 7
}
{
    "movieid": 1672052,
    "actorid": 134348,
    "as_character": "[Large Boer]  <41>",
    "leading": 41
}

{
    "movieid": 1672580,
    "actorid": 12187,
    "as_character": "[Lt. Reed]  <21>",
    "leading": 21
}
{
    "movieid": 1672580,
    "actorid": 8884,
    "as_character": "[Col. Ian 'Johnny' Hamilton]  <7>",
    "leading": 7
}
{
    "movieid": 1672580,
    "actorid": 13438,
    "as_character": "[Large Boer]  <41>",
    "leading": 41
}

Collection => imdb_actors

{
    "actorid": 121878,
    "name'": "Bell, Wayne (I)",
    "sex'": "M'"
}
{
    "actorid": 88848,
    "name": "Ball, Vincent (I)",
    "sex": "M"
}
{
    "actorid": 134348,
    "name": "Bernard, Hank",
    "sex": "F"
}
{
    "actorid": 12187,
    "name'": "Bell, Wayne (I)",
    "sex'": "F'"
}
{
    "actorid": 8884,
    "name": "Ball, Vincent (I)",
    "sex": "F"
}
{
    "actorid": 13438,
    "name": "Bernard, Hank",
    "sex": "M"
}

CodePudding user response：

As already mentioned by @_Takis it is a bad design to map each table from relational database to one collection. NoSQL databases like MongoDB are not optimized for joins, some NoSQL databases do not support joins at all!

One solution is this one:

db.imdb_movie.aggregate([
   {
      $lookup:
         {
            from: "imdb_moviestoactors",
            localField: "movieid",
            foreignField: "movieid",
            as: "moviestoactor"
         }
   },
   { $unwind: "$moviestoactor" },
   {
      $lookup:
         {
            from: "imdb_actors",
            localField: "moviestoactor.actorid",
            foreignField: "actorid",
            as: "actor"
         }
   },
   { $unwind: "$actor" },
   {
      $group: {
         _id: { title: "$title", sex: "$actor.sex" },
         count: { $sum: 1 }
      }
   },
   { $replaceWith: { $mergeObjects: ["$_id", "$$ROOT"] } },
   {
      $group: {
         _id: "$title",
         counts: { $push: "$$ROOT" }
      }
   },
   {
      $project: {
         male: { $first: { $filter: { input: "$counts", cond: { $eq: ["$$this.sex", "M"] } } } },
         female: { $first: { $filter: { input: "$counts", cond: { $eq: ["$$this.sex", "F"] } } } }
      }
   },
   { $match: { $expr: { $gt: ["$male.count", "$female.count"] } } }
])

Mongo Playground