Combining missing and term query in nested document in Elasticsearch

Multi tool use
I have these 3 documents, where fields
is of type nested
:
{
"fields": [
{"field_id": 23, "value": "John Doe"},
{"field_id": 92, "value": null}
]
}
{
"fields": [
{"field_id": 23, "value": "Ada Lovelace"},
]
}
{
"fields": [
{"field_id": 23, "value": "Jack Daniels"},
{"field_id": 92, "value": "jack@example.com"}
]
}
I need to search for documents where:
(`field_id` = `92` AND `value` is `null`) OR (`field_id` `92` is missing.)
Combining a terms and missing query leads to only the document with the null
value being returned:
...
"nested": {
"path": "fields",
"filter": {
"bool": {
"bool": {
"must": [
{
"missing": {
"field": "fields.value"
}
},
{
"terms": {
"fields.field_id": [92]
}
}
]
}
}
}
}
...
How can I do this?
elasticsearch
add a comment |
I have these 3 documents, where fields
is of type nested
:
{
"fields": [
{"field_id": 23, "value": "John Doe"},
{"field_id": 92, "value": null}
]
}
{
"fields": [
{"field_id": 23, "value": "Ada Lovelace"},
]
}
{
"fields": [
{"field_id": 23, "value": "Jack Daniels"},
{"field_id": 92, "value": "jack@example.com"}
]
}
I need to search for documents where:
(`field_id` = `92` AND `value` is `null`) OR (`field_id` `92` is missing.)
Combining a terms and missing query leads to only the document with the null
value being returned:
...
"nested": {
"path": "fields",
"filter": {
"bool": {
"bool": {
"must": [
{
"missing": {
"field": "fields.value"
}
},
{
"terms": {
"fields.field_id": [92]
}
}
]
}
}
}
}
...
How can I do this?
elasticsearch
Do you mean to say where (field_id
is92
andvalue
isnull
) or (field_id:92
is not present).
– Nishant Saini
Jan 3 at 3:13
Also isfields
a nested field or object field?
– Nishant Saini
Jan 3 at 3:15
@NishantSaini Yes, that's right. I've edited the post.fields
is nested (the actual mapping is a lot more complex)
– Simon Fredsted
Jan 3 at 7:51
add a comment |
I have these 3 documents, where fields
is of type nested
:
{
"fields": [
{"field_id": 23, "value": "John Doe"},
{"field_id": 92, "value": null}
]
}
{
"fields": [
{"field_id": 23, "value": "Ada Lovelace"},
]
}
{
"fields": [
{"field_id": 23, "value": "Jack Daniels"},
{"field_id": 92, "value": "jack@example.com"}
]
}
I need to search for documents where:
(`field_id` = `92` AND `value` is `null`) OR (`field_id` `92` is missing.)
Combining a terms and missing query leads to only the document with the null
value being returned:
...
"nested": {
"path": "fields",
"filter": {
"bool": {
"bool": {
"must": [
{
"missing": {
"field": "fields.value"
}
},
{
"terms": {
"fields.field_id": [92]
}
}
]
}
}
}
}
...
How can I do this?
elasticsearch
I have these 3 documents, where fields
is of type nested
:
{
"fields": [
{"field_id": 23, "value": "John Doe"},
{"field_id": 92, "value": null}
]
}
{
"fields": [
{"field_id": 23, "value": "Ada Lovelace"},
]
}
{
"fields": [
{"field_id": 23, "value": "Jack Daniels"},
{"field_id": 92, "value": "jack@example.com"}
]
}
I need to search for documents where:
(`field_id` = `92` AND `value` is `null`) OR (`field_id` `92` is missing.)
Combining a terms and missing query leads to only the document with the null
value being returned:
...
"nested": {
"path": "fields",
"filter": {
"bool": {
"bool": {
"must": [
{
"missing": {
"field": "fields.value"
}
},
{
"terms": {
"fields.field_id": [92]
}
}
]
}
}
}
}
...
How can I do this?
elasticsearch
elasticsearch
edited Jan 3 at 7:57
Simon Fredsted
asked Jan 2 at 15:54
Simon FredstedSimon Fredsted
508725
508725
Do you mean to say where (field_id
is92
andvalue
isnull
) or (field_id:92
is not present).
– Nishant Saini
Jan 3 at 3:13
Also isfields
a nested field or object field?
– Nishant Saini
Jan 3 at 3:15
@NishantSaini Yes, that's right. I've edited the post.fields
is nested (the actual mapping is a lot more complex)
– Simon Fredsted
Jan 3 at 7:51
add a comment |
Do you mean to say where (field_id
is92
andvalue
isnull
) or (field_id:92
is not present).
– Nishant Saini
Jan 3 at 3:13
Also isfields
a nested field or object field?
– Nishant Saini
Jan 3 at 3:15
@NishantSaini Yes, that's right. I've edited the post.fields
is nested (the actual mapping is a lot more complex)
– Simon Fredsted
Jan 3 at 7:51
Do you mean to say where (
field_id
is 92
and value
is null
) or (field_id:92
is not present).– Nishant Saini
Jan 3 at 3:13
Do you mean to say where (
field_id
is 92
and value
is null
) or (field_id:92
is not present).– Nishant Saini
Jan 3 at 3:13
Also is
fields
a nested field or object field?– Nishant Saini
Jan 3 at 3:15
Also is
fields
a nested field or object field?– Nishant Saini
Jan 3 at 3:15
@NishantSaini Yes, that's right. I've edited the post.
fields
is nested (the actual mapping is a lot more complex)– Simon Fredsted
Jan 3 at 7:51
@NishantSaini Yes, that's right. I've edited the post.
fields
is nested (the actual mapping is a lot more complex)– Simon Fredsted
Jan 3 at 7:51
add a comment |
1 Answer
1
active
oldest
votes
You already have query for one condition. Lets call this A. For second condition check for fields.field_id: 92
in nested documents. Lets say this is B. But your condition is fields.field_id: 92
should not exist. So to achieve this wrap B in must_not
. i.e. B'
What is required is A OR B'
So the final query will be:
{
"query": {
"bool": {
"should": [
{
"nested": {
"path": "fields",
"query": {
"bool": {
"must": [
{
"term": {
"fields.field_id": 92
}
}
],
"must_not": [
{
"exists": {
"field": "fields.value"
}
}
]
}
}
}
},
{
"bool": {
"must_not": [
{
"nested": {
"path": "fields",
"query": {
"term": {
"fields.field_id": 92
}
}
}
}
]
}
}
]
}
}
}
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54009318%2fcombining-missing-and-term-query-in-nested-document-in-elasticsearch%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You already have query for one condition. Lets call this A. For second condition check for fields.field_id: 92
in nested documents. Lets say this is B. But your condition is fields.field_id: 92
should not exist. So to achieve this wrap B in must_not
. i.e. B'
What is required is A OR B'
So the final query will be:
{
"query": {
"bool": {
"should": [
{
"nested": {
"path": "fields",
"query": {
"bool": {
"must": [
{
"term": {
"fields.field_id": 92
}
}
],
"must_not": [
{
"exists": {
"field": "fields.value"
}
}
]
}
}
}
},
{
"bool": {
"must_not": [
{
"nested": {
"path": "fields",
"query": {
"term": {
"fields.field_id": 92
}
}
}
}
]
}
}
]
}
}
}
add a comment |
You already have query for one condition. Lets call this A. For second condition check for fields.field_id: 92
in nested documents. Lets say this is B. But your condition is fields.field_id: 92
should not exist. So to achieve this wrap B in must_not
. i.e. B'
What is required is A OR B'
So the final query will be:
{
"query": {
"bool": {
"should": [
{
"nested": {
"path": "fields",
"query": {
"bool": {
"must": [
{
"term": {
"fields.field_id": 92
}
}
],
"must_not": [
{
"exists": {
"field": "fields.value"
}
}
]
}
}
}
},
{
"bool": {
"must_not": [
{
"nested": {
"path": "fields",
"query": {
"term": {
"fields.field_id": 92
}
}
}
}
]
}
}
]
}
}
}
add a comment |
You already have query for one condition. Lets call this A. For second condition check for fields.field_id: 92
in nested documents. Lets say this is B. But your condition is fields.field_id: 92
should not exist. So to achieve this wrap B in must_not
. i.e. B'
What is required is A OR B'
So the final query will be:
{
"query": {
"bool": {
"should": [
{
"nested": {
"path": "fields",
"query": {
"bool": {
"must": [
{
"term": {
"fields.field_id": 92
}
}
],
"must_not": [
{
"exists": {
"field": "fields.value"
}
}
]
}
}
}
},
{
"bool": {
"must_not": [
{
"nested": {
"path": "fields",
"query": {
"term": {
"fields.field_id": 92
}
}
}
}
]
}
}
]
}
}
}
You already have query for one condition. Lets call this A. For second condition check for fields.field_id: 92
in nested documents. Lets say this is B. But your condition is fields.field_id: 92
should not exist. So to achieve this wrap B in must_not
. i.e. B'
What is required is A OR B'
So the final query will be:
{
"query": {
"bool": {
"should": [
{
"nested": {
"path": "fields",
"query": {
"bool": {
"must": [
{
"term": {
"fields.field_id": 92
}
}
],
"must_not": [
{
"exists": {
"field": "fields.value"
}
}
]
}
}
}
},
{
"bool": {
"must_not": [
{
"nested": {
"path": "fields",
"query": {
"term": {
"fields.field_id": 92
}
}
}
}
]
}
}
]
}
}
}
answered Jan 4 at 4:14


Nishant SainiNishant Saini
2,1181019
2,1181019
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54009318%2fcombining-missing-and-term-query-in-nested-document-in-elasticsearch%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
ojg5CoVfF2UVAK4E,npxDiKZrSNSBq9MrdJ6
Do you mean to say where (
field_id
is92
andvalue
isnull
) or (field_id:92
is not present).– Nishant Saini
Jan 3 at 3:13
Also is
fields
a nested field or object field?– Nishant Saini
Jan 3 at 3:15
@NishantSaini Yes, that's right. I've edited the post.
fields
is nested (the actual mapping is a lot more complex)– Simon Fredsted
Jan 3 at 7:51