Combining missing and term query in nested document in Elasticsearch

Multi tool use
Multi tool use












0















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?










share|improve this question

























  • 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











  • @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
















0















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?










share|improve this question

























  • 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











  • @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














0












0








0








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?










share|improve this question
















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








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 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











  • @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











  • 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

















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












1 Answer
1






active

oldest

votes


















0














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
}
}
}
}
]
}
}
]
}
}
}





share|improve this answer























    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
    });


    }
    });














    draft saved

    draft discarded


















    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









    0














    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
    }
    }
    }
    }
    ]
    }
    }
    ]
    }
    }
    }





    share|improve this answer




























      0














      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
      }
      }
      }
      }
      ]
      }
      }
      ]
      }
      }
      }





      share|improve this answer


























        0












        0








        0







        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
        }
        }
        }
        }
        ]
        }
        }
        ]
        }
        }
        }





        share|improve this answer













        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
        }
        }
        }
        }
        ]
        }
        }
        ]
        }
        }
        }






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Jan 4 at 4:14









        Nishant SainiNishant Saini

        2,1181019




        2,1181019
































            draft saved

            draft discarded




















































            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.




            draft saved


            draft discarded














            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





















































            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
            w0wgfvetJ1yM

            Popular posts from this blog

            Monofisismo

            Angular Downloading a file using contenturl with Basic Authentication

            Olmecas