Filtering aggregation results based on nested objects in ElasticSearch

[kornikopic (Jonathan)]

In my database, I have 2 tables such as:

+------------+          +-------------+
|   Project  |          | Deliverable |
+------------+          +-------------+
| id         +----------+ project_id  |
|            |1        *| id          |
|            |          | category    |
|            |          | status      |
+------------+          +-------------+

For this example, the data are the folowing:

+---------------+-----------+--------+---------------+
| Deliverable   | Project   | Status | Category (id) |
+---------------+-----------+--------+---------------+
| deliverable A | project A | OPEN   | 86            |
| deliverable B | project A | CLOSED | 209           |
| deliverable C | project B | OPEN   | 223           |
+---------------+-----------+--------+---------------+

In Elasticsearch, I have created this mapping (it's only a portion):

{
  "projects" : {
    "mappings" : {
      "properties" : {
        "deliverables" : {
          "type" : "nested",
          "properties" : {
            "category" : {
              "properties" : {
                "id" : {
                  "type" : "integer"
                },
                "name" : {
                  "type" : "text",
                  "fields" : {
                    "raw" : {
                      "type" : "keyword"
                    },
                    "suggest" : {
                      "type" : "completion",
                      "analyzer" : "simple",
                      "preserve_separators" : true,
                      "preserve_position_increments" : true,
                      "max_input_length" : 50
                    }
                  },
                  "analyzer" : "html_strip",
                  "fielddata" : true
                },
              }
            },
          }
        },
        "name" : {
          "type" : "text"
        }
      }
    }
  }
}

I have created aggregation on deliverables.category:

GET /projects/_search
{
  "aggs": {
    "all_categories": {
      "global": {},
      "aggs": {
        "categories": {
          "filter": {
            "bool": {
              "must": [
                {
                  "nested": {
                    "path": "deliverables",
                    "query": {
                      "range": {
                        "deliverables.expire_date": {
                          "gte": "2022-11-11"
                        }
                      }
                    }
                  }
                },
                {
                  "nested": {
                    "path": "deliverables",
                    "query": {
                      "term": {
                        "deliverables.status": "OPEN"
                      }
                    }
                  }
                }
              ]
            }
          },
          "aggs": {
            "categories": {
              "nested": {
                "path": "deliverables"
              },
              "aggs": {
                "categories": {
                  "terms": {
                    "field": "deliverables.category.id"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

Which gives me this result:

"aggregations" : {
  "all_categories" : {
    "doc_count" : 2,
    "categories" : {
      "doc_count" : 2,
      "categories" : {
        "doc_count" : 3,
        "categories" : {
          "doc_count_error_upper_bound" : 0,
          "sum_other_doc_count" : 0,
          "buckets" : [
            {
              "key" : 86,
              "doc_count" : 1
            },
            {
              "key" : 209,
              "doc_count" : 1
            },
            {
              "key" : 223,
              "doc_count" : 1
            }
          ]
        }
      }
    }
  }
}

I am expected to get 2 categories out of 3 because one deliverable is CLOSED. But from my understanding the result is based on the document Project not on the nested objects.

How can I get the result based on the nested object so I can get the 2 categories?

Please advise. Thank you.