SQL translates to wrong DSL query

[shivmgs (Shiva)]

We have a field company_name of type text. For the same field, we have additional .raw field - company_name.raw which is a keyword field.

For the following SQL query, search on company_name

{
    "query": "SELECT \"company_name\" AS \"column3\",COUNT( \"cursor\" ) AS \"column2\"FROM \"jobspikr_v3_data\" AS \"440b2537c24d29841e7443fdcdf254\"WHERE ( ( (\"company_name\" LIKE '%info%'))) GROUP BY \"company_name\" ORDER BY \"column3\" ASC LIMIT 101",
    "fetch_size": 10
}

Its being translated to the following DSL, where search is applied on .raw field.

{
    "size": 0,
    "query": {
        "wildcard": {
            "company_name.raw": {
                "wildcard": "*info*",
                "boost": 1.0
            }
        }
    },
    "_source": false,
    "stored_fields": "_none_",
    "aggregations": {
        "groupby": {
            "composite": {
                "size": 10,
                "sources": [
                    {
                        "136613": {
                            "terms": {
                                "field": "company_name.raw",
                                "missing_bucket": true,
                                "order": "asc"
                            }
                        }
                    }
                ]
            },
            "aggregations": {
                "136626": {
                    "filter": {
                        "exists": {
                            "field": "cursor",
                            "boost": 1.0
                        }
                    }
                }
            }
        }
    }
}

Is there a way to restrict the search on the company_name field(not .raw) field?

[dadoonet (David Pilato)]

Welcome!

A guess would be to change the type of the company field to be a keyword field.

But what is wrong with the current behavior?

[shivmgs (Shiva)]

HI David,

The company_name.raw field is not a searchable field. Its used only for aggregation and sorting. company_name field is a text field, which is used for all the searching. Here in the SQL we are trying to use the company_name field only, but it translates to company_name.raw automatically. Here we are getting responses like these

"type": "query_shard_exception",
"reason": "failed to create query: {\n  \"wildcard\" : {\n    \"company_name.raw\" : {\n      \"wildcard\" : \"*info*\",\n      \"boost\" : 1.0\n    }\n  }\n}",

[dadoonet (David Pilato)]

Could you provide a full recreation script as described in About the Elasticsearch category. It will help to better understand what you are doing. Please, try to keep the example as simple as possible.

A full reproduction script is something anyone can copy and paste in Kibana dev console, click on the run button to reproduce your use case. It will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.

Also share the full error please.

[Andrei_Stefan]

This is documented here.