Query with multiple fields and conditions in ElasticSearch

Question

In ElasticSearch is possible transform this SQL in one query?

SELECT * FROM table WHERE state IN ('NY', 'CA', 'FL') AND grade IN ('GOOD', 'BAD') AND active = 'YES' AND quantity > 100 AND quantity < 200

Russ Cam · Accepted Answer · 2015-02-23 01:03:09Z

1

You could use a filtered query with a bool filter for this

{
  "query" : {
    "filtered" : {
      "query" : {
        "match_all" : {}
      },
      "filter" : {
        "bool" : {
          "must" : [{
              "terms" : {
                "state" : [
                  "NY",
                  "CA",
                  "FL"
                ]
              }
            }, 
            {
              "terms" : {
                "grade" : [
                  "GOOD",
                  "BAD"
                ]
              }
            }, 
            {
              "term" : {
                "active" : "YES"
              }
            }, 
            {
              "range" : {
                "quantity" : {
                  "gt" : 100,
                  "lt" : 200
                }
              }
            }
          ]
        }
      }
    }
  }
}

answered Feb 23, 2015 at 1:03

Russ Cam

126k34 gold badges211 silver badges275 bronze badges

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

2 Comments

Tom Over a year ago

And if quantity is just quantity > 100?

Russ Cam Over a year ago

just remove the lt property on the range filter i.e. { "range" : { "quantity" :{ "gt" : 100 } } }

Collectives™ on Stack Overflow

Query with multiple fields and conditions in ElasticSearch

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related