CODEWITHSUNDEEP
elasticsearchsearch
Flanfl
Flanfl

Reputation: 514

Elasticsearch: Find all parents where all children have a property with a specific value

I have the current schema:

I want to retrieve all parent where all child have their property foo equals to 0

I tried different approach but whatever I do, some parent are retrived while one child has the property foo at 1

Example of my query:

{
    "query": {
        "bool": {
            "must": [
                {
                    "range": {
                        "date": {
                            "lt": "2018-07-05T10:00:00.000Z"
                        }
                    }               
                },
                {
                    "nested": {
                        "path": "child",
                        "query": {
                            "bool": {
                                "must": {
                                    "term": {
                                        "child.foo": 0
                                    }       
                                }
                            }
                        }
                    }
                }
            ]
        }
    }
}

I tried with should, match, range... even must_not/should_not. I also tried filtering without any luck, I keep getting hits with the foo property at 1.

I also tried the aggregation path but I don't understand how to apply it to my need.

EDIT: I looked at the possible duplicate. While it did not answer my question, it put me on the right track. My issue was that I was thinking in a SQL way, with joins and such. While I should have thought in the elastic way.

Thus, what I wanted to do could not be done. What I needed was to look for parent where at least one child had the foo property at 1 or more. Then, ignore these results and take the others. Thus the answer is simple: I just add to change the must of the nested query by a must_not and that was it!

Upvotes: 0

Views: 743

Answers (1)

Flanfl
Flanfl

Reputation: 514

As I explained in the edit of my question, the answer is fairly easy once you start thinking in the right way./ 

{
    "query": {
        "bool": {
            "must": [
                {
                    "range": {
                        "date": {
                            "lt": "2018-07-05T10:00:00.000Z"
                        }
                    }               
                },
                {
                    "nested": {
                        "path": "child",
                        "query": {
                            "bool": {
                                "must_not": {
                                    "term": {
                                        "child.foo": 0
                                    }       
                                }
                            }
                        }
                    }
                }
            ]
        }
    }
}

The trick was just to set a must_not

Upvotes: 1

Related Questions