Case-Insensitive Queries Without Case-Insensitive Indexes

1
{
2
   "_id": ObjectId("5ef20c5c7ff4160ed48d8f83"),
3
   "first_name": "Harriet",
4
   "last_name": "Tubman",
5
   "quote": "I was the conductor of the Underground Railroad for eight years, 
6
             and I can say what most conductors can't say; I never ran my 
7
             train off the track and I never lost a passenger"
8
},
9
{
10
   "_id": ObjectId("5ef20c797ff4160ed48d90ea"),
11
   "first_name": "HARRIET",
12
   "middle_name": "BEECHER",
13
   "last_name": "STOWE",
14
   "quote": "When you get into a tight place and everything goes against you,
15
             till it seems as though you could not hang on a minute longer, 
16
             never give  up then, for that is just the place and time that 
17
             the tide will turn."
18
},
19
{
20
   "_id": ObjectId("5ef20c937ff4160ed48d9201"),
21
   "first_name": "Bella",
22
   "last_name": "Abzug",
23
   "quote": "This woman's place is in the House—the House of Representatives."
24
}

1
db.InspirationalWomen.find({first_name: "Harriet"})

1
db.InspirationalWomen.find({first_name: "Harriet"}).explain("executionStats")

1
{
2
   "queryPlanner": {
3
      ...
4
      "winningPlan": {
5
         "stage": "FETCH",
6
         "inputStage": {
7
            "stage": "IXSCAN",
8
            "keyPattern": {
9
               "first_name": 1
10
            },
11
            "indexName": "first_name_1",
12
            ...
13
            "indexBounds": {
14
               "first_name": [
15
                  "[\"Harriet\", \"Harriet\"]"
16
               ]
17
            }
18
         }
19
      },
20
      "rejectedPlans": []
21
   },
22
   "executionStats": {
23
      "executionSuccess": true,
24
      "nReturned": 1,
25
      "executionTimeMillis": 0,
26
      "totalKeysExamined": 1,
27
      "totalDocsExamined": 1,
28
      "executionStages": {
29
         ...
30
         }
31
      }
32
   },
33
   ...
34
}

1
db.InspirationalWomen.find({first_name: { $regex: /Harriet/i} })

1
{
2
   "queryPlanner": {
3
      ...
4
      "winningPlan": {
5
         "stage": "FETCH",
6
         "inputStage": {
7
            "stage": "IXSCAN",
8
            "filter": {
9
               "first_name": {
10
                  "$regex": "Harriet",
11
                  "$options": "i"
12
               }
13
            },
14
            "keyPattern": {
15
               "first_name": 1
16
            },
17
            "indexName": "first_name_1",
18
            ...
19
            "indexBounds": {
20
               "first_name": [
21
                  "[\"\", {})",
22
                  "[/Harriet/i, /Harriet/i]"
23
               ]
24
            }
25
         }
26
      },
27
      "rejectedPlans": []
28
   },
29
   "executionStats": {
30
      "executionSuccess": true,
31
      "nReturned": 2,
32
      "executionTimeMillis": 3,
33
      "totalKeysExamined": 4704,
34
      "totalDocsExamined": 2,
35
      "executionStages": {
36
         ...
37
      }
38
   },
39
   ...
40
}

1
db.InspirationalWomen.find({first_name: "Harriet"}).collation( { locale: 'en', strength: 2 } )

1
db.InspirationalWomen.find({first_name: "Harriet"}).collation( { locale: 'en', strength: 2 } ).explain("executionStats")

1
{
2
   "queryPlanner": {
3
      ...
4
      "collation": {
5
         "locale": "en",
6
         ...
7
         "strength": 2,
8
         ...
9
      },
10
      "winningPlan": {
11
         "stage": "FETCH",
12
         "inputStage": {
13
            "stage": "IXSCAN",
14
            "keyPattern": {
15
               "first_name": 1
16
            },
17
            "indexName": "first_name-case_insensitive",
18
            "collation": {
19
               "locale": "en",
20
               ...
21
               "strength": 2,
22
               ...
23
            },
24
            ...
25
            "indexBounds": {
26
               "first_name": [
27
                  "[\"7)KK91O\u0001\u000b\", \"7)KK91O\u0001\u000b\"]"
28
               ]
29
            }
30
         }
31
      },
32
      "rejectedPlans": []
33
   },
34
   "executionStats": {
35
      "executionSuccess": true,
36
      "nReturned": 2,
37
      "executionTimeMillis": 0,
38
      "totalKeysExamined": 2,
39
      "totalDocsExamined": 2,
40
      "executionStages": {
41
         ...
42
         }
43
      }
44
   },
45
   ...
46
}