# Unable to run SQL query on multi fields using Elastic Search v. 7.3

**URL:** <https://discuss.elastic.co/t/unable-to-run-sql-query-on-multi-fields-using-elastic-search-v-7-3/222321>\
**Category:** Elasticsearch\
**Tags:** elastic-stack-sql\
**Created:** [March 5, 2020, 2:45pm UTC](https://discuss.elastic.co/t/unable-to-run-sql-query-on-multi-fields-using-elastic-search-v-7-3/222321 "2020-03-05T14:45:58Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Prince.Tyagi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/prince.tyagi/32/63898_2.png) [@Prince.Tyagi](https://discuss.elastic.co/u/Prince.Tyagi)\
**Post date:** [March 5, 2020, 2:45pm UTC](https://discuss.elastic.co/t/unable-to-run-sql-query-on-multi-fields-using-elastic-search-v-7-3/222321/1 "2020-03-05T14:45:58Z")

</div>

Hi Team,

I am getting below error while executing elasticsearch SQL query for translating it to elastic query on multifield:

> ```
> {
> * "error": {
> * "root_cause": [
> * {
> * "type": "verification_exception",
> * "reason": "Found 1 problem(s) line 1:36: [productDescription.KeywordField like '%moringa%'] cannot operate on field of data type [text]: No keyword/multi-field defined exact matches for [KeywordField]; define one or use MATCH/QUERY instead"}],
> * "type": "verification_exception",
> * "reason": "Found 1 problem(s) line 1:36: [productDescription.KeywordField like '%moringa%'] cannot operate on field of data type [text]: No keyword/multi-field defined exact matches for [KeywordField]; define one or use MATCH/QUERY instead"},
> * "status": 400
> }
> 
> ```

I am using Elasticsearch 7.3.0.

Below is the query which I am using for sql translate api -

> ```
> http://localhost:9200/_sql/translate
> {
> "query": "SELECT * FROM \"t3-imp-2019\" where (productDescription.KeywordField like '%moringa%')"
> }
> 
> ```

Below is my index mapping and settings:

> ```
> {
> "settings": {
> "index": {
> "analysis": {
> "normalizer": {
> "lowercase_normalizer": {
> "filter": [
> "lowercase"
> ],
> "type": "custom",
> "char_filter": []
> }
> },
> "analyzer": {
> "keyword-analyser": {
> "filter": [
> "lowercase"
> ],
> "type": "custom",
> "tokenizer": "keyword"
> }
> }
> }
> }
> },
> "mappings": {
> "_doc": {
> "properties": {
> "productDescription": {
> "analyzer": "english",
> "type": "text",
> "fields": {
> "KeywordField": {
> "analyzer": "keyword-analyser",
> "type": "text"
> }
> }
> }
> }
> }
> }
> }
> 
> ```

According to error if I am trying to convert productDescription field type to "keyword", then it is giving below error:

> ```
> {
> * "error": {
> * "root_cause": [
> * {
> * "type": "mapper_parsing_exception",
> * "reason": "no handler for type [Keyword] declared on field [KeywordField]"}],
> * "type": "mapper_parsing_exception",
> * "reason": "Failed to parse mapping [_doc]: no handler for type [Keyword] declared on field [KeywordField]",
> * "caused_by": {
> * "type": "mapper_parsing_exception",
> * "reason": "no handler for type [Keyword] declared on field [KeywordField]"}},
> * "status": 400
> }
> 
> ```

---

<div class="post-metadata">

**Author:** ![matriv](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/matriv/32/43656_2.png) [@matriv](https://discuss.elastic.co/u/matriv)\
**Post date:** [March 5, 2020, 5:28pm UTC](https://discuss.elastic.co/t/unable-to-run-sql-query-on-multi-fields-using-elastic-search-v-7-3/222321/2 "2020-03-05T17:28:26Z")

</div>

You are trying to use `KeywordField` which is of type `text`.  
The `LIKE` operator works only on fields of type `keyword`.  
If you still need the `KeywordField` analyzed with the `keyword-analyzer`  
you need to add an extra field `keyword` of `"type" : "keyword" and use that in your query:

```auto
SELECT * FROM \"t3-imp-2019\" where (productDescription.keyword like '%moringa%'

```

Of course this means that your `keyword` field will contain the whole String and won't be split up  
to words by any analyzer.

---

<div class="post-metadata">

**Author:** ![Prince.Tyagi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/prince.tyagi/32/63898_2.png) [@Prince.Tyagi](https://discuss.elastic.co/u/Prince.Tyagi)\
**Post date:** [March 6, 2020, 11:41am UTC](https://discuss.elastic.co/t/unable-to-run-sql-query-on-multi-fields-using-elastic-search-v-7-3/222321/3 "2020-03-06T11:41:50Z")

</div>

OK,

It's working fine for me.

Thanks

---

<div class="post-metadata">

**Author:** ![Prince.Tyagi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/prince.tyagi/32/63898_2.png) [@Prince.Tyagi](https://discuss.elastic.co/u/Prince.Tyagi)\
**Post date:** [March 6, 2020, 11:43am UTC](https://discuss.elastic.co/t/unable-to-run-sql-query-on-multi-fields-using-elastic-search-v-7-3/222321/4 "2020-03-06T11:43:46Z")

</div>

Here is the sample code to add a new keyword field in the existing metadata:

> ```
> {
> "properties": {
> "productDescription": {
> "analyzer": "english",
> "type": "text",
> "fields": {
> "AutocompleteField": {
> "analyzer": "autocomplete-analyser",
> "type": "text"
> },
> "KeywordField2": {
> "type": "keyword"
> },
> "KeywordField3": {
> "type": "keyword"
> },
> "KeywordField": {
> "analyzer": "keyword-analyser",
> "type": "text"
> },
> "SnowField": {
> "analyzer": "snowball-analyzer",
> "type": "text"
> },
> "StandardField": {
> "analyzer": "standard-analyser",
> "type": "text"
> }
> }
> }
> }
> }
> 
> ```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [April 3, 2020, 11:43am UTC](https://discuss.elastic.co/t/unable-to-run-sql-query-on-multi-fields-using-elastic-search-v-7-3/222321/5 "2020-04-03T11:43:54Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.