Mongodb query specific month|year not date

Question

How can I query a specific month in mongodb, not date range, I need month to make a list of customer birthday for current month.

In SQL will be something like that:

SELECT * FROM customer WHERE MONTH(bday)='09'

Now I need to translate that in mongodb. Note: My dates are already saved in MongoDate type, I used this thinking that will be easy to work before but now I can't find easily how to do this simple thing.

Answer 1

With MongoDB 3.6 and newer, you can use the $expr operator in your find() query. This allows you to build query expressions that compare fields from the same document in a $match stage.

db.customer.find({ "$expr": { "$eq": [{ "$month": "$bday" }, 9] } })

---

For other MongoDB versions, consider running an aggregation pipeline that uses the $redact operator as it allows you to incorporate with a single pipeline, a functionality with $project to create a field that represents the month of a date field and $match to filter the documents which match the given condition of the month being September.

In the above, $redact uses $cond tenary operator as means to provide the conditional expression that will create the system variable which does the redaction. The logical expression in $cond will check for an equality of a date operator field with a given value, if that matches then $redact will return the documents using the $$KEEP system variable and discards otherwise using $$PRUNE.

Running the following pipeline should give you the desired result:

db.customer.aggregate([
    { "$match": { "bday": { "$exists": true } } },
    {
        "$redact": {
            "$cond": [
                { "$eq": [{ "$month": "$bday" }, 9] },
                "$$KEEP",
                "$$PRUNE"
            ]
        }
    }
])

---

This is similar to a $project +$match combo but you'd need to then select all the rest of the fields that go into the pipeline:

db.customer.aggregate([
    { "$match": { "bday": { "$exists": true } } },
    {
        "$project": {
            "month": { "$month": "$bday" },
            "bday": 1,
            "field1": 1,
            "field2": 1,
            .....
        }
    },
    { "$match": { "month": 9 } }
])

---

With another alternative, albeit slow query, using the find() method with $where as:

db.customer.find({ "$where": "this.bday.getMonth() === 8" })

Answer 2

You can do that using aggregate with the $month projection operator:

db.customer.aggregate([
  {$project: {name: 1, month: {$month: '$bday'}}},
  {$match: {month: 9}}
]);

Answer 3

First, you need to check whether the data type is in ISODate. IF not you can change the data type as the following example.

db.collectionName.find().forEach(function(each_object_from_collection){each_object_from_collection.your_date_field=new ISODate(each_object_from_collection.your_date_field);db.collectionName.save(each_object_from_collection);})

Now you can find it in two ways

db.collectionName.find({ $expr: {
$eq: [{ $year: "$your_date_field" }, 2017]
}});

Or by aggregation

db.collectionName.aggregate([{$project: {field1_you_need_in_result: 1,field12_you_need_in_result: 1,your_year_variable: {$year: '$your_date_field'}, your_month_variable: {$month: '$your_date_field'}}},{$match: {your_year_variable:2017, your_month_variable: 3}}]);

Answer 4

Yes you can fetch this result within date like this ,

db.collection.find({
  $expr: {
          $and: [
              {
                "$eq": [
                  {
                   "$month": "$date"
                 },
                  3
             ]
           },
           {
             "$eq": [
                 {
               "$year": "$date"
                },
                2020
               ]
             }
          ]
         }
        })

Answer 5

If you're concerned about efficiency, you may want to store the month data in a separate field within each document.