How to get all documents where all objects in an array are of certain values in Elastic search

Question

Lets assume I want to fetch all documents where "status" for all the elements in the array field is NOT UNKNOWN

For example:

[
  {
    "type": "object1",
    "list": [
      {
        "node": "1",
        "status": "UP"
      },
      {
        "node": "2",
        "status": "DOWN"
      },
      {
        "node": "3",
        "status": "UNKNOWN"
      }
    ]
  },
  {
    "type": "object2",
    "list": [
      {
        "node": "1",
        "status": "UNKNOWN"
      },
      {
        "node": "2",
        "status": "UNKNOWN"
      }
    ]
  }
]

And the query should return only "object1" document since "object2" list has all elements as UNKNOWN.

The mapping, I have defined it as a nested object and I can already search for records where list.status=UP for example Just want to know how to achieve the use case of fetching documents where ALL the elements in the array field are NOT a certain value

Tried this

    {
  "query": {
    "bool" : {
      "must_not" : {
        "term" : { "list.status" : "UNKNOWN" }
      }
    }
  }
}

However the above query does not return object1 (not expected) in this case but does filter out object2 (as expected)

Answer 1

Your problem is that even if one of the nested doc matches, the compete outer doc is returned. What you want is when all the nested docs match then only you want to return the outer doc.

For this, the documentation suggests must_not. Try to remove the docs which have "UNKNOWN" and then you will get only with "UP" and "DOWN":

{
  "query": {
    "bool": {
      "must_not": [
        {
          "nested": {
            "path": "list",
            "query": {
              "term": {
                "status": "UNKNOWN"
              }
            }
          }
        }
      ]
    }
  }
}

Regarding you update: You have the opposite idea. With nested fields the association is intact. With a normal object of array field, the association is lost. That is why nested type exists. Link