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
}

Please show sample documents and a desired result

DAXaholic
– DAXaholic

2016-07-20 17:20:03 +00:00
Commented Jul 20, 2016 at 17:20 — DAXaholic
– DAXaholic, Commented Jul 20, 2016 at 17:20
I have edited the question to have this

Brandon Tomblinson
– Brandon Tomblinson

2016-07-20 18:16:02 +00:00
Commented Jul 20, 2016 at 18:16 — Brandon Tomblinson
– Brandon Tomblinson, Commented Jul 20, 2016 at 18:16

Bertrand Martel · Accepted Answer · 2016-07-20 19:15:19Z

5

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") }

edited Jul 20, 2016 at 19:15

answered Jul 20, 2016 at 18:28

Bertrand Martel

46k17 gold badges156 silver badges170 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Brandon Tomblinson Over a year ago

Actually hold on, you just get the count of all the documents I want the count to be the count of distinct IPs for that date

Brandon Tomblinson Over a year ago

I want it grouped so that in your answer for instance the date 2016-7-20 the count should be 2, because there are 2 distinct IPs on that day

Brandon Tomblinson Over a year ago

so for instance, the query below returns the count of distinct IPs for June 2,2016 , I want a an aggregate that can compute this for each date range specified db.collection.distinct("IP",{datetime:{$gte:ISODate('2016-06-02'),$lt:ISODate('2016-06-03')}}).length

Bertrand Martel Over a year ago

Ok, I've updated my post. One additional $group is necessary for this

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related