How to do nested AND and OR filters in ElasticSearch?

Question

My filters are grouped together into categories. I would like to retrieve documents where a document can match any filter in a category, but if two (or more) categories are set, then the document must match any of the filters in ALL categories.

If written in pseudo-SQL it would be:

SELECT * FROM Documents WHERE (CategoryA = 'A') AND (CategoryB = 'B' OR CategoryB = 'C')

I've tried Nested filters like so:

{
    "sort": [{
        "orderDate": "desc"
    }],
    "size": 25,
    "query": {
        "match_all": {}
    },
    "filter": {
        "and": [{
            "nested": {
                "path":"hits._source",
                "filter": {
                    "or": [{
                        "term": {
                            "progress": "incomplete"
                        }
                    }, {
                        "term": {
                            "progress": "completed"
                        }
                    }]
                }
            }
        }, {
            "nested": {
                "path":"hits._source",
                "filter": {
                    "or": [{
                        "term": {
                            "paid": "yes"
                        }
                    }, {
                        "term": {
                            "paid": "no"
                        }
                    }]
                }
            }
        }]
    }
}

But evidently I don't quite understand the ES syntax. Is this on the right track or do I need to use another filter?

not the answer to the question: or with term can be done easier with terms. and the default for bool is and. something along filter.bool.must: [ {terms: progress: ["incomplete","complete"] }. {terms: paid: ["yes", "no"]}] might work? — cfrick
– cfrick, Commented Apr 2, 2014 at 14:30
nested queries/filters are used for arrays/lists (depend which language you are familiar with e.g. a = [{'b':1},{'c':2}]). Could you write an example of one of your documents? That would help — Diolor
– Diolor, Commented Apr 2, 2014 at 16:08
cfrick has lead me in the right path. I nested a bunch of 'terms' filters inside an 'and' filter, and it appears to meet my needs. — MHTri
– MHTri, Commented Apr 2, 2014 at 16:26

Geert-Jan · Accepted Answer · 2014-04-03 10:28:27Z

This should be it (translated from given pseudo-SQL)

{
   "sort": [
      {
        "orderDate": "desc"
      }
    ],
    "size": 25,
    "query":
    {
        "filtered":
        {
            "filter":
            {
                "and":
                [
                    { "term": { "CategoryA":"A" } },
                    {
                        "or":
                        [
                            { "term": { "CategoryB":"B" } },
                            { "term": { "CategoryB":"C" } }
                        ]
                    }
                ]
            }
        }
    }
}

I realize you're not mentioning facets but just for the sake of completeness:

You could also use a filter as the basis (like you did) instead of a filtered query (like I did). The resulting json is almost identical with the difference being:

a filtered query will filter both the main results as well as facets
a filter will only filter the main results NOT the facets.

Lastly, Nested filters (which you tried using) don't relate to 'nesting filters' like you seemed to believe, but related to filtering on nested-documents (parent-child)

Diolor · Accepted Answer · 2014-04-02 16:11:53Z

Although I have not understand completely your structure this might be what you need.

You have to think tree-wise. You create a bool where you must (=and) fulfill the embedded bools. Each embedded checks if the field does not exist or else (using should here instead of must) the field must (terms here) be one of the values in the list.

Not sure if there is a better way, and do not know the performance.

{
    "sort": [
        {
            "orderDate": "desc"
        }
    ],
    "size": 25,
    "query": {
        "query": {           #
            "match_all": {}  # These three lines are not necessary
        },                   #
        "filtered": {
            "filter": {
                "bool": {
                    "must": [
                        {
                            "bool": {
                                "should": [
                                    {
                                        "not": {
                                            "exists": {
                                                "field": "progress"
                                            }
                                        }
                                    },
                                    {
                                        "terms": {
                                            "progress": [
                                                "incomplete",
                                                "complete"
                                            ]
                                        }
                                    }
                                ]
                            }
                        },
                        {
                            "bool": {
                                "should": [
                                    {
                                        "not": {
                                            "exists": {
                                                "field": "paid"
                                            }
                                        }
                                    },
                                    {
                                        "terms": {
                                            "paid": [
                                                "yes",
                                                "no"
                                            ]
                                        }
                                    }
                                ]
                            }
                        }
                    ]
                }
            }
        }
    }
}

This is incorrect. must and should are not aliases for and and or. They have different functionality (although there are similarities conceptually)

Collectives™ on Stack Overflow

How to do nested AND and OR filters in ElasticSearch?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related