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.
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
{ "_id" : ObjectId("5a8498a29d1ed018c7f648ca"), "name" : "John, Smith, " } and you search string is JohnSmithBobAlexAlice, does your query match?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.
$indexOfCP array inverted ? The superstring should be the first element and substring should be second elementAdding 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]}
}
})