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.

Please show the full error you're getting and a sample document that you need to be matched. Also I think the solution is probably to simply remove the nested query. — Val
– Val, Commented Feb 5, 2018 at 5:00
@Kilise, plz add sample of document that indexed in elasticsearch — Mohammad Akbari
– Mohammad Akbari, Commented Feb 5, 2018 at 5:55

Mohammad Akbari · Accepted Answer · 2020-07-09 13:42:02Z

20

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

edited Jul 9, 2020 at 13:42

answered Feb 5, 2018 at 6:27

Mohammad Akbari

4,8146 gold badges52 silver badges80 bronze badges

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

7 Comments

Kilise Over a year ago

Thanks, this works perfect. It was really hard to figure out the position of the objects, even when reading the docs. Merci!

Javier Buzzi Over a year ago

This doesn't work at all. This only does a filter on the last item "categoryId", all others: "is_active", "gender", and "brand" are ignored. Why? Because they're under the same object, and JSON.. and every other language I know off, can only have a single key, and you have "must" 4 times under "bool".

Dalibor Karlović Over a year ago

must is an array of conditions, you pass each instance as an object.

Mohammad Akbari Over a year ago

@Dalibor Karlović: Very thanks for the comment, I edit the answer

Radio Controlled Over a year ago

This is not a filter, it should affect the ranking,

|

Collectives™ on Stack Overflow

How to filter with multiple fields and values in elasticsearch?

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related