CODEWITHSUNDEEP
regexmongodbcountgroup-byaggregation-framework
Bobert4
Bobert4

Reputation: 21

distinct value with count and condition mongo DB

I am new to MongoDB, and so far it seems like it is trying to go out of it's way to make doing simple things overly complex.

I am trying to run the below MYSQL equivalent

SELECT userid, COUNT(*) 
FROM userinfo 
WHERE userdata like '%PC% or userdata like '%wire%' 
GROUP BY userid

I have mongo version 3.0.4 and i am running MongoChef. I tried using something like the below:

 db.userinfo.group({
"key": {
    "userid": true
},
"initial": {
    "countstar": 0
},
"reduce": function(obj, prev) {
 prev.countstar++;
},
"cond": {
    "$or": [{
        "userdata": /PC/
    }, {
        "userdata": /wire/
    }]
}
});

but that did not like the OR. when I took out the OR, thinking I’d do half at a time and combine results in excel, i got an error "group() can't handle more than 20000 unique keys", and the result table should be much bigger than that.

From what I can tell online, I could do this using aggregation pipelines, but I cannot find any clear examples of how to do that.

This seems like it should be a simple thing that should be built in to any DB, and it makes no sense to me that it is not. Any help is much appreciated.

Upvotes: 2

Views: 3742

Answers (2)

Ganesh Apune
Ganesh Apune

Reputation: 197

Here is an example which counts the distinct number of first_name values for records with a last_name value of “smith”:

db.collection.distinct("first_name", {“last_name”:”smith”}).length;

output 3

Upvotes: 0

Blakes Seven
Blakes Seven

Reputation: 50406

/

Works "sooo" much better with the .aggregate() method, as .group() is a very outmoded way of approaching this:

db.userinfo.aggregate([
   { "$match": {
         "userdata": { "$in":[/PC/,/wire/] }
   }},
   { "$group": {
       "_id": "$userid",
       "count": { "$sum": 1 }
   }} 
])

The $in here is a much shorter way of writing your $or condition as well.

This is native code as opposed to JavaScript translation as well, so it runs much faster.

Upvotes: 3

Related Questions