In this post we saw the basics of MongoDB filtering, now it is time to have a closer look at how to filter and search information in MongoDB collections when dealing with attributes of type string.

In SQL we commonly use the  LIKE function to compare strings. LIKE allow us to check if 2 strings are equal:

SELECT * FROM names n where n.name LIKE 'James Smith'

By combining the use of the LIKE function with wildcards, we can find all values that start, end, or contain a string at a given position. Developers who had worked with SQL Server are well familiar with the % wildcard that represents 0 or any number of characters.

For example, this query will find all values starting with ‘James’:

SELECT * FROM names n where n.name LIKE 'James%'

This query will get all the values ending with ‘Smith’:

SELECT * FROM names n where n.name LIKE '%Smith'

This query will find all values that contains a ‘a’ in any position:

SELECT * FROM names n where n.name LIKE '%a%'

You get the idea. Now let´s see how we can achieve the same with MongoDB.

SQL LIKE querys for MongoDB

In MongoDB we can query string fields quite easily using regular expressions. As an example let´s imagine we got a MongoDB collection of movies that has the following fields: title, plot, and director.

Now I am going to get a list of movies of my favourite director. Too bad that in spite of being my favourite director, I can only remember his last name: oh well, that will have to do. I will filter by the director field, and get all the documents that end with ‘Spielberg’:

db.getCollection("movies").find({'director':/Spielberg$/ })

Notice that the above query is case sensitive, if the ‘director’ field had contained the text ‘steven spielberg’, my query would not have found it.
To make the query case insensitive we have to add an i like this:

db.getCollection("movies").find({'director':/Spielberg$/i })

Let´s continue, and now let´s imagine that I want to look for that Indiana Jones movie that takes place in India, what was its title? Indiana Jones and the lost Temple? Nope, that sounds wrong. I am pretty sure, though, that it was Indian Jones and something or other. Lets make a query to look for all the movies whose title starts with ‘Indiana Jones’, and I will also make my query case insensitive:

db.getCollection("movies").find({'title':/^indiana jones/i })

Uow, there are a lot of Indiana Jones movies in this database: Indiana Jones and the Kingdom of the Crystal Skull , Indiana Jones and the Last Crusade, … Oh, here it is the one I was looking for: Indiana Jones and the Temple of Doom.

Now, I would like to look for a movie to see tonight, something fun, a ‘Comedy’. Uhm … I need to filter by the genre field. That  field contains a coma separated string with the genres that fit a given movie, for example ‘The Derby 1895’ is classified as ‘Documentary, Short, Sport’. I need to search for those items that contain the word ‘Comedy’ in the genre field at any position …

db.getCollection("movies").find({'genre':/Comedy/})

To finish here it is an example of a query to search all the values that start with a given string, and finish with another given string:

db.getCollection("books").find({'title':/^john.*tolkien$/i })

That´s all for today, thanks for reading 😉