MongoDB $or query
Use $in
For the query in the question, it's more appropriate to use $in
db.Profiles.find ( { "name" : { $in: ["gary", "rob"] } } );
Why doesn't it work
There's a missing quote - the cli is waiting for you to finish the second part of your or:
db.Profiles.find ( { $or : [ { "name" : "gary" }, {"name":"rob} ] } )
..............................................................^
You need to finish the query sufficiently for the cli to parse it for it to then say there's a syntax error.
Case insensitive matching
As indicated by a comment, if you want to search in a case insensitive manner, then you either use $or
with a $regex:
db.Profiles.find ( { $or : [ { "name" : /^gary/i }, {"name": /^rob/i } ] } )
Or, you simply use one regex:
db.Profiles.find ( { "name" : /^(gary|rob)/i } )
However, a regex query that doesn't start with a fixed string cannot use an index (it cannot use an index and effectively do "start here until no match found then bail") and therefore is sub-optimal. If this is your requirement, it's a better idea to store a normalized name field (e.g. name_lc
- lower case name) and query on that:
db.Profiles.find ( { "name_lc" : { $in: ["gary", "rob"] } } );
Related videos on Youtube
Fruitful
Updated on July 09, 2022Comments
-
Fruitful almost 2 years
I run following query in mongo shell:
db.Profiles.find ( { $or : [ { "name" : "gary" }, {"name":"rob} ] } )
It just returns nothing as expected(JSON)?
-
luckydonald almost 6 yearsNote, the syntax error, initial problem of this question above, got corrected by an edit at a later time.
-
AD7six almost 4 yearsI have reverted that edit, it is not helpful to edit one of the OPs original problems out of the question.
-
-
Fruitful over 11 yearsWow, bad mistake. Thanks @AD7six
-
Fruitful over 11 yearsYour suggested query works much better. Do I need to change any syntax if say names were capitalised in an array? e.g. name:["Gary", "rob"]
-
Fruitful over 11 yearsdb.Profiles.find ( { "names" : { $in: [/gary/i, /rob/i] } } ) answered my own question I think.
-
AD7six over 11 years@Fruitful will work - be wary of relying on queries that cannot be indexed - use ensureIndex appropriately and profile the db to identify slow queries (or use explain on individual queries). Otherwise with time things just get slow or stop being viable.
-
luckydonald almost 6 yearsNote, somebody corrected the syntax in the question above. Just in case you stare at it, not understanding what was wrong.