# Filter by with missing doc/fields in nested docs

**URL:** <https://discuss.elastic.co/t/filter-by-with-missing-doc-fields-in-nested-docs/199449>\
**Category:** Elasticsearch\
**Created:** [September 13, 2019, 2:44pm UTC](https://discuss.elastic.co/t/filter-by-with-missing-doc-fields-in-nested-docs/199449 "2019-09-13T14:44:23Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![antonkallenberg](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/antonkallenberg/32/6259_2.png) [@antonkallenberg](https://discuss.elastic.co/u/antonkallenberg)\
**Post date:** [September 13, 2019, 2:44pm UTC](https://discuss.elastic.co/t/filter-by-with-missing-doc-fields-in-nested-docs/199449/1 "2019-09-13T14:44:24Z")

</div>

Hi guys,

Is it possible to filter documents on missing docs/fields in nested documents?

**Example**  
I have documents which looks like this (lastSessions is nested):

```
[{
	"id": "41853",
	"lastSessions": [{
		"guid": "0278A47B-4B16-4487-A797-5666BF2BA522",
		"periodId": 344,
		"state": "Started"
	}]
}, {
	"id": "41854",
	"lastSessions": [{
			"guid": "0278A47B-4B16-4487-A797-5666BF2BA522",
			"periodId": 344,
			"state": "Started"
		},
		{
			"guid": "0278A47B-4B16-4487-A797-5666BF2BA522",
			"periodId": 343,
			"state": "Abandoned"
		}
	]
}]

```

If I want to find all docs which has "last session state" set to "started" for period 344, I would do something like:

```
{
	"query": {
		"bool": {
			"must": [{
				"nested": {
					"path": "lastSessions",
					"query": {
						"bool": {
							"must": [{
									"term": {
										"periodId": {
											"value": 344
										}
									}
								},
								{
									"terms": {
										"lastSessions.state": [
											"Started"
										]
									}
								}
							]
						}
					}
				}
			}]
		}
	}
}

```

but... what if I want to find all docs which doesn't have any session for period 343 (i.e the document 41853) 🙂? Is it possible?

Regards,  
Anton

---

<div class="post-metadata">

**Author:** ![antonkallenberg](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/antonkallenberg/32/6259_2.png) [@antonkallenberg](https://discuss.elastic.co/u/antonkallenberg)\
**Post date:** [September 16, 2019, 8:18am UTC](https://discuss.elastic.co/t/filter-by-with-missing-doc-fields-in-nested-docs/199449/2 "2019-09-16T08:18:27Z")

</div>

Anyone? 🙂

---

<div class="post-metadata">

**Author:** ![Mikhail\_Khludnev](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mikhail_khludnev/32/59591_2.png) [@Mikhail\_Khludnev](https://discuss.elastic.co/u/Mikhail_Khludnev)\
**Post date:** [September 16, 2019, 8:42am UTC](https://discuss.elastic.co/t/filter-by-with-missing-doc-fields-in-nested-docs/199449/3 "2019-09-16T08:42:22Z")

</div>

Hello, Anton.  
What if you put `must_not` with `exists` into the deepest `bool` clause in existing query?

---

<div class="post-metadata">

**Author:** ![antonkallenberg](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/antonkallenberg/32/6259_2.png) [@antonkallenberg](https://discuss.elastic.co/u/antonkallenberg)\
**Post date:** [September 16, 2019, 1:58pm UTC](https://discuss.elastic.co/t/filter-by-with-missing-doc-fields-in-nested-docs/199449/4 "2019-09-16T13:58:07Z")

</div>

Hi Mikhail,

Thank you for your reply, I wish it was that simple, or maybe I'm missing something...?

Using must\_not with an exist would look something like:

```
{
	"query": {
		"nested": {
			"path": "lastSessions",
			"query": {
				"bool": {
					"must_not": [{
						"exists": {
							"field": "sessions.periodId"
						}
					}]
				}
			}
		}
	}
}

```

That will give me zero hits back since all docs has a periodId-field. I would like something like exist but I would like pass some kind of filter with the exists, to only match docs which are missing a field with a specific value, something like (made this up, it's not a valid query):

```
{
	"query": {
		"nested": {
			"path": "sessions",
			"query": {
				"bool": {
					"must_not": [{
						"exists": {
							"field": "sessions.periodId",
							"term": {
								"periodId": {
									"value": 343
								}
							}
						}
					}]
				}
			}
		}
	}
}

```

Any ideas? 🙂

---

<div class="post-metadata">

**Author:** ![antonkallenberg](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/antonkallenberg/32/6259_2.png) [@antonkallenberg](https://discuss.elastic.co/u/antonkallenberg)\
**Post date:** [September 16, 2019, 2:52pm UTC](https://discuss.elastic.co/t/filter-by-with-missing-doc-fields-in-nested-docs/199449/5 "2019-09-16T14:52:09Z")

</div>

The core issue is that a must\_not with a term doesn't work on nested queries? Or am I missing something:

```
{
	"query": {
		"nested": {
			"path": "lastSessions",
			"query": {
				"bool": {
					"must_not": [{
						"term": {
							"sessions.periodId": {
								"value": 343
							}
						}
					}]
				}
			}
		}
	}
}

```

Will give me 2 hits back, but there is only one hit which really matches, only one doc which misses a session with id 343?

---

<div class="post-metadata">

**Author:** ![antonkallenberg](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/antonkallenberg/32/6259_2.png) [@antonkallenberg](https://discuss.elastic.co/u/antonkallenberg)\
**Post date:** [September 16, 2019, 3:10pm UTC](https://discuss.elastic.co/t/filter-by-with-missing-doc-fields-in-nested-docs/199449/6 "2019-09-16T15:10:04Z")

</div>

Okay.... just found `include_in_parent`, guess flattening the nested docs within the parent doc will make this kind of query possible...

---

<div class="post-metadata">

**Author:** ![Mikhail\_Khludnev](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mikhail_khludnev/32/59591_2.png) [@Mikhail\_Khludnev](https://discuss.elastic.co/u/Mikhail_Khludnev)\
**Post date:** [September 18, 2019, 6:57pm UTC](https://discuss.elastic.co/t/filter-by-with-missing-doc-fields-in-nested-docs/199449/7 "2019-09-18T18:57:37Z")

</div>

> [@antonkallenberg](#):
>
> The core issue is that a must\_not with a term doesn't work on nested queries? Or am I missing something:

Something like this (pure negative queries) usually addressed with must + match\_all, however, it should happen underneath, though I'm not sure about nested.

> [@antonkallenberg](#):
>
> Will give me 2 hits back, but there is only one hit which really matches, only one doc which misses a session with id 343?

It's better to /\_explain it.

---

<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:** [October 16, 2019, 6:57pm UTC](https://discuss.elastic.co/t/filter-by-with-missing-doc-fields-in-nested-docs/199449/8 "2019-10-16T18:57:39Z")

</div>

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