'Elasticsearch: find document doesn't have intersecting nested items
I have a document with nested collection and the goal is to find that ones which don't have any inner items intersecting particular period considering also an item's status.
inb4 I've found at least two SO items that didn't help me (probably I'm noob):
ElasticSearch - find all documents whose nested documents do not intersect with date range
and
Elasticsearch inverse range overlap query
So, my document, let's say, simple (I'll paste a JSON definition, just for simplicity. all mappings are fine, trust me ;) ):
{
"maintenances": [ // <-- this is nested collection
{
"start": "date/time",
"end": "date/time",
"status": boolean
} ]
}
What I need is to write a query for documents that don't have any active (status = true) maintenance items intersecting some period (from and to for instance).
I started from simple expression:
must_not { nested { exists { field: maintenances } } }
or
must_not { nested { must [
{ maintenances.start <= to },
{ maintenances.end >= from },
{ status = true }
] } }
That returned me any document from test entries.
Remembering that nested query will return outer document in case if any document will match expression I decided to make a query complex, something like:
maintenances not exists
or
(
any maintenance within range is not active
and
any maintenance outside range is active
)
But it became clear (not very fast unfortunately) that this query doesn't work for sorts of edge-cases: like all nested maintenance items are inactive, or all maintenance are outside requested bounds.
Currently I'm not sure, but I assume that query should contain as many or-d items as many edge-cases are? Like:
maintenances not exists
or
(
any maintenance within range is not active
and
any maintenance outside range
)
or
(
any maintenance within range is not active
and
no maintenance outside range
)
or
(
no maintenance within range
and
any maintenance outside range
)
or
OVER9000 of them
Does anyone know simplest way to query Elastic for my case?
Solution 1:[1]
all mappings are fine, trust me
Suddenly (no actually) this was an issue. Absence of mapping for status prevented from data being filtered correctly.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | dotFive |
