CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Jusep
Jusep

Reputation: 187

MongoDB: Finding to words, then counting the coincidences

I have a database of books that looks like this:

{"_id": ObjectID("1"), "title": "Harry Potter", "year": NumberInt(2000), "author": "JK. Rowling", "genres" : [fantasy, drama]}, 
{"_id": ObjectID("2"), "title": "Harry Potter 99", "year": NumberInt(2020), "author": "JK. Rowling", "genres" : [fantasy, drama]}, 
{"_id": ObjectID("3"), "title": "Book", "year": NumberInt(2020), "author": "Mr. Author", "genres" : [war, romance]}, 
{...}

I would like to get the books that have the combination of the genres "fantasy" and "drama", converting their titles to capital letter, counting them, and finally getting an average of their year of publishment. Something like this:

{
  "_id" : ["HARRY POTTER", "HARRY POTTER 99],
  "yearavg" : "2010",
  "count" : "2"
}

I can't get through the grouping, so I've wrote this so far, it is not working though:

query1 = {"genres" : "Fantasy"}
query2 = {"genres" : "Drama"}
logic = {$and : [query1, query2]}
phase1 = {$match : logic}
phase2 = {$group : {"_id" : "$title", "year" : "$year"}}
phase3 = {$project : {"total" : {"$size" : "$genres"}, "genres" : 1}} 

steps = [phase1, phase2, phase3]
db.genres.aggregate(steps)

Thanks!

Upvotes: 1

Views: 125

Answers (1)

turivishal
turivishal

Reputation: 36144

You can try,

  • $match genres are in all ["fantasy", "drama"] array
  • $group by null and make titles array after converting to upper case using $toUpper, get average year using $avg, get count using $sum
db.genres.aggregate([
  { $match: { genres: { $all: ["fantasy", "drama"] } } },
  {
    $group: {
      _id: null,
      titles: { $push: { $toUpper: "$title" } },
      yearavg: { $avg: "$year" },
      count: { $sum: 1 }
    }
  }
])

Playground

Upvotes: 1

Related Questions