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.