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

[Prince.Tyagi]

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
}

[matriv (Marios Trivyzas)]

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:

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.

[Prince.Tyagi]

OK,

It's working fine for me.

Thanks

[Prince.Tyagi]

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"
        }
      }
    }
  }
}