Elasticsearch equivalent SQL In Query with multiple fields

Question

I am looking for a Elasticsearch equivalent SQL In query like below

SELECT * from table
WHERE (section , class) in (('a','1'), ('b', '2'))

I know how to use In query for single fields in elasticsearch

SELECT * FROM table WHERE class IN ('1', '2');

Elasticsearch query -

{
  "query" : {
    "bool" : {
      "filter" : {
        "terms" : {
          "class" : ['1', '2']
        }
      }
    }
  }
}

My actual problem statement -

Sample index data :

[
{
    "_index" : "some_index",
    "_type" : "_doc",
    "_id" : "41",
    "_score" : 1.0,
    "_source" : {
      "class" : "1",
      "section" : "a",
      "attribute_3" : "hello world"
},
{
    "_index" : "some_index",
    "_type" : "_doc",
    "_id" : "42",
    "_score" : 1.0,
    "_source" : {
      "class" : "2",
      "section" : "a",
      "attribute_3" : "hello world"
},
{
    "_index" : "some_index",
    "_type" : "_doc",
    "_id" : "43",
    "_score" : 1.0,
    "_source" : {
      "class" : "1",
      "section" : "b",
      "attribute_3" : "hello world"
},
{
    "_index" : "some_index",
    "_type" : "_doc",
    "_id" : "44",
    "_score" : 1.0,
    "_source" : {
      "class" : "2",
      "section" : "b",
      "attribute_3" : "hello world"
},
{
    "_index" : "some_index",
    "_type" : "_doc",
    "_id" : "45",
    "_score" : 1.0,
    "_source" : {
      "class" : "3",
      "section" : "b",
      "attribute_3" : "hello world"
}
]

I want to use a filter on data where (class is 1 AND section is a) OR (class is 2 AND section is b) Note : I'm preparing this 'OR' combination dynamically and its going to be more than two combinations.

My expected search result should be -

[{
    "_index" : "some_index",
    "_type" : "_doc",
    "_id" : "41",
    "_score" : 1.0,
    "_source" : {
      "class" : "1",
      "section" : "a",
      "attribute_3" : "hello world"
},
{
    "_index" : "some_index",
    "_type" : "_doc",
    "_id" : "44",
    "_score" : 1.0,
    "_source" : {
      "class" : "2",
      "section" : "b",
      "attribute_3" : "hello world"
}]

Nishant · Accepted Answer · 2020-11-19 07:33:15Z

2

This would translate to:

{
  "query": {
    "bool": {
      "should": [
        {
          "bool": {
            "must": [
              {
                "term": {
                  "a": 0
                }
              },
              {
                "term": {
                  "b": 9
                }
              }
            ]
          }
        },
        {
          "bool": {
            "must": [
              {
                "term": {
                  "a": 0
                }
              },
              {
                "term": {
                  "b": 4
                }
              }
            ]
          }
        }
      ]
    }
  }
}

but if a is always 0 as you mentioned in the example the query can be rephrased to:

{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "a": 0
          }
        },
        {
          "terms": {
            "b": [
              9,
              4
            ]
          }
        }
      ]
    }
  }
}

answered Nov 19, 2020 at 7:33

Nishant

7,9121 gold badge24 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Arnab_Ghosh Over a year ago

Hi i have edited my question. The query is not static i am dynamically constructing this and it's going to be more than just 2 combinations. As i asked in my question i am looking similar to SQL In query, not an OR query like below select * from table where (("class" == "0") and ("section" == "9")) or (("class" == "0") and ("section" == "4")) ...

Nishant Over a year ago

Yo can programmatically add as many combination as you like. Definitely the query would look too big, but thats how it would translate.

Arnab_Ghosh Over a year ago

Thanks Nishant ! 👍🏼

Nishant Over a year ago

@Arnab_Ghosh Please upvote and accept the answer if it helped.

Arnab_Ghosh Over a year ago

@Nishant I have accepted and upvoted too, but since my reputation is less than 15, it isn't visible..

Collectives™ on Stack Overflow

Elasticsearch equivalent SQL In Query with multiple fields

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related