CODEWITHSUNDEEP
mongodbaggregation-framework
Jusep
Jusep

Reputation: 187

MongoDB: Group and then subtract

I have a list of books. I would like to get the authors who have been writting books for a longer period. I wanted to do that substracting the year of their last book, and the year of their first one. My database looks like this:

{"_id": ObjectID("1), "title": "Harry Potter", "year": NumberInt(2000), "author": "JK. Rowling"}, 
"_id": ObjectID("1), "title": "Harry Potter 99", "year": NumberInt(2020), "author": "JK. Rowling"}

So, my code so far looks like this:

phase1= {$group:{"_id" : "$author"}, "enddate" : {$max: "$year"}, "startdate": {$min: "$year"}}    

phase2 = {"total" : {$subtract : ["$enddate", "$startdate"]}
steps = [phase1, phase2]

db.actors.aggregate(steps)

This is not working for me, so I would like if someone could help me to write a correct code to do this. The result should look like this:

{

"_id" : "JK. Rowling",

"startdate" : 2000,

"enddate" : 2020,

"total" : 20

}

Thanks.

Upvotes: 0

Views: 335

Answers (2)

J.F.
J.F.

Reputation: 15235

You are almost there, is as easy as use $addFields and you get it.

Something like this (I've added also a $match stage to filter by author you want... if you want)

db.collection.aggregate([
  {
    "$match": {
      "author": "JK. Rowling"
    }
  },
  {
    "$group": {
      "_id": "$author",
      "enddate": {
        $max: "$year"
      },
      "startdate": {
        $min: "$year"
      }
    }
  },
  {
    $addFields: {
      "total": {
        $subtract: [
          "$enddate",
          "$startdate"
        ]
      }
    }
  }
])

Example here

Upvotes: 1

matthPen
matthPen

Reputation: 4363

You have to use the $addFields stage for your second stage, and correct some typo errors(not closing $group stage after _id), and it will work :

db.collection.aggregate([
  {
    $group: {
      "_id": "$author",
      "enddate": {
        $max: "$year"
      },
      "startdate": {
        $min: "$year"
      }
    }
  },
  {
    $addFields: {
      "total": {
        $subtract: [
          "$enddate",
          "$startdate"
        ]
      }
    }
  }
])

You can test it here

Upvotes: 1

Related Questions