Mongoid: Query based on size of embedded document array

Question

This is similar to this question here but I can't figure out how to convert it to Mongoid syntax:

MongoDB query based on count of embedded document

Let's say I have Customer: {_id: ..., orders: [...]}

I want to be able to find all Customers that have existing orders, i.e. orders.size > 0. I've tried queries like Customer.where(:orders.size.gt => 0) to no avail. Can it be done with an exists? operator?

Answer 1

Just adding my solution which might be helpful for someone:

scope :with_orders, -> { where(orders: {"$exists" => true}, :orders.not => {"$size" => 0}}) }

Answer 2

I nicer way would be to use the native syntax of MongoDB rather than resort to rails like methods or JavaScript evaluation as pointed to in the accepted answer of the question you link to. Especially as evaluating a JavaScript condition will be much slower.

The logical extension of $exists for a an array with some length greater than zero is to use "dot notation" and test for the presence of the "zero index" or first element of the array:

Customer.collection.find({ "orders.0" => { "$exists" => true } })

That can seemingly be done with any index value where n-1 is equal to the value of the index for the "length" of the array you are testing for at minimum.

Worth noting that for a "zero length" array exclusion the $size operator is also a valid alternative, when used with $not to negate the match:

Customer.collection.find({ "orders" => { "$not" => { "$size" => 0 } } })

But this does not apply well to larger "size" tests, as you would need to specify all sizes to be excluded:

Customer.collection.find({ 
    "$and" => [ 
        { "orders" => { "$not" => { "$size" => 4 } } }, 
        { "orders" => { "$not" => { "$size" => 3 } } },
        { "orders" => { "$not" => { "$size" => 2 } } },
        { "orders" => { "$not" => { "$size" => 1 } } },
        { "orders" => { "$not" => { "$size" => 0 } } }
    ]
})

So the other syntax is clearer:

Customer.collection.find({ "orders.4" => { "$exists" => true } })

Which means 5 or more members in a concise way.

Please also note that none of these conditions alone can just an index, so if you have another filtering point that can it is best to include that condition first.