My Adventures in Coding

May 19, 2011

MongoDB – Negative Regex Query in Mongo shell

Filed under: MongoDB — Brian @ 8:26 am
Tags: , , ,

MongoDB’s interactive shell, supports the use of Regular Expressions as one of their Advanced Query options. The other day it came up that I needed to do a query for all documents where the text of a field did NOT start with a given pattern. It took a little bit of trial and error to get the regex right in the mongo shell, so here is what worked for us.

So let’s say for example we have a MongoDB database with a collection called “cars”. The primary key for each document is “make:model”, for example “Ford:Fusion”.

To find all cars made by “Ford”, you would just query for all documents where the id starts with the text “Ford”:

db.cars.find({"_id":/^Ford/})

But, if you want to query for all cars that are NOT made by “Ford”, you can do this with a negative regex such as:

db.cars.find({"_id":/^((?!Ford).)/})

Obviously, this type of regex is not efficient, but if you just need to do a quick query from the Mongo shell, it can come in handy.

I hope that saves you some time!

Advertisements

Blog at WordPress.com.