Elasticsearch query (not filter) with multiple fields and conditions

Question

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.

but you are working with exact values, does it make sense to score the documents? — Igor Belo
– Igor Belo, Commented Jan 27, 2016 at 11:32
i am not sure i completely understand. do you mean that since i am matching on exact values, there can only be results coming back which match all conditions, and no results which partially match say, the area and active fields and have no match in ratings field? which would mean all results will be scored same? — nesh_s
– nesh_s, Commented Jan 27, 2016 at 11:35
by your query all fields MUST match, so if a document matches area and rating fields and doesn't match active field, this doc will not be retrieved. What I mean is: what is the difference between doc("UK", "GOOD", "YES") and doc("France", "AVG", "YES")? Both match but working with exact values I see no difference between them, it's a binary operation (0/1). — Igor Belo
– Igor Belo, Commented Jan 27, 2016 at 11:53
agreed. you are right. would you then advise using "filter" and not bothering with "query". also would you advise against using "query" in case i was to do a wildcard search as well? — nesh_s
– nesh_s, Commented Jan 27, 2016 at 12:53

Richa · Accepted Answer · 2016-01-27 11:25:01Z

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.

Val · Accepted Answer · 2016-01-27 11:21:18Z

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"
          }
        }
      ]
    }
  }
}

Collectives™ on Stack Overflow

Elasticsearch query (not filter) with multiple fields and conditions

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related