How to find a substring in a field in Mongodb

Question

How can I find all the objects in a database with where a field of a object contains a substring?

If the field is A in an object of a collection with a string value:

I want to find all the objects in the db "database" where A contains a substring say "abc def".

I tried:

db.database.find({A: {$regex: '/^*(abc def)*$/''}})

but didn't work

UPDATE

A real string (in unicode):

Sujet  Commentaire sur  Star Wars  Episode III - La Revanche des Sith 1

Need to search for all entries with Star Wars

db.test.find({A: {$regex: '^*(star wars)*$''}}) not wokring

As @derick points out, if you want to look for "any string of characters", you need `.*` : `.` for any (single) character, `*` for "the previous thing any number (including zero) times. The `(star wars)*$` you have would match "star warsstar warsstar wars" but not "star wars is great". — drevicko, Jun 24 '13 at 01:20

Derick · Accepted Answer · 2013-06-23T09:27:07.253

82

Instead of this:

db.database.find({A: {$regex: '/^*(abc def)*$/''}})

You should do this:

db.database.find({A: /abc def/i })

^* is not actually valid syntax as ^ and $ are anchors and not something that is repeatable. You probably meant ^.* here. But there is no need for ^.* as that simply means "Everything up to the character following" and (abc def)* means "0 or more times "abc def", but it has to be at the end of the string, because of your $. The "i" at the end is to make it case insensitive.

edited Jun 23 '13 at 09:27

answered Apr 20 '12 at 10:01

Derick

35,169
5
76
99

2

note that using '^' helps speeding up regex query since indexes will be effective only in this form (see the bottom of the page at http://docs.mongodb.org/manual/reference/operator/regex/) – Tzury Bar Yochay May 05 '13 at 15:21
Correct me if I'm wrong, but does '^*' make any sense? Shouldn't it be '^.*'? To me '^*' reads "zero or more beginnings of the line", of which there is only and always one, and it looks to me like it __doesn't__ allow characters between the beginning of the line and the rest of the regex... – drevicko Jun 17 '13 at 07:58
@drevicko, you're right. That was a typo in my answer. I've clarified it. – Derick Jun 23 '13 at 09:27
It's worth noting that if you've got an index on `A`, it will only be used if [the regex is anchored to the start of the line and is case sensitive](http://docs.mongodb.org/manual/reference/operator/regex/) - ie: `/^.*abc def/` – drevicko Jun 24 '13 at 01:17
1

drevicko, that's not correct. The first part of the regex match needs to be *static* otherwise it sill will have to do a full collection scan. Ie: ^abcdef works well to use only part of an index, but ^.*abcdef does not. In the first case, a range query is done first (>= "abcdef & < "abcdeg") and then the regexp is run on each of the values that comes out of that. – Derick Jun 24 '13 at 09:16
How to write Spring Data mongo repository method for this? Please guide – Oct 08 '15 at 14:32
-1 because your answer is really misleading on the error OP made. And you are implying he is using NodeJS or mongoshell (with the regex format you offer), however this won’t work with python or mlab for instance. So I’d propose the query `{A: {$regex: 'star wars', $option: 'i'}}`, or luisCarlos’s answer – Ulysse BN Feb 08 '17 at 06:19
How (in)efficient is this? – vishvAs vAsuki Nov 08 '17 at 05:37

score 56 · Answer 2 · edited Mar 22 '19 at 16:05

56

Just use the string "Star Wars" and $regex will do the rest

db.test.find({A: {$regex: 'Star Wars'}})

edited Mar 22 '19 at 16:05

DJDaveMark

2,669
23
35

answered Feb 06 '15 at 16:53

LuisCarlos Rodriguez

663
5
7

1

this is great, but this one is case sensitive – Pyd Apr 15 '18 at 18:07
12

Case insensitive: `db.test.find({A: {$regex: 'Star Wars', $options: "$i"}})` – DJDaveMark Jan 23 '19 at 22:04

score 9 · Answer 3 · answered Jun 28 '19 at 14:38

9

$regex is too expensive/slow on large collections.

I'd suggest to leverage aggregation framework and $indexOfCP.

db.test.aggregate([{$match: 
    {$expr: { $gt: [{ $indexOfCP: [ "$A", "Star Wars" ] }, -1]}}
    }, {$project: {A:1}}])

For case-insensitive search you may add $toUpper to the mix and search for STAR WARS.

answered Jun 28 '19 at 14:38

dev101

119
1
2

A bit late to the party, but why do you think that it has better performance than a regex? – Anarion Mar 31 '23 at 17:07

score 2 · Answer 4 · answered Mar 11 '20 at 06:44

2

This worked for me:

db.test.find({"A": {'$regex': '.*star wars.*'}})

answered Mar 11 '20 at 06:44

Ghasem

14,455
21
138
171

As pointed out in other answers, `.*` is superfluous/meaningless here and only increases computational costs. – yktoo Jul 07 '22 at 09:16

Dror · Answer 5 · 2021-04-13T06:50:49.257

2

this one uses the aggregation syntax:

db.movies.aggregate([
  {$match: 
    {
      title: {$regex: 'Star'}
    } 
  }
] )

edited Apr 13 '21 at 06:50

answered Apr 13 '21 at 06:30

Dror

5,107
3
27
45

score 1 · Answer 6 · answered Dec 12 '20 at 15:52

1

// executes, name LIKE john and only selecting the "name" and "friends" fields
db.collection.find({ name: /john/i }, 'name friends').exec();

// passing options
db.collection.find({ name: /john/i }, null, { skip: 10 }).exec();

answered Dec 12 '20 at 15:52

kartik tyagi

6,256
2
14
31

I like this because it does it with "Native" Strings vs Regex I tested this in Compass – CryptoCode Aug 26 '23 at 11:08

score 1 · Answer 7 · edited Feb 17 '23 at 12:09

1

db.getCollection('your_collection_name').find({key:{$regex: "value"}})

Example:- your_collection_name:

users (let's say), key: email, value: @gmail.com,

now query is

db.getCollection('users').find({email:{$regex: "@gmail.com"}})

Then you will get all users which contain @gmail.com in the email

edited Feb 17 '23 at 12:09

ejuhjav

2,660
2
21
32

answered Feb 09 '23 at 06:32

Manohar_MR

11
2

score 0 · Answer 8 · answered Feb 27 '23 at 16:44

Here's the syntax to do the substring search in mongoDb.. the $options : "i" makes the search case-insensitive .. if you want case sensitive search remove the options argument.

    db.collection("test")
    .find({ name: { $regex: subStringName, $options: "i" } })

How to find a substring in a field in Mongodb

8 Answers8

Linked