1

Given the following mongodb documents:

db.customers
{
    "name" : "customer 1",
    "merchants" : [
        { "name" : "test merchant 1" },
        { "name" : "test merchant 2" },
        { "name" : "test merchant 3" }
    ]
}
{
    "name": "customer 2",
    "merchants" : [
        { "name" : "test merchant 1" }
    ]
}

How would I do a find and return only the customer with more than one merchant.

Coming from a SQL background, the equivalent would be:

Customers Table:
id int(11),
name char(56)

Merchants Table:
name char(56),
customer_id int(11)

select customer.id, count(merchants.id) as m_count 
from 
customers, merchants 
where
customers.id = merchants.customer_id
group by
customers.id
having
m_count > 1;

How would I accomplish this in mongodb? I've gotten as far as using an aggregate to get the count of merchants, but don't know how to filter the results based on the count. Maybe there's a whole different way to go about it in mongodb...

lps
  • 1,403
  • 16
  • 28

1 Answers1

0

Try to use $where, like in here

> db.customers.find( { $where: "this.merchants.length > 1" } )

Because MongoDB only provides $size operator to check for equality, one can create a query, where to check if the fields exists and if the length of the array is not 0 and not 1, that means is bigger than 1:

> db.customers.find( {$and: [{merchants: {$not:{$size:0}}},{merchants: {$not:{$size:1}}}, {merchants:{$exists: true}}] } )
Community
  • 1
  • 1
sergiuz
  • 5,353
  • 1
  • 35
  • 51
  • Thank you. The answer is available on the other thread, but your answer worked too so will mark it as accepted. – lps Oct 15 '15 at 03:49