MongoDB Aggregation Query Leap Year

Question

I have a problem with queries.

Consider these documents:

{
    "_id" : ObjectId("63a994974ac549c5ea982d2b"),
    "title" : "Destroyer",
    "year" : 2018
},
{
    "_id" : ObjectId("63a994974ac549c5ea982d2a"),
    "title" : "Aquaman",
    "year" : 2014
},

{
    "_id" : ObjectId("63a994974ac549c5ea982d29"),
    "title" : "On the Basis of Sex",
    "year" : 1996   
},

{
    "_id" : ObjectId("63a994974ac549c5ea982d28"),
    "title" : "Holmes and Watson",
    "year" : 1940
},
{
    "_id" : ObjectId("63a994974ac549c5ea982d27"),
    "title" : "Conundrum: Secrets Among Friends",
    "year" : 1957
},
{
    "_id" : ObjectId("63a994974ac549c5ea982d26"),
    "title" : "Welcome to Marwen",
    "year" : 2004
},

{
    "_id" : ObjectId("63a994974ac549c5ea982d25"),
    "title" : "Mary Poppins Returns",
    "year" : 1997
},

{
    "_id" : ObjectId("63a994974ac549c5ea982d24"),
    "title" : "Bumblebee",
    "year" : 2004
}

I am trying to group all leap years in these documents. I tried this code:

var query0 = {$group: {"_id": {"year": "$year"}}}

var query1 = {$addFields: {
      leap: {
        "$and": [
          {"$eq": [{"$mod": ["$year",4]}, 0]},
          {"$or": [
              {"$ne": [{"$mod": ["$year", 100]}, 0]},
              {"$eq": [{"$mod": ["$year", 400]}, 0] }
          ]}
        ]
      }
}}

var query2 = {$project: {leap: true}}

var query3 = {$group: {
      "_id": "$leap",
      "count": {$sum: 1}
  }}

var etapas =  [query0, query1, query2, query3]

db.genres.aggregate(etapas)

But I get this output:

{
    "_id": false,
    "count": 8 
}

It is wrong because the expect output is this:

{
    "_id": Leap,
    "count": 3
}

I do not know what I am doing wrong.. but I cannot get the right output.

How can I fix this?

Thanks so much for your attention on me.

Answer 1

You can try this query:

• First use a $match to get only Leap years. The condition is the same as you have.
• So with only leap years you can group them.
• Now the result is 4 because there are two 2004 so if you only want individual years you can use addToSet to avoid duplicates.
• And $project to get your desired output. Also you can use "_id": 0 if you don't want to output the _id.

With this query you only need one $group stage and it is after the $match so there will be less data.

db.collection.aggregate([
  {
    "$match": {
      "$expr": {
        "$and": [
          {"$eq": [{"$mod": ["$year",4]}, 0]},
          {"$or": [
              {"$ne": [{"$mod": ["$year", 100]}, 0]},
              {"$eq": [{"$mod": ["$year", 400]}, 0] }
          ]}
        ]
      }
    }
  },
  {
    "$group": {
      "_id": null,
      "year": {
        "$addToSet": "$year"
      }
    }
  },
  {
    "$project": {
      "_id": "Leap",
      "count": {
        "$size": "$year"
      }
    }
  }
])

Example here