CODEWITHSUNDEEP
mongodbmongodb-query
dsharew
dsharew

Reputation: 10665

MongoDb Query: checking if a string contains field value

I know it is possible to check if a field value is like a string like this: 

db.users.findOne({"username" : {$regex : ".*jo*"}});

But what I want is to check if a string contains the filed value.
If I have a string like this: "John, Smith, ", I wanted to match users with username "John" and "Smith".

I know it is possible to split the string and use the $in operator but wanted to know if it can be done easily using string comparisons.

Upvotes: 3

Views: 18906

Answers (3)

May Rest in Peace
May Rest in Peace

Reputation: 2207

Adding to the accepted answer, starting v3.6 , $indexOfCP can be used directly in find() via $expr

db.foo.find({ 
   "$expr": {
       {$ne : [{$indexOfCP: ["John, Smith, ", "$username"]}, -1]}
   }
})

Upvotes: 2

dsharew
dsharew

Reputation: 10665

Starting from Mongodb 3.4 they have introduced $indexOfCP operator.

Searches a string for an occurence of a substring and returns the UTF-8 code point index (zero-based) of the first occurence. If the substring is not found, returns -1.

So this works:

db.user.aggregate(
   [
     {
       $addFields:
          {
            searchIndex: { $indexOfCP: [ "$username", "John, Smith, " ] },
          }
      },

      { 
        $match: {

             searchIndex: {$ne: -1}

           }
        },
   ]
)

and this matches users with username like: "Jo", "John", "Smit", "Smith" ..etc.

Upvotes: 2

user9251303
user9251303

Reputation:

If I've understood your question correctly I beleieve the below $regex is what you're after.

My collection looks like this:

/* 1 */
{
  "_id" : ObjectId("5a8498a29d1ed018c7f648ca"),
  "name" : "John, Smith, "
}

The find and $regex looks like :

db.foo.find({ name: { $regex: 'John.*Smith' } }, { _id : 0 })

If you needed case insensitivity:

db.foo.find({ name: { $regex: 'john.*smith', $options: 'i'} }, { _id : 0 })

Output:

/* 1 */
{
  "name" : "John, Smith, "
}

If I was to run:

db.foo.find( { name: { $regex: 'Bill.*Smith', $options: 'i' }}, { _id : 0})

or

db.foo.find( { name: { $regex: 'John.*Bill', $options: 'i' } }, { _id : 0})

Output:

Fetched 0 record(s) in 1ms

So the $regex will only return a match if John AND Smith are in the field.

To elaborate on the actual $regex itself:

. Matches any single character except the newline character

* Matches the preceding expression 0 or more times

$option i is for case insensitivity

Upvotes: 3

Related Questions