How to find documents that don't have a nested object in array which matches some value?

Question

Here you can see a small part of document at ES. At array of details may be a lot of different objects with different id's and values. I have to create a query that finds all documents that don't have an object with id "SR_Middle_Name" or "SR_Middle_Name" and value "Aron" :

"details":[  
   {  
      "id":"SR_Name",
      "value":"Elvis"
   },
   {  
      "id":"SR_Middle_Name",
      "value":"Aron"
   }
]

Thank you all for a help , I found solution here: https://stackoverflow.com/questions/48229388/elasticsearch-nested-query-exclude-parent-document — Юра Вусач, Apr 13 '18 at 11:44

score 1 · Answer 1 · answered Apr 13 '18 at 09:03

The object you are trying to query is a nested object. First make sure you provide the correct index mapping for the nested object. And after that you can make nested query on that. Eg-

GET /_search
{
    "query": {
        "nested" : {
            "path" : "details",            
            "query" : {
                "bool" : {
                    "must" : [
                    { "match_not" : {"details.id" : "SR_Middle_Name"} },                    
                    ]
                }
            }
        }
    }
}

score 0 · Answer 2 · answered Nov 10 '20 at 13:03

To be able to find documents - with out nested document - with specific values you should use a combination must_not(nested(terms(value))) Example:

{
  "bool" : {
    "must_not" : [
      {
        "nested" : {
          "query" : {
            "term" : {
              "details.id" : {
                "value" : "SR_Middle_Name"
              }
            }
          },
          "path" : "details"
        }
      }
    ]
  }
}

To be able to add

or have id "SR_Middle_Name" and value "Aron"

wrap first query in should clause and add additional should clause

Final example:

{
  "bool" : {
    "should" : [
      {
        "bool" : {
          "must_not" : [
            {
              "nested" : {
                "query" : {
                  "term" : {
                    "details.id" : {
                      "value" : "SR_Middle_Name"
                    }
                  }
                },
                "path" : "details"
              }
            }
          ]
        }
      },
      {
        "nested" : {
          "query" : {
            "bool" : {
              "filter" : [
                {
                  "term" : {
                    "details.id" : {
                      "value" : "SR_Middle_Name"
                    }
                  }
                },
                {
                  "term" : {
                    "details.value" : {
                      "value" : "Aron"
                    }
                  }
                }
              ]
            }
          },
          "path" : "details"
        }
      }
    ]
  }
}

score -1 · Answer 3 · answered Apr 13 '18 at 09:58

May be this help you:

case 1: where id = "SR_Middle_Name" and value = "Aron"

curl -XGET 'localhost:9200/yourindex/yourtypes/_search?pretty' -H 'Content-Type: application/json' -d'
{
    "query" : {
        "bool" : {
            "must" : [
                { "match_phrase" : {"details.id" : "SR_Middle_Name"}},
                { "match_phrase" : {"details.value" : "Aron"}}
            ]
        }
    }
}'

case 2 : where id != "SR_Middle_Name" and value = "Aron"

curl -XGET 'localhost:9200/yourindex/yourtypes?pretty' -H 'Content-Type: application/json' -d'
{
    "query" : {
        "bool" : {
            "must_not" : { "match_phrase" : {details.id" : "SR_Middle_Name"}},
            "must" : { "match_phrase" : {"details.value" : "Aron"}}                        
        }
    }
}'

How to find documents that don't have a nested object in array which matches some value?

3 Answers3