### Author:  [@Hryhorii](https://discuss.elastic.co/u/Hryhorii)
#### Post date: [May 30, 2023, 9:06am UTC](https://discuss.elastic.co/t/keyword-subfield-mapping-causes-unexpected-querying-results/334655/1 "2023-05-30T09:06:34Z")
We have an index mapping schema with a lot of text fields. To be able to sort and filter them we added keyword subfield mapping with lowercase normalizer. Here is a short part of our schema:
```auto
{
"mappings": {
"dynamic": "strict",
"properties": {
"createdTime": {
"type": "date",
"format": "strict_date_optional_time||epoch_millis||basic_date"
},
"field1": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256,
"normalizer": "lowercase"
}
}
},
"fileSize": {
"type": "integer"
},
}
}
}
```
We index 4 documents with such values in "field1":
1. selection
2. electron.jpg
3. election
4. ele cti on
Then we do a full-text search with this query:
```auto
POST /_search
{
"query": {
"bool": {
"must": [
{
"query_string": {
"query": "ele*on"
}
}
]
}
}
}
```
But it returns incorrect results (expected are 2 and 3):
- If we have a text field, and a subfield as a keyword - it returns 3 and 4 results
- If we remap to have only a text field - it returns a 3 result
- If we remap to have only a text field, and also add a simple build-in analyzer to it - _it returns the expected results_
- If we have a text field, and a subfield as a keyword, and also add a simple build-in analyzer to the text field - it returns 2, 3 and 4
What we're missing here? What options do we have?
Please note, that we need to support sorting, filtering (which is available with a keyword subfield), and a full-text wildcard query with an asterisk in the middle.
---
### Author:  [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [May 30, 2023, 10:03am UTC](https://discuss.elastic.co/t/keyword-subfield-mapping-causes-unexpected-querying-results/334655/2 "2023-05-30T10:03:15Z")
Welcome!
Please note that it could be a bad practice to use wildcards ([Query DSL | Elasticsearch Guide [8.11] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl.html#query-dsl-allow-expensive-queries))...
And normally, users don't enter wildcards on a search engine. I'm never doing this within the google search bar as an example. 😉
Instead, you should look at the [wildcard field type](https://www.elastic.co/guide/en/elasticsearch/reference/current/keyword.html#wildcard-field-type) if you really want to use wildcards.
> But it returns incorrect results (expected are 2 and 3):
`ele*on` matches `ele cti on` IMO... But I understand what you mean. You want to compare full terms, right? So you want to compare `ele*on` with `selection`, `electron.jpg`, `election`, `ele`, `cti` and `on`, right.?
So you need to find an analyzer which does exactly this. I'd use a custom analyzer and use the `_analyze` API to better understand ho to build the right one for your use case. See [Test an analyzer | Elasticsearch Guide [8.8] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/8.8/test-analyzer.html).
I'd recommend looking at ngrams instead of using wildcards.
---
### Author:  [@Hryhorii](https://discuss.elastic.co/u/Hryhorii)
#### Post date: [May 30, 2023, 10:29am UTC](https://discuss.elastic.co/t/keyword-subfield-mapping-causes-unexpected-querying-results/334655/3 "2023-05-30T10:29:19Z")
Thanks for the quick response!
> [@dadoonet](#):
>
> Please note that it could be a bad practice to use wildcards ([Query DSL | Elasticsearch Guide [8.8] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl.html#query-dsl-allow-expensive-queries))...
>
> And normally, users don't enter wildcards on a search engine. I'm never doing this within the google search bar as an example. 😉
We'd read about that, but for now, we decided to start in this way since we migrating from Azure Search and we use a similar approach there (Azure Search is also built on top of the Lucene engine). For other scenarios (including trailing and leading wildcard querying), everything works fine.
> [@dadoonet](#):
>
> Instead, you should look at the [wildcard field type](https://www.elastic.co/guide/en/elasticsearch/reference/current/keyword.html#wildcard-field-type) if you really want to use wildcards.
Regarding using the [wildcard field type](https://www.elastic.co/guide/en/elasticsearch/reference/current/keyword.html#wildcard-field-type) as far as I understand we can't do a full-text search with this field, we have to add a specific field in wildcard query?
```auto
GET /_search
{
"query": {
"wildcard": {
"user.id": {
"value": "ki*y",
"boost": 1.0,
"rewrite": "constant_score"
}
}
}
}
```
> [@dadoonet](#):
>
> `ele*on` matches `ele cti on` IMO... But I understand what you mean. You want to compare full terms, right? So you want to compare `ele*on` with `selection`, `electron.jpg`, `election`, `ele`, `cti` and `on`, right.?
>
> So you need to find an analyzer which does exactly this. I'd use a custom analyzer and use the `_analyze` API to better understand ho to build the right one for your use case. See [Test an analyzer | Elasticsearch Guide [8.8] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/8.8/test-analyzer.html).
You got it right! As I mentioned, we tried a simple analyzer for a text field only. This works as expected (search response contains `electron.jpg` and `election`):
```auto
{
"mappings": {
"dynamic": "strict",
"properties": {
"createdTime": {
"type": "date",
"format": "strict_date_optional_time||epoch_millis||basic_date"
},
"field1": {
"type": "text",
"analyzer": "simple"
},
"fileSize": {
"type": "integer"
},
}
}
}
```
But as soon as we add a keyword subfield the search response will start returning `electron.jpg`, `election`, and also `ele cti on`. We found it weird since we thought that keyword subfield mapping should be different from the main text field.
Thanks for suggesting ngrams! Will we be able to support our scenarios with them (both full-text and search within a specific field)?
---
### Author:  [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [May 30, 2023, 1:03pm UTC](https://discuss.elastic.co/t/keyword-subfield-mapping-causes-unexpected-querying-results/334655/4 "2023-05-30T13:03:16Z")
> [@Hryhorii](#):
>
> Azure Search is also built on top of the Lucene engine
I think (from what I recall), that Azure Search was actually built on top of Elasticsearch. But that's another story 😉 .
> we can't do a full-text search with this field, we have to add a specific field in wildcard query?
Indeed. So normally I recommend doing multiple searches at the same time. Combining scores between partial match and exact match is normally super helpful for the end users. See the following script as an idea:
>
> Will we be able to support our scenarios with them (both full-text and search within a specific field)?
Yes I believe so with the above strategy ^^^ 🙂
---
### Author:  [@Hryhorii](https://discuss.elastic.co/u/Hryhorii)
#### Post date: [May 30, 2023, 5:03pm UTC](https://discuss.elastic.co/t/keyword-subfield-mapping-causes-unexpected-querying-results/334655/5 "2023-05-30T17:03:37Z")
> [@dadoonet](#):
>
> So normally I recommend doing multiple searches at the same time
As far as I understand, your recommendation is to do multiple wildcard searches for each field in our index document if we want to achieve a full-text search with a wildcard query. Like:
```auto
GET /_search
{
"query": {
"wildcard": {
"field1": {
"value": "ele*on"
}
},
"wildcard": {
"field2": {
"value": "ele*on"
}
},
"wildcard": {
"field3": {
"value": "ele*on"
}
}
}
}
```
Do I get it right?
---
### Author:  [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [May 30, 2023, 5:31pm UTC](https://discuss.elastic.co/t/keyword-subfield-mapping-causes-unexpected-querying-results/334655/6 "2023-05-30T17:31:17Z")
Yeah. But was more thinking of something like:
```auto
GET /_search
{
"query": {
"multi_match" : {
"query": "ele on",
"fields": ["field1.keyword^3.0", "field1^2.0", "field1.ngram", "field1.phonetic"]
}
}
}
```
But as you (really) want to use wildcards, I guess you have it right...
---
### Author:  [@Hryhorii](https://discuss.elastic.co/u/Hryhorii)
#### Post date: [May 30, 2023, 6:38pm UTC](https://discuss.elastic.co/t/keyword-subfield-mapping-causes-unexpected-querying-results/334655/7 "2023-05-30T18:38:48Z")
Thank you for your quick and detailed responses! I think it will help us.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [June 27, 2023, 6:39pm UTC](https://discuss.elastic.co/t/keyword-subfield-mapping-causes-unexpected-querying-results/334655/8 "2023-06-27T18:39:39Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.