Query migration to SQL - simplify query

As Elasticsearch implements SQL-like queries, i'm trying to migarate existing one using Elasticsearch SQL.

Source query:

{
  "query": {
    "bool": {
      "must": [
        {
          "multi_match": {
            "query": "foofoo bar",
            "fields": [
              "desc^4",
              "desc.ngram^3"
            ],
            "operator": "AND"
          }
        }
      ],
      "should": [
        {
          "match": {
            "user_id": {
              "query": "222",
              "boost": "2"
            }
          }
        }
      ]
    }
  }
}

I've tried to replicate this to Query String Query, that can be passed to QUERY() inside SQL query and ended with:
+((+desc:(foofoo )^3 +desc:(bar)^3) | (+desc.ngram:(foofoo )^3 +desc.ngram:(bar)^3)) user_id:(222)^2

It gives the same result as using normal bool query, but is there any easier way to use it inside SQL query?

Hi @pdd,
Sorry for the delay here. Shortly put, I don't think there is a different way than the one you used.

In SQL, whenever we have an AND or an OR we build a bool query and we either use must statements (for AND) or should statements (for OR). And this is the [SQL_feature] -> [Elasticsearch_feature] kind of approach where we implement an sql feature in Elasticsearch.

The way you are looking at this issue, though, is taking an Elasticsearch feature and translate it into SQL world, more or less.
Thus, this is not so straight forward and you can only do this with the ES specific features in ES-SQL, such as MATCH() and QUERY() functions.

1 Like

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