Mongo Distinct Count and Where

Question

I need help with running a Distinct, Count and Where query in Mongo. Collection is called 'trackedevents'

{
    Type: 'Asset Search',
    User: 'ABC'
    Timestamp: '26/01/2015'
},
{
    Type: 'Asset Search',
    User: 'DEF'
    Timestamp : '27/01/2015'
}

What I need help with is getting mongo to give me a Count of Unique Users within a certain Timestamp range where the Type is 'Asset Search'

I could do this in SQL relatively easily but mongo just feels more convoluted. Can some of the experts here help me with this.

No its just me being lazy il update it now – TheMonkeyMan Feb 01 '16 at 13:40 — TheMonkeyMan, Feb 01 '16 at 13:40

score 2 · Accepted Answer · answered Feb 01 '16 at 13:51

You could approach this in a couple of ways. The first would be to use the aggregation framework which uses the $match and $group pipeline steps for querying and grouping the documents by the user field to then get the count:

var pipeline = [
    {
        "$match": {
            "Type": "Asset Search",
            "Timestamp": { "$gte": start, "$lte": end }
        }
    },
    {
        "$group": {
            "_id": "$User",
            "count": { "$sum": 1 }
        }
    }
];

TrackedEvent.aggregate(pipeline, function(err, result){
    if (err) { /* Handle error */ }
    console.log(result);
})

The other way is to use a combination of the distinct() and count() methods

var query = {
        "Type": "Asset Search",
        "Timestamp": { "$gte": start, "$lte": end }
    };
TrackedEvent.distinct('User', query).count().exec(function (err, count) {
    console.log('The number of unique users is: %d', count);
});

Sprotte · Answer 2 · 2016-02-01T13:49:03.600

1

User.find()
      .and([{'Type': 'Asset Search'},
               $and: [
                       {'Timestamp': {$gt: date1}},
                       {'Timestamp': {$lt: date2}}
                     ]
      .count()
      .exec(function (err, userCount) {
         //do stuff
      })

If you want a range between 2 dates you may have to add a second and condition in the and. At the moment with this query yo will get the count of all users with an timestamp greater the req.params.date and type "asset search". also is your timestamp field really a array of timestamps?

//Edit now its like you wished

edited Feb 01 '16 at 13:49

answered Feb 01 '16 at 13:44

Sprotte

541
6
19

Is this valid for mongo though as running find().and() fails. – TheMonkeyMan Feb 01 '16 at 13:47
Of course it is look here http://stackoverflow.com/questions/13272824/combine-two-or-queries-with-and-in-mongoose i also added my answer – Sprotte Feb 01 '16 at 13:50

Mongo Distinct Count and Where

2 Answers2