Translated SQL query to SearchRequest

Elastic Stack Elasticsearch
1

Hello
I want to create a search request from a translated SQL query.
Here is the code that i use to translate the SQL query to json ES query:

RestClient restClient = RestClient.builder(
            new HttpHost("localhost",  9200, "http")).build();
     
    Request request = new Request("POST",  "/_sql/translate");
    request.setJsonEntity("{\"query\":\"SELECT * FROM pdo limit 10\"}");
    Response response = restClient.performRequest(request);
    String responseBody = EntityUtils.toString(response.getEntity()); 
    restClient.close();

I would like to create a searchRequest object and execute a search based on the translated SQL.
The SearchSourceBuilderis created like the following:

SearchSourceBuilder searchRequest = new SearchSourceBuilder();
searchRequest.query( here should be a QueryBuilder object )

Please help to convert a translated SQL response to a QueryBuilder object or maybe there any other way to create a SearchSourceBuilder that will be based on the full JSON ES query

Thanks
Yuriy

2

You should instead translate the request in Kibana Dev Console or using curl. And then transform the request to an actual Java Request.

For example, run:

DELETE pdo
POST pdo/_doc
{
  "foo": "bar"
}
POST _sql/translate
{
  "query": "SELECT * FROM pdo WHERE foo='bar' LIMIT 10"  
}

This gives:

{
  "size" : 10,
  "query" : {
    "term" : {
      "foo.keyword" : {
        "value" : "bar",
        "boost" : 1.0
      }
    }
  },
  "_source" : {
    "includes" : [
      "foo"
    ],
    "excludes" : [ ]
  },
  "sort" : [
    {
      "_doc" : {
        "order" : "asc"
      }
    }
  ]
}

From which I'd remove all the default values to keep only the significant part:

GET pdo/_search
{
  "query" : {
    "term" : {
      "foo.keyword" : {
        "value" : "bar"
      }
    }
  }
}

Which can be translated to Java to something like:

client.search(new SearchRequest("pdo").source(
        new SearchSourceBuilder().query(
                QueryBuilders.termQuery("foo.keyword", "bar")
        )
), RequestOptions.DEFAULT);
3

If you really want the LIMIT 10 you need to include the:

"size" : 10,
4

Well. By default the size is 10 :smirk:

5

Hi David and Marios.
Thanks for the reply.
The way that you suggest is not simple because it requires to perform a string manipulation on the response.
So my question is what is the best practice to enrich the _sql/translate response by an additional data and use it to create a high level client search?

Actually the requirement is to first get SQL converted to ES native query and then combine it with existing Native query and then execute it as a whole

Thanks

6

You can't do that I'm afraid.

The closest thing you can do is to get the "query" part and use it within a wrapperQuery.
You can also do that for some other fields like size, from but it's harder for some others like sort.

I wrote an example at

Hope this helps.

7

I was wondering if you'd be willing to detail on the task at hand, maybe we have some alternative suggestions? I'm not clear as to why you'd want to have the SQL translation and then execute that, rather than simply execute it through the SQL API.
What kind of extra DSL manipulation would do you need to perform that SQL might not be able to do?

You could potentially provide extra filtering DSL to the SQL API using the filter parameter.

1 Like
8

David, thank you very much your suggestion is worked fine for me.
Thanks a lot

10

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