Complex MongoDB query with multiple OR

Question

I'm using mongodb and I've built to make the following query.

1 AccessToken.where('resource_owner_id').equals(event.resource_owner_id)
2           .where('revoked_at').equals(undefined)
3           .or([ { scopes: /resources/i }, { scopes: new RegExp(event.resource,'i') } ])
4           .or([ { device_ids: [] }, { device_ids: event.body.id } ])

For this example I'm using Mongoose and coffescript.

Unluckily it doesn't work as I want, mainly for the or statement. What I want is the two or on row 3 and 4 being independent.

This means that the field scopes (row 3) must contain the string resources or the string stored in event.resource, meanwhile the field device_ids must be empty or contain the id event.body.id.

From the tests I've prepared I can see that the or command put all together. This means that when just one of the four or conditions is satisfied I get the document.

- { scopes: /resources/i }
- { scopes: new RegExp(event.resource,'i') }
- { device_ids: [] }
- { device_ids: event.body.id }

What I'm not able to reach is to split them in two groups so that both the conditions related to the field scopes and the conditions related to the field device_ids can be satisfied.

Hope the problem is well described. Thanks.

JohnnyHK · Accepted Answer · 2012-11-07 23:54:03Z

8

Don't use $where unless you absolutely have to as performance is terrible. Use a combination of the $or and $size operators instead:

UPDATE There's no Mongoose and method so you have to use the $and operator directly.

AccessToken.find({
    resource_owner_id: event.resource_owner_id,
    revoked_at: undefined,
    $and: [
        { $or: [{ scopes: /resources/i }, { scopes: new RegExp(event.resource,'i') }] },
        { $or: [{ device_ids: { $size: 0 } }, { device_ids: event.body.id }] }
    ]
}, callback);

edited Nov 7, 2012 at 23:54

answered Nov 7, 2012 at 23:38

JohnnyHK

313k70 gold badges632 silver badges479 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andrea Reginato Over a year ago

This is similar to the solution I was coming out with. I updated the question as it was a bit more complex. The problem is that I would love to group the or in a way that one condition for scopes and one condition for device_ids should be satisfied.

JohnnyHK Over a year ago

@AndreaReginato To keep the two $or operations separate you have to put them in an $and operator. See updated answer.

Andrea Reginato Over a year ago

Thanks a lot. I'll follow your detailed advices and let you know how things ended up.

Collectives™ on Stack Overflow

Complex MongoDB query with multiple OR

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related