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, Feb 05 '18 at 05:00
@Kilise, plz add sample of document that indexed in elasticsearch — Mohammad Akbari, Feb 05 '18 at 05:55

Mohammad Akbari · Accepted Answer · 2020-07-09T13:42:02.070

17

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 09 '20 at 13:42

answered Feb 05 '18 at 06:27

Mohammad Akbari

4,486
6
43
74

Thanks, this works perfect. It was really hard to figure out the position of the objects, even when reading the docs. Merci! – Kilise Feb 05 '18 at 20:13
5

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". – Javier Buzzi Oct 11 '18 at 08:21
1

`must` is an array of conditions, you pass each instance as an object. – Dalibor Karlović Oct 18 '18 at 14:27
@Dalibor Karlović: Very thanks for the comment, I edit the answer – Mohammad Akbari Oct 19 '18 at 02:56
@Javier Buzzi: Very thanks for the comment, I edit the answer – Mohammad Akbari Oct 19 '18 at 02:57
1

This is not a filter, it should affect the ranking, – Radio Controlled Jul 09 '20 at 08:17
@Radio Controlled- Thanx for your attention, I add the filter part to the answer – Mohammad Akbari Jul 09 '20 at 13:43

How to filter with multiple fields and values in elasticsearch?

1 Answers1

Linked