MongoDB $match by array subset

Question

In a MongoDB storing objects the form of

{ array: ["a", "b", "c", ... ] }
{ array: ["a", "d", "f", ... ] }
...

I want to retrieve all objects having e.g. "b" or "d" as values in their array field. How can I do this? What I tried is

{ $match: { array: { $all: [ "b", "d" ] } } }

but this requires "b" and "d" to be present in order to match.

brianz · Accepted Answer · 2013-06-27 21:45:53Z

4

You can use the $or operator with a simple query on the array field, or an $in operation as Sammaye points out below (which will be more performant):

http://docs.mongodb.org/manual/reference/operator/or/#op._S_or

> db.coll.insert({ array: ["a", "b", "c"] })
> db.coll.insert({ array: ["a", "d", "f"] })
>
> db.coll.find({ array : 'b' })
{ "_id" : ObjectId("51cc7aa881e227fe57ccb569"), "array" : [ "a", "b", "c" ] }
>
> db.coll.find({ array : 'a' })
{ "_id" : ObjectId("51cc7aa881e227fe57ccb569"), "array" : [ "a", "b", "c" ] }
{ "_id" : ObjectId("51cc7ab481e227fe57ccb56a"), "array" : [ "a", "d", "f" ] }

$in:

> db.coll.find({ array : { $in : ['a', 'b'] } })
{ "_id" : ObjectId("51cc7aa881e227fe57ccb569"), "array" : [ "a", "b", "c" ] }
{ "_id" : ObjectId("51cc7ab481e227fe57ccb56a"), "array" : [ "a", "d", "f" ] }

> db.coll.find({ array : { $in : ['c', 'z'] } }) 
{ "_id" : ObjectId("51cc7aa881e227fe57ccb569"), "array" : [ "a", "b", "c" ] }

$or:

> db.coll.find({ $or : [ { array  : 'a' }, { array : 'b' } ] })
{ "_id" : ObjectId("51cc7aa881e227fe57ccb569"), "array" : [ "a", "b", "c" ] }
{ "_id" : ObjectId("51cc7ab481e227fe57ccb56a"), "array" : [ "a", "d", "f" ] }
>
> db.coll.find({ $or : [ { array  : 'c' }, { array : 'z' } ] })
{ "_id" : ObjectId("51cc7aa881e227fe57ccb569"), "array" : [ "a", "b", "c" ] }

If you add an index on the array field these queries will be faster.

edited Jun 27, 2013 at 21:45

answered Jun 27, 2013 at 18:03

brianz

7,5086 gold badges40 silver badges44 bronze badges

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

4 Comments

Sammaye Over a year ago

The $in operator would be much better to use here, in fact the $or operator would be quite unperformant for this query

brianz Over a year ago

Presuming there is an index, I think it really depends on the data and how many hits/misses one gets on the query. If I do explains on both query patterns the nscanned can be higher with the $in query. Do you have any examples showing how $or is a bad option?

Sammaye Over a year ago

Nah, an $or is processed differently to how it is in SQL. It is like sending off a separate query for each clause (hence the fact that each clause uses a separate index) and then merging duplicates when they are returned. $or is quite lengthy process actually. $in will show more scans since you may have only been looking at one clause of the $or

brianz Over a year ago

Thanks for the comments and explanation...answer updated and your comment upvoted!

Collectives™ on Stack Overflow

MongoDB $match by array subset

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related