CODEWITHSUNDEEP
sqlelasticsearch
nesh_s
nesh_s

Reputation: 399

Elasticsearch query (not filter) with multiple fields and conditions

I have the following SQL query:

SELECT * FROM
 table WHERE
 area IN  ('UK', 'US', 'France') 
 AND rating IN ('GOOD', 'BAD','AVG') 
 AND active = 'YES'

I want to convert it to an elastic query.

Please see my attempt at using "filter" below:

{
  "query" : {
    "filtered" : {
      "query" : {
        "match_all" : {}
      },
      "filter" : {
        "bool" : {
          "must" : [{
              "terms" : {
                "area" : [
                  "UK",
                  "US",
                  "France"
                ]
              }
            }, 
            {
              "terms" : {
                "rating" : [
                  "GOOD",
                  "BAD",
                  "AVG"
                ]
              }
            }, 
            {
              "term" : {
                "active" : "YES"
              }
            }
          ]
        }
      }
    }
  }
}

However this doesnt help me with the score because of all these matches being inside filter. Is there a way to move all these "must" matches to a "query" so that I can get scored results?

I am specifically looking a way to convert the SQL query I have above to elastic search query with all conditions inside a "query" instead of "filter"

Thanks very much.

Upvotes: 0

Views: 556

Answers (2)

Richa
Richa

Reputation: 7649

You can use bool query instead of bool filter. Your query will get converted to:

{
 "query": {
  "bool": {
     "must" : [{
          "terms" : {
            "area" : [
              "UK",
              "US",
              "France"
            ]
          }
        }, 
        {
          "terms" : {
            "rating" : [
              "GOOD",
              "BAD",
              "AVG"
            ]
          }
        }, 
        {
          "term" : {
            "active" : "YES"
          }
        }
      ]
    }
   }
  }

Now all the conditions are in query not in filter.Hope it helps.

Upvotes: 1

Val
Val

Reputation: 217254

You're on the right track, you can simply move your bool/must array inside the query and remove the filtered query altogether:

{
  "query": {
    "bool": {
      "must": [
        {
          "terms": {
            "area": [
              "UK",
              "US",
              "France"
            ]
          }
        },
        {
          "terms": {
            "rating": [
              "GOOD",
              "BAD",
              "AVG"
            ]
          }
        },
        {
          "term": {
            "active": "YES"
          }
        }
      ]
    }
  }
}

Upvotes: 0

Related Questions