Elastic search query on nested object with specific condition

Question

I need some help with the elastic search query. My index has a nested field called departments, let's say the nested object has a dept-id field. I would like to do a search on this nested object and the condition I will highlight below.

Index/_mapping
{
    "items": {
        "mappings": {
            "properties": {
                "item-id": {"type": "text"},
                "departments": {
                    "type": "nested",
                    "properties": {
                        "dept-id": {
                            "type": "text"
                        }
                    }
                }
            }
        }
    }
}

Sample Data
{
    "hits": [
        {
            "_index": "items",
            "_source": {
                "item-id": "item-1",
                "departments": [
                    {
                        "dept-id": "dept-1"
                    },
                    {
                        "dept-id": "dept-2"
                    },
                    {
                        "dept-id": "dept-3"
                    }
                ]
            }
        },
        {
            "_index": "items",
            "_source": {
                "item-id": "item-2",
                "departments": [
                    {
                        "dept-id": "dept-1"
                    }
                ]
            }
        },
        {
            "_index": "items",
            "_source": {
                "item-id": "item-3",
                "departments": [
                    {
                        "dept-id": "dept-2"
                    }
                ]
            }
        }
    ]
}

Condition:

• I need to filter the data above based on this list of inactive-department ids.
  • inactive-department: ["1"]
• I am trying to query for the index to return data with item-id 1 and 3.
• I tried must_not on the nested object but it is not the answer I'm looking for as it only returns item-3. Adding an active field (boolean type) to the nested object which mostly will be a solution to my problem. However, I am curious is there a way to query this without an additional field?

Answer 1

I don't know if I understand correctly but you want to filter by "dept-1" (which are the inactive ones) and have the return with the docs: item-1 and item-3?

The first thing I did was put the type of the dept-id field as a keyword.

The query I used was this:

GET /teste/_search
{
  "query": {
    "nested": {
      "path": "departments",
      "query": {
        "bool": {
          "must_not": [
            {
              "terms": {
                "departments.dept-id": [
                  "dept-1"
                ]
              }
            }
          ]
        }
      }
    }
  }
}

Results:

"hits" : [
      {
        "_index" : "teste",
        "_type" : "_doc",
        "_id" : "sJwaO4EBBR941NShfXGS",
        "_score" : 0.0,
        "_source" : {
          "item-id" : "item-3",
          "departments" : [
            {
              "dept-id" : "dept-2"
            }
          ]
        }
      },
      {
        "_index" : "teste",
        "_type" : "_doc",
        "_id" : "sZwaO4EBBR941NShpnH0",
        "_score" : 0.0,
        "_source" : {
          "item-id" : "item-1",
          "departments" : [
            {
              "dept-id" : "dept-1"
            },
            {
              "dept-id" : "dept-2"
            },
            {
              "dept-id" : "dept-3"
            }
          ]
        }
      }
    ]