# Elasticsearch search document with nested document with optional fields

**URL:** <https://discuss.elastic.co/t/elasticsearch-search-document-with-nested-document-with-optional-fields/301604>\
**Category:** Elasticsearch\
**Created:** [April 5, 2022, 8:36am UTC](https://discuss.elastic.co/t/elasticsearch-search-document-with-nested-document-with-optional-fields/301604 "2022-04-05T08:36:03Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![kemot90](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kemot90/32/103954_2.png) [@kemot90](https://discuss.elastic.co/u/kemot90)\
**Post date:** [April 5, 2022, 8:36am UTC](https://discuss.elastic.co/t/elasticsearch-search-document-with-nested-document-with-optional-fields/301604/1 "2022-04-05T08:36:03Z")

</div>

I try to create query for nested object that contains year and month. Both of them are optional. If some field not exists we treat them as hit. I found one solution but it causes combinatorial explosion of terms so I'm trying to find a better solution.

Steps of reproduction:

1. Creating index with mapping

```auto
PUT /date-test
{
    "mappings": {
        "properties": {
            "datesOfBirth": {
                "type": "nested"
            }
        }
    }
}

```

1. Add documents with nested objects

```auto
PUT /date-test/_doc/1
{
    "name": "Object1",
    "datesOfBirth": []
}

```

```auto
PUT /date-test/_doc/2
{
    "name": "Object2",
    "datesOfBirth": [
        {
            "year": 1990,
            "month": 4
        }
    ]
}

```

```auto
PUT /date-test/_doc/3
{
    "name": "Object3",
    "datesOfBirth": [
        {
            "year": 1995,
            "month": 2
        },
        {
            "year": 1998,
            "month": 4
        }
    ]
}

```

```auto
PUT /date-test/_doc/4
{
    "name": "Object4",
    "datesOfBirth": [
        {
            "month": 4
        }
    ]
}

```

1. This query works as expected for year range 1994-1996 and month range 1-5 (objects 1, 3, 4 are returned):

```auto
{
    "size": 1000,
    "query": {
        "bool" : {
            "should": [
                { "bool": {"must_not": [ //match when all fields are absent
                        { "nested": { "path": "datesOfBirth", "query": { "exists": { "field": "datesOfBirth.year" }} }},
                        { "nested": { "path": "datesOfBirth", "query": { "exists": { "field": "datesOfBirth.month" }} }}
                    ]
                }},
                { "bool": {"must_not": [ //match when year is absent but month exists and match to range
                        { "nested": { "path": "datesOfBirth", "query": { "exists": { "field": "datesOfBirth.year" }} }}
                    ],
                    "should": [
                        {"nested": { "path": "datesOfBirth", "query": { "bool": { "must": [
                            { "range": { "datesOfBirth.month": { "gte": 1, "lte": 5} } }
                        ]
                        }}}}
                    ]
                }},
                { "bool": {"must_not": [ //match when month is absent but year exists and match to range
                        { "nested": { "path": "datesOfBirth", "query": { "exists": { "field": "datesOfBirth.month" }} }}
                    ],
                    "should": [
                        {"nested": { "path": "datesOfBirth", "query": { "bool": { "must": [
                            { "range": { "datesOfBirth.year": { "gte": 1994, "lte": 1996} } }
                        ]
                        }}}}
                    ]
                }},
                {"nested": { "path": "datesOfBirth", "query": { "bool": { "must": [ //both fields exists and must match to given ranges
                    { "range": { "datesOfBirth.year": { "gte": 1994, "lte": 1996} } },
                    { "range": { "datesOfBirth.month": { "gte": 1, "lte": 5} } }
                ]
                }}}}
            ],
            "minimum_should_match": 1
        }
    }
}

```

Is there better way to achieve that behaviour? I'm using Elasticsearch 7.1.

---

<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 5, 2022, 8:36am UTC](https://discuss.elastic.co/t/elasticsearch-search-document-with-nested-document-with-optional-fields/301604/2 "2022-04-05T08:36:03Z")

</div>

Elasticsearch 7.1 is [EOL](https://www.elastic.co/support/eol) and no longer supported. Please upgrade ASAP.

(This is an automated response from your friendly Elastic bot. Please report this post if you have any suggestions or concerns :elasticheart: )

---

<div class="post-metadata">

**Author:** ![kemot90](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kemot90/32/103954_2.png) [@kemot90](https://discuss.elastic.co/u/kemot90)\
**Post date:** [April 5, 2022, 10:21am UTC](https://discuss.elastic.co/t/elasticsearch-search-document-with-nested-document-with-optional-fields/301604/3 "2022-04-05T10:21:03Z")

</div>

I've also tried always set field but with null in case of value absence and add mapping for year and month where I define null\_value: -1. Then I can remove part with combination of field absence.

1. Create index with mapping

```auto
PUT /date-test
{
    "mappings": {
        "properties": {
            "datesOfBirth": {
                "type": "nested",
                "properties": {
                    "year": { "type": "integer", "null_value": -1 },
                    "month": { "type": "integer", "null_value": -1 }
                }
            }
        }
    }
}

```

1. Creating documents as follows:

```auto
PUT /date-test/_doc/7
{
    "name": "SomeObjectWithoutYear",
    "datesOfBirth": [
        {
            "year": null,
            "month": 4
        }
    ]
}

```

Then I can do query like this:

```auto
POST /date-test/_search
{
    "size": 1000,
    "query": {
        "bool" : {
            "should": [
                { "bool": {"must_not": [
                        { "nested": { "path": "datesOfBirth", "query": { "exists": { "field": "datesOfBirth.year" }} }},
                        { "nested": { "path": "datesOfBirth", "query": { "exists": { "field": "datesOfBirth.month" }} }}
                    ]
                }},
                {"nested": { "path": "datesOfBirth", "query": { "bool": { "should": [
                    { "match": { "datesOfBirth.year": { "query": -1 } } },
                    { "match": { "datesOfBirth.month": { "query": -1 } } },
                    { "range": { "datesOfBirth.year": { "gte": 1994, "lte": 1996} } },
                    { "range": { "datesOfBirth.month": { "gte": 1, "lte": 5} } }
                ],
                "minimum_should_match": 2
                }}}}
            ],
            "minimum_should_match": 1
        }
    }
}

```

But I'm wondering if it is the cleanest way to achieve that.

---

<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:** [May 3, 2022, 10:21am UTC](https://discuss.elastic.co/t/elasticsearch-search-document-with-nested-document-with-optional-fields/301604/4 "2022-05-03T10:21:29Z")

</div>

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