MongoDB regex search with Array

Question

I am looking for a logic to retrieve data from database from 1000s records. I cant do at application level.

I have data with ending two twin letter like "ll, gg, ss, ff... ". wants to retrieve words which ends with the above twin characters from DB.

My sample DB:

  [{
     "word": "Floss"
   }, {
    "word": "smacx"
   }, {
   "word": "fuzz"
   }, {
    "word": "grass"
   }, {
    "word": "dress"
   }, {
    "word": "puff"
   }, {
    "word": "cliff"
   }, {
    "word": "sniff"
   }, {
    "word": "chess"
   }, {
    "word": "kiss"
   }, {
    "word": "fell"
   }, {
    "word": "shell"
  }]

checkarray = ['ll','gg','ll','ss'];

Any idea on how to do this as DB Level. Application Level looping is making high and taking more time as it has almost 100k records.

Answer 1

You can use the $in with regular expression by creating a new array of RegExp objects to use with the $in expression as follows:

var checkarray = ['ll','gg','ll','ss'],
    regex = checkarray.map(function (k) { return new RegExp(k); });
db.collection.find({
    "word": { "$in": regex } 
})

Bear in mind that using $in can be fairly efficient with small arrays but not so well with huge lists since it will skip around in the index to find the matching documents, or walk through the whole collection if there isn't an index to use.

---

Besides using the $in with the regular expression, You can use a $regex operator with a pipe-delimited regex pattern containing the checkarray like this:

var checkarray = ['ll','gg','ll','ss'],
    regex = checkarray.join("|");
db.collection.find({
    "word": {
        "$regex": regex, 
        "$options": "i"
    } 
})

---

To match the last two characters, use the following pattern \gg$\, i.e. append $ to the pattern where the $ metacharacter denotes the end of a string. For example, the pattern abc$ can match the following abc, endsinabc, 123abc, ....

So, for your follow-up question

I need words which is ending of letters of checkArray, not in the middle or starting. CheckArray characters should be in ending letter of string. like "EGG" not "FILLED"

you can go about it like this:

var checkarray = ['ll','gg','ff','ss'],
    regex = checkarray.map(function (k) { return new RegExp(k+'$'); });
db.collection.find({
    "word": { "$in": regex } 
})

To test this, populate these sample documents to test collection:

db.test.insert([
    { "_id": 1, "word" : "well" },
    { "_id": 2, "word" : "filled" },
    { "_id": 3, "word" : "glass" },
    { "_id": 4, "word" : "blessed" }
])

The above query will return documents with _ids 1 and 3.

{ "_id" : 1, "word" : "well" }
{ "_id" : 3, "word" : "glass" }