# Translated SQL query to SearchRequest

**URL:** <https://discuss.elastic.co/t/translated-sql-query-to-searchrequest/254087>\
**Category:** Elasticsearch\
**Created:** [November 3, 2020, 12:39am UTC](https://discuss.elastic.co/t/translated-sql-query-to-searchrequest/254087 "2020-11-03T00:39:51Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![yuriy.kasterin](https://avatars.discourse-cdn.com/v4/letter/y/ed655f/32.png) [@yuriy.kasterin](https://discuss.elastic.co/u/yuriy.kasterin)\
**Post date:** [November 3, 2020, 12:39am UTC](https://discuss.elastic.co/t/translated-sql-query-to-searchrequest/254087/1 "2020-11-03T00:39:51Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [November 3, 2020, 3:28pm UTC](https://discuss.elastic.co/t/translated-sql-query-to-searchrequest/254087/2 "2020-11-03T15:28:33Z")

</div>

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:

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

```

This gives:

```auto
{
  "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:

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

```

Which can be translated to Java to something like:

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

```

---

<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:** [November 3, 2020, 6:25pm UTC](https://discuss.elastic.co/t/translated-sql-query-to-searchrequest/254087/3 "2020-11-03T18:25:06Z")

</div>

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

```auto
"size" : 10,

```

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [November 3, 2020, 7:55pm UTC](https://discuss.elastic.co/t/translated-sql-query-to-searchrequest/254087/4 "2020-11-03T19:55:20Z")

</div>

Well. By default the size is 10 😏

---

<div class="post-metadata">

**Author:** ![yuriy.kasterin](https://avatars.discourse-cdn.com/v4/letter/y/ed655f/32.png) [@yuriy.kasterin](https://discuss.elastic.co/u/yuriy.kasterin)\
**Post date:** [November 4, 2020, 7:52am UTC](https://discuss.elastic.co/t/translated-sql-query-to-searchrequest/254087/5 "2020-11-04T07:52:44Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [November 4, 2020, 10:04am UTC](https://discuss.elastic.co/t/translated-sql-query-to-searchrequest/254087/6 "2020-11-04T10:04:10Z")

</div>

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

> <https://github.com/dadoonet/elasticsearch-java-client-demo/blob/cfe29fc9884798191d39c31eae55a4987228c02f/src/test/java/fr/pilato/test/elasticsearch/hlclient/EsClientTest.java#L217-L232>

Hope this helps.

---

<div class="post-metadata">

**Author:** ![bogdan.pintea](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bogdan.pintea/32/45740_2.png) [@bogdan.pintea](https://discuss.elastic.co/u/bogdan.pintea)\
**Post date:** [November 4, 2020, 10:41am UTC](https://discuss.elastic.co/t/translated-sql-query-to-searchrequest/254087/7 "2020-11-04T10:41:06Z")

</div>

> [@yuriy.kasterin](#):
>
> I would like to create a searchRequest object and execute a search based on the translated SQL.

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?

> [@yuriy.kasterin](#):
>
> So my question is what is the best practice to enrich the \_sql/translate response by an additional data

You could potentially provide extra filtering DSL to the SQL API using the `filter` [parameter](https://www.elastic.co/guide/en/elasticsearch/reference/master/sql-rest-filtering.html).

---

<div class="post-metadata">

**Author:** ![yuriy.kasterin](https://avatars.discourse-cdn.com/v4/letter/y/ed655f/32.png) [@yuriy.kasterin](https://discuss.elastic.co/u/yuriy.kasterin)\
**Post date:** [November 4, 2020, 11:20am UTC](https://discuss.elastic.co/t/translated-sql-query-to-searchrequest/254087/8 "2020-11-04T11:20:40Z")

</div>

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

---

<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:** [December 27, 2020, 4:09pm UTC](https://discuss.elastic.co/t/translated-sql-query-to-searchrequest/254087/10 "2020-12-27T16:09:26Z")

</div>

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