CODEWITHSUNDEEP
mongodbdateaggregation-frameworkdistinct
user3871
user3871

Reputation: 12708

Get distinct ISO dates by days, months, year

I want to get a distinct set of years and months for all document objects in my MongoDB.

For example, if documents have dates:

Return unique months and years for all documents, ex:

Schema snippet:

var myObjSchema = mongoose.Schema({
        date: Date,
        request: {
           ...

I tried using distinct against schema field date:

db.mycollection.distinct('date', {}, {})

But this gave duplicate dates. Output snippet:

ISODate("2015-08-11T20:03:42.122Z"),
ISODate("2015-08-11T20:53:31.135Z"),
ISODate("2015-08-11T21:31:32.972Z"),
ISODate("2015-08-11T22:16:27.497Z"),
ISODate("2015-08-11T22:41:58.587Z"),
ISODate("2015-08-11T23:28:17.526Z"),
ISODate("2015-08-11T23:38:45.778Z"),
ISODate("2015-08-12T06:21:53.898Z"),
ISODate("2015-08-12T13:25:33.627Z"),
ISODate("2015-08-12T14:46:59.763Z")

So the question is:

EDIT: I've found you can get these dates and such with the following query, however the results are not distinct:

db.mycollection.aggregate( 
     [ 
         { 
             $project : { 
                  month : { 
                      $month: "$date" 
                  }, 
                  year : { 
                      $year: "$date" 
                  }, 
                  day: { 
                      $dayOfMonth: "$date" 
                  } 
              }
          } 
      ] 
  );

Output: duplicates

{ "_id" : "", "month" : 7, "year" : 2015, "day" : 14 }
{ "_id" : "", "month" : 7, "year" : 2015, "day" : 15 }
{ "_id" : "", "month" : 7, "year" : 2015, "day" : 15 }

Upvotes: 6

Views: 6799

Answers (3)

Xavier Guihot
Xavier Guihot

Reputation: 61646

Indeed, you can distinct values via a $group/_id: null/$addToSet stage.

I'm also including here the use of dateToString that formats your dates into "%Y-%m" (e.g. 2021-12).

// { date: ISODate("2021-12-05") }
// { date: ISODate("2021-12-08") }
// { date: ISODate("2022-04-05") }
// { date: ISODate("2022-12-14") }
db.collection.aggregate([
  { $group: {
    _id: null,
    months: { $addToSet: { $dateToString: { date: "$date", format: "%Y-%m" } } }
  }}
])
// { _id: null, months: ["2021-12", "2022-04", "2022-12"] }

Upvotes: 4

Hamdi Charef
Hamdi Charef

Reputation: 649

db.mycollection.aggregate(
[
{
"$project": { 
                     "year": { "$year": "$date" }, 
                     "month": { "$month": "$date" }
            }
},{ $group : { 
                    "_id" :{"year" : "$year"  }
               }
},
{
$sort: {'_id': -1
}
   }
])

Upvotes: -1

Sede
Sede

Reputation: 61225

You need to group your document after the projection and use $addToSet accumulator operator

db.mycollection.aggregate([
    { "$project": { 
         "year": { "$year": "$date" }, 
         "month": { "$month": "$date" } 
    }},
    { "$group": { 
        "_id": null, 
        "distinctDate": { "$addToSet": { "year": "$year", "month": "$month" }}
    }}
])

Upvotes: 10

Related Questions