CODEWITHSUNDEEP
mongodb
jaca777
jaca777

Reputation: 13

How to get year of date and count records for every year

I have a CSV file looks like this: Name, Age, Date of Birth (M/D/Y as a string). I want to get year from 'Date of Birth' field and count number of people for every year. Result should look like this: 

 - 2005: 53
 - 2006: 12
 - 2007: 21
 - ...

I've tried this, but it didn't work :

db.collection.aggregate({
    $group: {
        _id: { $year: "$Date of Birth" },
        total: { $sum: 1 }
    }
})

Upvotes: 1

Views: 32

Answers (1)

whoami - fakeFaceTrueSoul
whoami - fakeFaceTrueSoul

Reputation: 17915

It's because $year will work on date but not on string, Please try this :

db.collectionName.aggregate([{ $project: { _id: 0, 'Date of Birth': { $arrayElemAt: [{ $split: ["$Date of Birth", "/"] }, 2] } } },
{ $group: { _id: '$Date of Birth', total: { $sum: 1 } } }])

Collection Data :

/* 1 */
{
    "_id" : ObjectId("5e0d0924400289966eab31bf"),
    "Date of Birth" : "01/01/2020"
}

/* 2 */
{
    "_id" : ObjectId("5e0d092b400289966eab3349"),
    "Date of Birth" : "01/11/2020"
}

/* 3 */
{
    "_id" : ObjectId("5e0d0939400289966eab3696"),
    "Date of Birth" : "11/01/2021"
}

/* 4 */
{
    "_id" : ObjectId("5e0d0946400289966eab398e"),
    "Date of Birth" : "11/01/2022"
}

/* 5 */
{
    "_id" : ObjectId("5e0d094e400289966eab3bb0"),
    "Date of Birth" : "11/01/2019"
}

Result :

/* 1 */
{
    "_id" : "2022",
    "total" : 1.0
}

/* 2 */
{
    "_id" : "2019",
    "total" : 1.0
}

/* 3 */
{
    "_id" : "2021",
    "total" : 1.0
}

/* 4 */
{
    "_id" : "2020",
    "total" : 2.0
}

Upvotes: 1

Related Questions