# Elastic Case Insensitive Search **URL:** \ **Category:** Elasticsearch\ **Created:** [February 2, 2023, 10:44am UTC](https://discuss.elastic.co/t/elastic-case-insensitive-search/324527 "2023-02-02T10:44:53Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![Tam2](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tam2/32/68143_2.png) [@Tam2](https://discuss.elastic.co/u/Tam2)\ **Post date:** [February 2, 2023, 10:44am UTC](https://discuss.elastic.co/t/elastic-case-insensitive-search/324527/1 "2023-02-02T10:44:53Z")
Hi All, I have a schema which uses Keywords to store values an example of a document would be something like this: We aggregate on a number of properties too such as make/model/colour/condition etc ```auto { "properties": { "bodyStyle": { "type": "keyword" }, "colourBase": { "type": "keyword" }, "condition": { "type": "keyword" }, "fuel": { "type": "keyword" }, "make": { "type": "keyword" }, "mileage": { "type": "integer" }, "model": { "type": "keyword" }, "registration": { "type": "keyword" }, "transmission": { "type": "keyword" } } } ``` I'm looking to implement a free text query, which then will do partial search across a number of fields So they query could be ?query=ford and it would search across make/model/registration/colour for example and return ford, however this currently would only work if the query was ?query=Ford as we store the value in the make field as Ford, not ford - Is it possible for it to convert to lowercase for the purpose of query? - Is it possible to do partial matches too, so search ?query=for would do (LIKE '%for%' - how i would do it in SQL) Effectively tying to create a query something like this: ```auto WHERE LOWER(make) LIKE '%query%' OR LOWER(model) LIKE '%query%' OR LOWER(registration) LIKE '%query%' OR LOWER(colourBase) LIKE '%query%' ``` ---
**Author:** ![Venkata\_Raja](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/venkata_raja/32/114264_2.png) [@Venkata\_Raja](https://discuss.elastic.co/u/Venkata_Raja)\ **Post date:** [February 2, 2023, 11:38am UTC](https://discuss.elastic.co/t/elastic-case-insensitive-search/324527/2 "2023-02-02T11:38:09Z")
Hi Tam, Keywords will be used for exact search only , try to index your fields as text where you can use analysers to make them lowercase while searching and use ngram to do partial searches or you can use [regexp query](https://www.elastic.co/guide/en/elasticsearch/reference/8.6/query-dsl-regexp-query.html). Hope it helped. ---
**Author:** ![Tam2](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tam2/32/68143_2.png) [@Tam2](https://discuss.elastic.co/u/Tam2)\ **Post date:** [February 2, 2023, 11:59am UTC](https://discuss.elastic.co/t/elastic-case-insensitive-search/324527/3 "2023-02-02T11:59:44Z")
> [@Venkata\_Raja](#): > > ngram Thanks for the response, is there any other practical differences between Keyword and Text? I.e. can we still aggregate on Text fields Assume i would need to empty the index, update the schema from Keyword -\> Text and then populate all documents again to index ---
**Author:** ![Venkata\_Raja](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/venkata_raja/32/114264_2.png) [@Venkata\_Raja](https://discuss.elastic.co/u/Venkata_Raja)\ **Post date:** [February 2, 2023, 12:04pm UTC](https://discuss.elastic.co/t/elastic-case-insensitive-search/324527/4 "2023-02-02T12:04:01Z")
keyword -\> support aggregations , exact search text-\> support full text search and can be analysed. ---
**Author:** ![Tam2](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tam2/32/68143_2.png) [@Tam2](https://discuss.elastic.co/u/Tam2)\ **Post date:** [February 2, 2023, 12:05pm UTC](https://discuss.elastic.co/t/elastic-case-insensitive-search/324527/5 "2023-02-02T12:05:19Z")
I need the fields to support aggregations and full text search What alternative approach is there? Create duplicates one Keyword and one text? ---
**Author:** ![Venkata\_Raja](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/venkata_raja/32/114264_2.png) [@Venkata\_Raja](https://discuss.elastic.co/u/Venkata_Raja)\ **Post date:** [February 2, 2023, 12:09pm UTC](https://discuss.elastic.co/t/elastic-case-insensitive-search/324527/6 "2023-02-02T12:09:41Z")
If you needs both aggregations & full text search , use same field as both text and keyword. ```auto "samplefield":{ "type":"text", "fields":{ "keyword":{ "type":"keyword" } } } ``` Now samplefield is text and samplefield.keyword is keyword for aggregations ---
**Author:** ![Tam2](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tam2/32/68143_2.png) [@Tam2](https://discuss.elastic.co/u/Tam2)\ **Post date:** [February 2, 2023, 12:12pm UTC](https://discuss.elastic.co/t/elastic-case-insensitive-search/324527/7 "2023-02-02T12:12:01Z")
Ah interesting, will try this approach - thanks Will i need to remove all my documents, update the property and then re-import all my documents? ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [February 2, 2023, 12:20pm UTC](https://discuss.elastic.co/t/elastic-case-insensitive-search/324527/8 "2023-02-02T12:20:16Z")
If you want to do case insenitive serach on a keyword field you can add a [lowercase normalizer](https://www.elastic.co/guide/en/elasticsearch/reference/8.6/analysis-normalizers.html). ---
**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\ **Post date:** [February 2, 2023, 3:04pm UTC](https://discuss.elastic.co/t/elastic-case-insensitive-search/324527/9 "2023-02-02T15:04:22Z")
Also `term` query has a `case_insensitive` parameter.. very easy if that is all you need. See [here](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-term-query.html) ---
**Author:** ![Tam2](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tam2/32/68143_2.png) [@Tam2](https://discuss.elastic.co/u/Tam2)\ **Post date:** [February 2, 2023, 4:20pm UTC](https://discuss.elastic.co/t/elastic-case-insensitive-search/324527/10 "2023-02-02T16:20:31Z")
Amazing! Just tested that on a field that worked perfectly, allows me to do case in-sensitive search ---
**Author:** ![Tam2](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tam2/32/68143_2.png) [@Tam2](https://discuss.elastic.co/u/Tam2)\ **Post date:** [February 2, 2023, 10:29pm UTC](https://discuss.elastic.co/t/elastic-case-insensitive-search/324527/11 "2023-02-02T22:29:16Z")
Quick follow-up on the regex part of the query I have a sample query like this, where the passed in query **NX19FUP** is a registration, it does find and return this vehicle at the top of the list, however it then also returns a number of other random records which wouldn't have **NX19FUP** contained anywhere in them, in this case i would expect it to return just one record ```auto { "size": 25, "from": 0, "_source": { "exclude": [ "finances", "lists", "feeds" ] }, "query": { "bool": { "filter": { "bool": { "must": [ { "term": { "clientId": 9 } }, { "terms": { "status": [ "ACTIVE" ] } } ] } }, "should": [ { "regexp": { "make": { "value": ".*NX19FUP.*", "case_insensitive": true } } }, { "regexp": { "model": { "value": ".*NX19FUP.*", "case_insensitive": true } } }, { "regexp": { "registration": { "value": ".*NX19FUP.*", "case_insensitive": true } } }, { "regexp": { "colourBase": { "value": ".*NX19FUP.*", "case_insensitive": true } } }, { "regexp": { "colourManufacturer": { "value": ".*NX19FUP.*", "case_insensitive": true } } }, { "regexp": { "variant": { "value": ".*NX19FUP.*", "case_insensitive": true } } }, { "regexp": { "transmission": { "value": ".*NX19FUP.*", "case_insensitive": true } } }, { "regexp": { "series": { "value": ".*NX19FUP.*", "case_insensitive": true } } } ] } } } ``` ---
**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\ **Post date:** [February 2, 2023, 11:19pm UTC](https://discuss.elastic.co/t/elastic-case-insensitive-search/324527/12 "2023-02-02T23:19:59Z")
> [@Tam2](#): > > ```auto > "regexp": { > "make": { > "value": ".*NX19FUP.*", > "case_insensitive": true > } > } > > ``` I am Confused that is a `regex` with a leading and trailing wildcard you will certainly get other results with that string in the middle a `term` search would look like this and be an exact case insensitive search ```auto "term": { "make": "NX19FUP", "case-insensitive" : "true" } ``` ---
**Author:** ![Tam2](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tam2/32/68143_2.png) [@Tam2](https://discuss.elastic.co/u/Tam2)\ **Post date:** [February 3, 2023, 8:19am UTC](https://discuss.elastic.co/t/elastic-case-insensitive-search/324527/13 "2023-02-03T08:19:06Z")
What I'm trying to do is build a generic search box on my website where the user can type in ford focus for example or the reg of a vehicle in this case NX19FUP What i then do is i split the words in the sentence so ford focus becomes and array of ['ford', 'focus'] then search for those terms across a number of fields I use the wildcard at the start and the ned to allow partial matches So if a user searches for ford then i would want it to match all of these: - ford - abc **ford** - abc **ford** adas But also if a user searches for something like a reg which is unique in our system then when searching **NX19FUP** for example, it should only ever find it in the registration field, none of the other fields it searches would contain the text **NX19FUP** with anything before or after, even if it does a wildcard before and after Not sure if there is a way to see why elastic thinks it's found a match for **NX19FUP** on the other fields? **EDIT:** Looking at the query in more detail it's because of the **should** in the query, we have a couple of must filters to filter for a particular customer and the status, then these regexp are in a group of should What would be the correct way to write this query so it creates and AND/OR group? ```auto { "size": 25, "from": 0, "_source": { "exclude": [ "finances", "lists", "feeds" ] }, "query": { "bool": { "filter": { "bool": { "must": [ { "term": { "clientId": 9 } }, { "terms": { "status": [ "ACTIVE" ] } } ] } }, "should": [ { "regexp": { "make": { "value": ".*NX19FUP.*", "case_insensitive": true } } }, { "regexp": { "model": { "value": ".*NX19FUP.*", "case_insensitive": true } } }, { "regexp": { "registration": { "value": ".*NX19FUP.*", "case_insensitive": true } } }, { "regexp": { "colourBase": { "value": ".*NX19FUP.*", "case_insensitive": true } } }, { "regexp": { "colourManufacturer": { "value": ".*NX19FUP.*", "case_insensitive": true } } }, { "regexp": { "variant": { "value": ".*NX19FUP.*", "case_insensitive": true } } }, { "regexp": { "transmission": { "value": ".*NX19FUP.*", "case_insensitive": true } } }, { "regexp": { "series": { "value": ".*NX19FUP.*", "case_insensitive": true } } } ] } } } ``` (in sql it would be like this) AND (make LIKE =%{query}% OR model LIKE '%{query}% OR registration LIKE '%{query}%') So any one of the regex queries should return a result **EDIT 2:** I've added a `"minimum_should_match": 1` which seems to work, is this the best way of doing it? ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [March 3, 2023, 8:19am UTC](https://discuss.elastic.co/t/elastic-case-insensitive-search/324527/14 "2023-03-03T08:19:36Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.