Query: one field, one operator, multiple values

[Andy]

Hello.

I'm new to MongoDB and I wonder how to do the following query (pseudo-
sql):

SELECT * FROM xyz
WHERE foo LIKE 'aaa' AND foo LIKE 'bbb'

I could convert that to a MongoDB query spec if the field names were
different, or if exact match was needed, or if the conditions were
combined with logical OR instead of AND. But it's not the case. This
is what I tried (with no success):

{foo: {$regex: ['aaa', 'bbb']}
{foo: {$all: [{$regex: 'aaa'}, {$regex: 'bbb'}]}}

I also could not find how to chain queries (in PyMongo). Maybe it's
impossible by design.

I understand that this is an edge case and the regular expressions
could be combined but this is not an option. I'm working on a database
abstraction for key/value stores and document-oriented databases. The
dates are meant to be stored as strings (sortable and transparent) and
the high-level syntax would be:

Document.objects(db).where(created__year=2010, created__month=7)

Or just:

Document.objects(db).where(created__day=15)

As you see, we cannot use "between" or "gt"/"lt" because that won't
make sense for cases when user needs data for a certain month or day
of month. A workaround would be to create extra fields like
"created_year", "created_month", etc. and then we could do even faster
lookups (exact instead of regexes) but the whole point is to provide
advanced queries without changing the data.

This works fine with key/value stores (which provide no query APIs so
we just use Python expressions) and Tokyo Cabinet (which lets you
define as many arbitrary conditions as needed) but I can't imagine how
to do this in MongoDB with specs. Do I have to try "where" with manual
checks against injections? I would really prefer more transparent ways
because the high-level queries must be chained and their conditions
combined just before the query object is sliced or coerced to a list.
Javascript expressions need a lot of extra care (and code).

«Any help would be very... helpful.» (Graham Chapman as King Arthur)

Thanks,
Andy

[Michael Dirolf]

I think $in should work? Or am I misunderstanding your question.
Here's an example:
>>> from pymongo import Connection
>>> import re
>>> db = Connection().test
>>> db.drop_collection("test")
>>> db.test.save({"x": "hello world"})
ObjectId('4c3b239ae6fb1b7cd9000000')
>>> db.test.save({"x": "mongodb"})
ObjectId('4c3b239fe6fb1b7cd9000001')
>>> db.test.save({"x": "foo"})
ObjectId('4c3b23a3e6fb1b7cd9000002')
>>> for x in db.test.find({"x": {"$in": [re.compile("ongo"), re.compile("orl")]}}):
... print x
...
{u'x': u'hello world', u'_id': ObjectId('4c3b239ae6fb1b7cd9000000')}
{u'x': u'mongodb', u'_id': ObjectId('4c3b239fe6fb1b7cd9000001')}

> --
> You received this message because you are subscribed to the Google Groups "mongodb-user" group.
> To post to this group, send email to mongod...@googlegroups.com.
> To unsubscribe from this group, send email to mongodb-user...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.
>
>

[Andy]

Thanks for reply, but $in really doesn't do what I need. As I
understand, {$in:[regex1,regex2]} is equivalent for "matches either
regex1 or regex2", but I need "matches both regex1 *and* regex2", and
$all seems to be the right choice. But apparently it isn't either.

>>> pymongo.version
'1.7'
>>> db.connection.server_info()['version']
u'1.5.3'

>>> db.test.save({"x": "hello world"})

ObjectId('4c3b4080a3baa42f5d000000')
>>> db.test.save({"x": "mongodb"})
ObjectId('4c3b4086a3baa42f5d000001')
>>> db.test.save({"x": "hello mongo"})
ObjectId('4c3b408ea3baa42f5d000002')
>>> list(db.test.find({"x": {"$all": [re.compile("mongo"), re.compile("world")]}}))
[]

I need the "hello mongo" document instead of [] in the last query.

Can I do that somehow? Or maybe it's even a bug that $all doesn't work
that way?

Thanks,
Andy

[Michael Dirolf]

Oh okay I see now. Maybe $all should work for that case - the primary
use is for matching against arrays but might make sense for regex as
well. There is also a case open for $and, which would allow this:
http://jira.mongodb.org/browse/SERVER-1089

[Andy]

Thanks for your help! I've posted there a link to this discussion.