0

Want to know the equivalent elasticsearch query for the below sql query?

SELECT * FROM table1 where val1 in (SELECT val1 FROM table1 WHERE val2 = "123");

How to achieve this in an effiecient way?

One way is to fetch all val1 in 1st Elasticsearch query and with the val1 values fetch all values in the 2nd Elasticsearch query. Is there any other way with which we can get the results in a single Elasticsearch query instead of two Elasticsearch query

Kulasangar
  • 9,046
  • 5
  • 51
  • 82
aham
  • 137
  • 11

1 Answers1

0

You could have your query as such assuming that your heading towards a HTTP POST request.

Request:

http://localhost:9200/yourindex/_search

Request Body:

{
   "query": {
       "query_string": {
           "query": "val1:(val2:\"123\")"
       }
   }
}

Instead of using the IN keyword, you could go with the : symbol in ES OR you could still use the Terms Query . This SO & this thread could be helpful.

EDIT

Using the terms query:

{
  "query" : {
    "bool" : {
      "filter" : {
        "terms" : {
          "val1" : [ "val2" : ["123"]]
        }
      }
    }
  }
}
Community
  • 1
  • 1
Kulasangar
  • 9,046
  • 5
  • 51
  • 82
  • This gives response only for the inner sub query SELECT val1 FROM table1 WHERE val2 = "123" and not the entire query – aham Feb 15 '17 at 09:38
  • @MahalakshmiRamanathan Did you try using the `terms` query? I've edited the answer. – Kulasangar Feb 15 '17 at 10:12