Get count of distinct values in a column grouped by date mongodb

Question

I am trying to run a mongodb query that for each day requested it will return the count of unique values in a different column. For instance for the month of june the query will return the date as well as the count of distinct values were in the b column on the date. Any one know how to do this I have tried a couple different things with no luck

Below are a couple sample documents, as well as what the output should be

{
  "_id" : ObjectId("578fa05a7391bb0d34bd3c28"),
  "IP" : "123.123.123.123",
  "datetime" : ISODate("2016-07-20T10:04:56-05:00")
},
{
  "_id" : ObjectId("578fa05a7391bb0d34bd3c28"),
  "IP" : "110.123.1.2",
  "datetime" : ISODate("2016-07-20T10:04:56-05:00"),
}

Output should be

{
"date":"2016-07-20",
count:2 -- this is distinct number of IPs for the date above
}

Bertrand Martel Bertrand Martel · Accepted Answer · 2016-07-20T18:28:00

With an aggregate, you can do :

2 $project
2 $group

The 2 projects are used to split day-month-year into a new field to group by it in the $group

mongo query is :

 db.device.aggregate([{
    $project: {
        _id: 1,
        IP: 1,
        day: {
            "$dayOfMonth": "$datetime"
        },
        month: {
            "$month": "$datetime"
        },
        year: {
            "$year": "$datetime"
        }
    }
 }, {
    $project: {
        _id: 1,
        IP: 1,
        datetime: {
            $concat: [{
                    $substr: ["$year", 0, 4]
                },
                "-", {
                    $substr: ["$month", 0, 2]
                },
                "-", {
                    $substr: ["$day", 0, 2]
                }
            ]
        }
    }
 }, {
    $group: {
        _id: {
            date: "$datetime",
            IP: "$IP"
        }
    }
 }, {
    $group: {
        _id: "$_id.date",
        count: {
            $sum: 1
        }
    }
 }])

$sum:1 is used to count your fields

It will give you something like this :

{ "_id" : "2016-7-20", "count" : 2 }
{ "_id" : "2016-8-20", "count" : 1 }

for the input data :

> db.device.find()
{ "_id" : ObjectId("578fa05a7391bb0d34bd3c28"), "IP" : "123.123.123.123", "datetime" : ISODate("2016-07-20T15:04:56Z") }
{ "_id" : ObjectId("578fa05a7391bb0d34bd3c29"), "IP" : "110.123.1.2", "datetime" : ISODate("2016-07-20T15:04:56Z") }
{ "_id" : ObjectId("578fa05a7391bb0d34bd3c25"), "IP" : "110.123.1.2", "datetime" : ISODate("2016-07-20T15:04:56Z") }
{ "_id" : ObjectId("578fa05a7391bb0d34bd3c30"), "IP" : "110.123.1.2", "datetime" : ISODate("2016-08-20T15:04:56Z") }
{ "_id" : ObjectId("578fa05a7391bb0d34bd3c12"), "IP" : "110.123.1.2", "datetime" : ISODate("2016-08-20T15:04:56Z") }

Get count of distinct values in a column grouped by date mongodb

1 Answers