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
Hello, Anton.
What if you put must_not
with exists
into the deepest bool
clause in existing query?
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?
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?
Okay.... just found include_in_parent
, guess flattening the nested docs within the parent doc will make this kind of query possible...
Something like this (pure negative queries) usually addressed with must + match_all, however, it should happen underneath, though I'm not sure about nested.
It's better to /_explain it.
1 Like
system
(system)
Closed
October 16, 2019, 6:57pm
8
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.