How to filter with multiple fields and values in elasticsearch?

Question

I've been reading through the docs and been trying to achieve a solution to filter results through multiple fields and columns, however I keep getting errors; malformed query.

I want to filter the result with exact equal values, such as the following:

is_active: true
category_id: [1,2,3,4]
brand: "addidas"
gender: "male"

To make it more clear what I intend to do, this is how I'd like it to run if it would be written in SQL:

SELECT .... WHERE 
is_active= 1 AND category_id IN(1,2,3,4) 
AND brand='addidas' AND gender='male'

My query in DSL goes as following:

{
    "body": {
        "query": {
            "nested": {
                "query": {
                    "bool": {
                        "must": {
                            "terms": {
                                "category_id": [
                                    1,
                                    2,
                                    3
                                ]
                            },
                            "term": {
                                "is_active": true
                            },
                            "term": {
                                "brand": "addidas"
                            }
                        }
                    }
                }
            }
        }
    }
}

How do I filter multiple fileds and values as described, in elasticsearch?

If you need extra information from me that is required to answer the question, leave a comment. If you add a link to the docs, please also provide an example (with query dsl) of how my current, or similar situations should be solved.

Answer 1

Use the following code:

The clause (query) must appear in matching documents and will contribute to the score.

"query": {
    "bool": {
        "must" : [
            {"term" : { "is_active" : true}},
            {"term" : { "gender" : "female"}},
            {"term" : { "brand" : "addidas"}},
            {"terms": { "categoryId": [1,2,3,4]}}
        ]
    }
}

Queries specified under the filter element have no effect on scoring

"query": {
    "bool": {
        "filter" : [
            {"term" : { "is_active" : true}},
            {"term" : { "gender" : "female"}},
            {"term" : { "brand" : "addidas"}},
            {"terms": { "categoryId": [1,2,3,4]}}
        ]
    }
}