SQL IN clause equivalent

Elastic Stack Elasticsearch
1

In this query:

SELECT id FROM tbl WHERE id IN (1,2,3)

Clearly, using the _xpack/sql/translate endpoint this will error in that the IN clause is not supported.

What would be the equivalent SQL statement for a query above? I've been searching, and there's nothing in the ES documentation.

Any pointers would be much appreciated.

2

Hi David,

You might want to try the following filter query:

{
  "query" : {
    "bool" : {
      "filter" : {
        "terms" : {
          "id" : [1,2,3]
        }
      }
    }
  }
}
3

Hello!
how would you express this in SQL? I am creating my query as a dynamic SQL string and the "IN" clause is not supported by the sql endpoint

4

I am afraid this is not (yet?) supported (see SELECT). The full power of Elaticsearch is really only exposed via the Elasticsearch Query DSL. Also, I wouldn't bet on the fact that this will change anytime soon. Even though it is possible to use an SQL-like query syntax in a number of cases, Elasticsearch is not an SQL database and thus it will not be able to support all SQL operators.

5

Yeah, totally understand. i think for those fields which are list-like i'll have to use a combination of SQL and the DSL, which is fine. not as clean as i would have preferred, but that's how it is

thanks for the help!

6

Hi @David_Palmer,
The IN statement is on our to-do list, but for the moment you could do the same thing (though, more painful I admit) using a list of multiple OR statements:

SELECT id FROM tbl WHERE id=1 OR id=2 OR id=3;

Also, small note here: if id is really the Elasticsearch _id meta field, we are not exposing that field. At least, not for now (there were discussion about it in the past). You'd need to define your own id field and populate it when indexing the documents.

7

@Andrei_Stefan -- thank you! Yes i considered this. but ultimately, i just ditched the SQL route and have gone with using the java Builders. While not my first choice, I'm finding them quite easy to work with and can perform all of the functions (obviously) that i need to make these queries dynamic.

Thank you for the help!

8

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