Mongo DB: how to select items with nested array count > 0

Question

The database is near 5GB. I have documents like:

{
    _id: ..
    user: "a"
    hobbies: [{
        _id: ..
        name: football
    },
    {
        _id: ..
        name: beer
    }
    ...
    ]
}

I want to return users who have more then 0 "hobbies" I've tried

db.collection.find({"hobbies" : { &gt : 0}}).limit(10)

and it takes all RAM and no result.

How to do conduct this select?
And how to return only: id, name, count ?
How to do it with c# official driver?

TIA

P.S. near i've found: "Add new field to hande category size. It's a usual practice in mongo world." is this true?

score 17 · Answer 1 · answered Aug 23 '13 at 21:59

In this specific case, you can use list indexing to solve your problem:

db.collection.find({"hobbies.0" : {$exists : true}}).limit(10)

This just makes sure a 0th element exists. You can do the same to make sure the list is shorter than n or between x and y in length by checking the existing of elements at the ends of the range.

score 10 · Accepted Answer · answered Sep 26 '11 at 08:17

10

Have you tried using hobbies.length. i haven't tested this, but i believe this is the right way to query the range of the array in mongodb

db.collection.find({$where: '(this.hobbies.length > 0)'})

answered Sep 26 '11 at 08:17

RameshVel

64,778
30
169
213

this works - but on small amount of data. For my task it seems i should maintain "count" variable. – 1gn1ter Sep 26 '11 at 08:42

score 8 · Answer 3 · answered May 12 '14 at 15:47

8

You can (sort of) check for a range of array lengths with the $size operator using a logical $not:

db.collection.find({array: {$not: {$size: 0}}})

answered May 12 '14 at 15:47

jayelm

7,236
5
43
61

This does seem to be the most applicable answer to an array that has a non-zero size. – Roddy Dec 16 '15 at 05:57

score 2 · Answer 4 · answered Sep 26 '11 at 07:12

That's somewhat true.

According to the manual

http://www.mongodb.org/display/DOCS/Advanced+Queries#AdvancedQueries-%24size

$size

The $size operator matches any array with the specified number of elements. The following example would match the object {a:["foo"]}, since that array has just one element:

db.things.find( { a : { $size: 1 } } );

You cannot use $size to find a range of sizes (for example: arrays with more than 1 element). If you need to query for a range, create an extra size field that you increment when you add elements

So you can check for array size 0, but not for things like 'larger than 0'

score 1 · Answer 5 · edited May 23 '17 at 11:33

Earlier questions explain how to handle the array count issue. Although in your case if ZERO really is the only value you want to test for, you could set the array to null when it's empty and set the option to not serialize it, then you can test for the existence of that field. Remember to test for null and to create the array when you want to add a hobby to a user.

For #2, provided you added the count field it's easy to select the fields you want back from the database and include the count field.

score 0 · Answer 6 · answered Sep 26 '11 at 07:17

if you need to find only zero hobbies, and if the hobbies key is not set for someone with zero hobbies , use EXISTS flag. Add an index on "hobbies" for performance enhancement :

db.collection.find( { hobbies : { $exists : true } } );
However, if the person with zero hobbies has empty array, and person with 1 hobby has an array with 1 element, then use this generic solution :

Maintain a variable called "hcount" ( hobby count), and always set it equal to size of hobbies array in any update. Index on the field "hcount" Then, you can do a query like :

 db.collection.find( { hcount : 0 } ) // people with 0 hobbies    
 db.collection.find( { hcount : 5 } ) // people with 5 hobbies

3 - From @JohnPs answer, "$size" is also a good operator for this purpose. http://www.mongodb.org/display/DOCS/Advanced+Queries#AdvancedQueries-%24size

Mongo DB: how to select items with nested array count > 0

6 Answers6