# Must clause not working as expected

**URL:** <https://discuss.elastic.co/t/must-clause-not-working-as-expected/356464>\
**Category:** Elasticsearch\
**Created:** [March 29, 2024, 12:48pm UTC](https://discuss.elastic.co/t/must-clause-not-working-as-expected/356464 "2024-03-29T12:48:41Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![jo\_dev](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jo_dev/32/45836_2.png) [@jo\_dev](https://discuss.elastic.co/u/jo_dev)\
**Post date:** [March 29, 2024, 12:48pm UTC](https://discuss.elastic.co/t/must-clause-not-working-as-expected/356464/1 "2024-03-29T12:48:41Z")

</div>

I am executing must clause query on a nested document. I am expecting that it will behave like logical AND, but observing inconsistent behavior.

Sample document mappings-

```auto
"mappings": {
            "properties": {
                "A": {
                    "type": "double"
                },
                "L1": {
                    "type": "nested",
                    "properties": {
                        "L2": {
                            "type": "nested",
                            "properties": {
                                "P": {
                                    "type": "double"
                                },
                                "Q": {
                                    "type": "double"
                                }
                            }
                        },
                        "X": {
                            "type": "double"
                        },
                        "Y": {
                            "type": "double"
                        }
                    }
                }
            }
        }

```

inserting one document -

```auto
{
    "A": 100,
    "L1": {
        "X": 50,
        "Y": 120,
        "L2": [
            {
                "P": 20,
                "Q": 40
            },
            {
                "P": 15,
                "Q": 65
            },
            {
                "P": 19,
                "Q": 40
            }
        ]
    }
}

```

**First query** - search document where **value of P is greater than equal to 16 AND value of P is less than equal to 18**

```auto
{
    "query": {
        "bool": {
            "filter": [
                {
                    "bool": {
                        "must": [
                            {
                                "nested": {
                                    "path": "L1",
                                    "query": {
                                        "bool": {
                                            "filter": [
                                                {
                                                    "nested": {
                                                        "path": "L1.L2",
                                                        "query": {
                                                            "bool": {
                                                                "filter": [
                                                                    {
                                                                        "range": {
                                                                            "L1.L2.P": {
                                                                                "gte": 16
                                                                            }
                                                                        }
                                                                    }
                                                                ]
                                                            }
                                                        }
                                                    }
                                                }
                                            ]
                                        }
                                    }
                                }
                            },
                            {
                                "nested": {
                                    "path": "L1",
                                    "query": {
                                        "bool": {
                                            "filter": [
                                                {
                                                    "nested": {
                                                        "path": "L1.L2",
                                                        "query": {
                                                            "bool": {
                                                                "filter": [
                                                                    {
                                                                        "range": {
                                                                            "L1.L2.P": {
                                                                                "lte": 18
                                                                            }
                                                                        }
                                                                    }
                                                                ]
                                                            }
                                                        }
                                                    }
                                                }
                                            ]
                                        }
                                    }
                                }
                            }
                        ]
                    }
                }
            ]
        }
    }
}

```

> _ **Document doesn't satisfy this query, but still document is getting returned.** _

**Second query** - search document where **value of P is greater than equal to 16**  **AND**  **value of Q is less than equal to 35**

```auto
{
    "query": {
        "bool": {
            "filter": [
                {
                    "bool": {
                        "must": [
                            {
                                "nested": {
                                    "path": "L1",
                                    "query": {
                                        "bool": {
                                            "filter": [
                                                {
                                                    "nested": {
                                                        "path": "L1.L2",
                                                        "query": {
                                                            "bool": {
                                                                "filter": [
                                                                    {
                                                                        "range": {
                                                                            "L1.L2.P": {
                                                                                "gte": 16
                                                                            }
                                                                        }
                                                                    }
                                                                ]
                                                            }
                                                        }
                                                    }
                                                }
                                            ]
                                        }
                                    }
                                }
                            },
                            {
                                "nested": {
                                    "path": "L1",
                                    "query": {
                                        "bool": {
                                            "filter": [
                                                {
                                                    "nested": {
                                                        "path": "L1.L2",
                                                        "query": {
                                                            "bool": {
                                                                "filter": [
                                                                    {
                                                                        "range": {
                                                                            "L1.L2.Q": {
                                                                                "lte": 35
                                                                            }
                                                                        }
                                                                    }
                                                                ]
                                                            }
                                                        }
                                                    }
                                                }
                                            ]
                                        }
                                    }
                                }
                            }
                        ]
                    }
                }
            ]
        }
    }
}

```

> **This query works as expected and doesn't return any document**

can someone explain what is wrong with the first query? What is the reason behind Elasticsearch returning the document though it doesn't match provided criteria?

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [March 29, 2024, 1:08pm UTC](https://discuss.elastic.co/t/must-clause-not-working-as-expected/356464/2 "2024-03-29T13:08:31Z")

</div>

As you have two separate nested clauses under the top `must` I believe these will be evaluated for all sub-documents independently. In the first query there are different nested documents that fulfil both clauses (`15` \< `18` and `19` \> `16`) and the indexed document therefore match. In the second example there is no sub-document that has `Q` less than `35` and all sub-documents therefore fail that clause.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [April 26, 2024, 1:09pm UTC](https://discuss.elastic.co/t/must-clause-not-working-as-expected/356464/3 "2024-04-26T13:09:14Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.