To search for a substring case insensitively, one approach is to convert the text to lowercase in a separate field and then use $regex search on that new field.
If you have text that you need to search without considering the case:
MongoDB Enterprise ruby-driver-rs:PRIMARY> db.foo.insert({foo:'hello world TESTING123'})
WriteResult({ "nInserted" : 1 })
Step 1: Create another field to store the text in lowercase.
MongoDB Enterprise ruby-driver-rs:PRIMARY> db.foo.insert({foo:'hello world TESTING123',foo_lower:'hello world testing123'})
Step 2: Add an index.
MongoDB Enterprise ruby-driver-rs:PRIMARY> db.foo.createIndex({foo_lower:1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"commitQuorum" : "votingMembers",
"ok" : 1,
"$clusterTime" : {
"clusterTime" : Timestamp(1597711723, 7),
"signature" : {
"hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
"keyId" : NumberLong(0)
}
},
"operationTime" : Timestamp(1597711723, 7)
}
Step 3: Search for "testing123" after converting it to lowercase.
Step 4: Utilize $regex for the search.
MongoDB Enterprise ruby-driver-rs:PRIMARY> db.foo.find({foo_lower:{$regex:'testing123'}})
{ "_id" : ObjectId("5f3b2498f885e53d90f30979"), "foo" : "hello world TESTING123", "foo_lower" : "hello world testing123" }
MongoDB Enterprise ruby-driver-rs:PRIMARY> db.foo.find({foo_lower:{$regex:'testing123'}}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.foo",
"indexFilterSet" : false,
"parsedQuery" : {
"foo_lower" : {
"$regex" : "testing123"
}
},
"queryHash" : "0D14CC56",
"planCacheKey" : "1974A2D4",
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"filter" : {
"foo_lower" : {
"$regex" : "testing123"
}
},
"keyPattern" : {
"foo_lower" : 1
},
"indexName" : "foo_lower_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"foo_lower" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"foo_lower" : [
"[\"\", {})",
"[/testing123/, /testing123/]"
]
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "serene",
"port" : 14420,
"version" : "4.4.0",
"gitVersion" : "563487e100c4215e2dce98d0af2a6a5a2d67c5cf"
},
"ok" : 1,
"$clusterTime" : {
"clusterTime" : Timestamp(1597711761, 1),
"signature" : {
"hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
"keyId" : NumberLong(0)
}
},
"operationTime" : Timestamp(1597711761, 1)
}