Case Insensitive search with $in

Question

How to search a column in a collection in mongodb with $in which includes an array of elements for search and also caseInsensitive matching of those elements in the column ?

Apart from the $in operator, this is a duplicate of http://stackoverflow.com/questions/1863399/mongodb-is-it-possible-to-make-a-case-insensitive-query — Mick Sear, May 22 '12 at 14:58
Ya right. my question was basically related to caseinsensitive match with $in operator. — Onkar Janwa, May 23 '12 at 05:19
You can use $caseSensitive: false. Refer here : https://docs.mongodb.com/manual/reference/operator/query/text/index.html — Praveen, Sep 28 '19 at 07:02

score 17 · Answer 1 · edited Apr 30 '16 at 12:32

17

Use $in with the match being case insensitive:

Data example:

{ 
    name : "...Event A",
    fieldX : "aAa" 
},
{ 
  name : "...Event B",
  fieldX : "Bab" 
},
{ 
  name : "...Event C",
  fieldX : "ccC" 
},
{ 
  name : "...Event D",
  fieldX : "dDd" 
}

And we want documents were "fieldX" is contained in any value of the array (optValues):

var optValues = ['aaa', 'bbb', 'ccc', 'ddd'];

var optRegexp = [];
optValues.forEach(function(opt){
        optRegexp.push(  new RegExp(opt, "i") );
});

db.collection.find( { fieldX: { $in: optRegexp } } );

This works for $all either.

I hope this helps!

p.s.: This was my solution to search by tags in a web application.

edited Apr 30 '16 at 12:32

elp

8,021
7
61
120

answered Oct 29 '15 at 15:21

Rochadsouza

888
10
10

worked for Ruby needs as well thanks.. just used `Regexp.new(opt, Regexp::IGNORECASE)` in place of `new RegExp(tag, "i") ` – Flo Woo Apr 19 '16 at 07:12
What will be the java code to run this Example in Java or Spring mongodbTemplete. – GoutamS Feb 05 '19 at 21:02
1

Can be simplified to `var optRegexp = optValues.map(value => new RegExp("^" + value + "$", "i");` to use with `{ $in: optRegexp }` – j3ff Jun 08 '20 at 16:51

score 15 · Accepted Answer · edited Jun 22 '19 at 06:32

15

You can use $elemMatch with regular expressions search, e.g. let's search for "blue" color in the following collection:

db.items.save({ 
  name : 'a toy', 
  colors : ['red', 'BLUE'] 
})

> ok

db.items.find({
  'colors': {
    $elemMatch: { 
      $regex: 'blue', 
      $options: 'i' 
    }
  }
})

>[ 
  {   
    "name": "someitem",
    "_id": { "$oid": "4fbb7809cc93742e0d073aef"},   
    "colors": ["red", "BLUE"]
   }
]

edited Jun 22 '19 at 06:32

Dmitrii Sidenko

660
6
19

answered May 22 '12 at 11:19

Zaur Nasibov

22,280
12
56
83

2

One note: this regex will not use index. So, such solution does not fit for 'big data'. Another solution is to support same lowercased array. – Andrew Orsich May 22 '12 at 11:46
Indeed. Thank you for mentioning that. – Zaur Nasibov May 22 '12 at 11:50
7

this is not useful when the we need to check over a array of value – Sai Ram Apr 29 '16 at 07:10

score 4 · Answer 3 · edited Jun 22 '19 at 02:24

4

This works for me perfectly.

From code we can create custom query like this:

{
  "first_name":{
    "$in":[
      {"$regex":"^serina$","$options":"i"}, 
      {"$regex":"^andreW$","$options":"i"}
    ]
  }
}

This will transform to following in mongo after query:

db.mycollection.find({"first_name":{"$in":[/^serina$/i, /^andreW$/i]}})

Same for "$nin".

edited Jun 22 '19 at 02:24

Dmitrii Sidenko

660
6
19

answered Jan 03 '17 at 09:29

Roshan Sharma

84
2

score 2 · Answer 4 · answered Jan 24 '19 at 06:07

2

This is pretty simple

const sampleData = [
  RegExp("^" + 'girl' + "$", 'i'),
  RegExp("^" + 'boy' + "$", 'i')
];
const filerObj = { gender : {$in : sampleData}};

answered Jan 24 '19 at 06:07

Dilshan Liyanage

4,440
2
31
33

score 1 · Answer 5 · answered Mar 12 '19 at 09:17

The way to do it in Java is:

List<String> nameList = Arrays.asList(name.split(PATTERN));
List<Pattern> regexList = new ArrayList<>();
for(String name: nameList) {
regexList.add(Pattern.compile(name , Pattern.CASE_INSENSITIVE));
}
criteria.where("Reference_Path").in(regexList);

score 0 · Answer 6 · answered Mar 31 '15 at 12:01

Here is my case insensitive search (query) with multiple condition (regex) from data of array, I've used $in but it doesn't support case insensitive search.

Example Data

{ 
  name : "...Event A",
  tags : ["tag1", "tag2", "tag3", "tag4] 
},
{ 
  name : "...Event B",
  tags : ["tag3", "tag2"]
},
{ 
  name : "...Event C",
  tags : ["tag1", "tag4"]
},
{ 
  name : "...Event D",
  tags : ["tag2", "tag4"]
}

My query

db.event.find(
  { $or: //use $and or $or depends on your needs
    [ 
      { tags : { 
         $elemMatch : { $regex : '^tag1$', $options : 'i' }
        } 
      },
      { tags : { 
         $elemMatch : { $regex : '^tag3$', $options : 'i' }
        } 
      }
    ]
})

Case Insensitive search with $in

6 Answers6

Linked