Elasticsearch multi term filter

Question

I'm quite new to Elasticsearch, so here's my question. I wanna do a search query with elasticsearch and wanna filter with multiple terms.

If I want to search for a user 'tom', then I would like to have all the matches where the user 'isActive = 1', 'isPrivate = 0' and 'isOwner = 1'.

Here's my search query

"query":{
    "filtered": {
        "query": {
            "query_string": {
                "query":"*tom*",
                "default_operator": "OR",
                "fields": ["username"]
            }
        },
        "filter": {
            "term": { 
                "isActive": "1",
                "isPrivate": "0",
                "isOwner": "1"
            }
        }
    }
}   

When I use 2 terms, it works like a charm, but when i use 3 terms it doesn't.

Thanks for the help!!

Answer 1

You should use bool filter to AND all your terms:

"query":{
    "filtered": {
        "query": {
            "query_string": {
                "query":"*tom*",
                "default_operator": "OR",
                "fields": ["username"]
            }
        },
        "filter": {
            "bool" : {
                "must" : [
                    {"term" : { "isActive" : "1" } },
                    {"term" : { "isPrivate" : "0" } },
                    {"term" : { "isOwner" : "1" } }
                ]
             }
         }
     }
}   

For version 2.x+ you can use bool query instead of filtered query with some simple replacement: https://www.elastic.co/guide/en/elasticsearch/reference/7.4/query-dsl-filtered-query.html

Answer 2

As one of the comments says, the syntax has changed in recent ES versions. If you are using Elasticsearch 6.+, and you want to use a wildcard and a sequence of terms in your query (such as in the question), you can use something like this:

GET your_index/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "wildcard": {
            "your_field_name_1": {
              "value": "tom*"
            }
          }
        },
        {
          "term": {
            "your_field_name_2": {
              "value": "US"
            }
          }
        },
        {
          "term": {
            "your_field_name_3": {
              "value": "Michigan"
            }
          }
        },
        {
          "term": {
            "your_field_name_4": {
              "value": "0"
            }
          }
        }
      ]
    }
  }
}

Also, from the documentation about wildcard queries:

Note that this query can be slow, as it needs to iterate over many terms. In order to prevent extremely slow wildcard queries, a wildcard term should not start with one of the wildcards * or ?.

I hope this helps.