Get the max value of a nested aggregated object

I have this kind of doc structure :

{
    "_index": "infra-metrics",
    "_id": "C4-XIIwBNCt3Y4ACckwp",
    "_score": 1,
    "_source": {
      "resourceId": "e5735563-2b4c-46e9-a38d-4728a5617ebc",
      "tenantId": "d7842881-31c9-4974-bc87-7a621ba440a5",
      "timestamp": "2023-11-30T14:17:24.627",
      "points": [
        {
          "measurement": "win_logical_disk",
          "precison": "MILLISECONDS",
          "size": 1000186310656,
          "free": 173625864192,
          "name": "C:",
          "load": 83
        },
        {
          "measurement": "win_logical_disk",
          "precison": "MILLISECONDS",
          "size": 999559262208,
          "free": 554817146880,
          "name": "D:",
          "load": 44
        }
      ]
    }
  }

I need the max load of each disk of each day for the past 29 days, how can I achieve that ?

I firstly thought about making this query :

{
  "size": 0, 
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "timestamp": {
              "gte": "now-29d/d",
              "lte": "now/d"
            }
          }
        },
        {
          "term": {
            "resourceId.keyword": {
              "value": "e5735563-2b4c-46e9-a38d-4728a5617ebc"
            }
          }
        },
        {
          "term": {
            "tenantId.keyword": {
              "value": "d7842881-31c9-4974-bc87-7a621ba440a5"
            }
          }
        },
        {
          "bool": {
            "should": [
              {
                "term": {
                  "points.measurement.keyword": {
                    "value": "win_logical_disk"
                  }
                }
              },
              {
                "term": {
                  "points.measurement.keyword": {
                    "value": "lnx_logical_disk"
                  }
                }
              },
              {
                "term": {
                  "points.measurement.keyword": {
                    "value": "ibmi_asp"
                  }
                }
              }
            ]
          }
        }
      ]
    }
  },
  "aggs": {
    "date_histogram": {
      "date_histogram": {
        "field": "timestamp",
        "calendar_interval": "day",
        "extended_bounds": {
          "min": "now-29d/d",
          "max": "now/d"
        }
      },
      "aggs": {
        "disk": {
          "terms": {
            "field": "points.name.keyword"
          },
          "aggs": {
            "max": {
              "max": {
                "field": "points.load"
              }
            }
          }
        }
      }
    }
  }
}

Let's say I only have 1 doc today, the one that I provided abov. If i run the above query, it returns me this :

{
      "key_as_string": "2023-11-30T00:00:00.000Z",
      "key": 1701302400000,
      "doc_count": 13,
      "disk": {
        "doc_count_error_upper_bound": 0,
        "sum_other_doc_count": 0,
        "buckets": [
          {
            "key": "C:",
            "doc_count": 13,
            "max": {
              "value": 83
            }
          },
          {
            "key": "D:",
            "doc_count": 13,
            "max": {
              "value": 83
            }
          }
        ]
      }
    }

But I want the query to return :

For "C:" : 83 For "D:" : 44

I know why it doesn't work but I have no idea how to do it.

Any ideas ?

Thanks

Solution

The reason is you are using object field type. The issue will be fixed if you use the nested field type. Check this article for more information.

Here are some useful informations for object vs nested:

Object fields are used when the structure of the sub-fields is important, but not the relationships between them. They are indexed as separate and independent fields, and can be queried independently. However, if you need to maintain the relationships between the sub-fields, object fields may not be suitable because they don't keep the correlation between the sub-fields.

On the other hand, nested fields are used when you need to maintain the relationships between the sub-fields. They are indexed as separate hidden documents, which allows for querying that maintains the relationships between the sub-fields. This is particularly useful when you have arrays of objects and you want to query objects that match multiple criteria.

Here is an example for you.

PUT measurement/_doc/1
{
  "resourceId": "e5735563-2b4c-46e9-a38d-4728a5617ebc",
  "tenantId": "d7842881-31c9-4974-bc87-7a621ba440a5",
  "timestamp": "2023-11-30T14:17:24.627",
  "points": [
    {
      "measurement": "win_logical_disk",
      "precison": "MILLISECONDS",
      "size": 1000186310656,
      "free": 173625864192,
      "name": "C:",
      "load": 83
    },
    {
      "measurement": "win_logical_disk",
      "precison": "MILLISECONDS",
      "size": 999559262208,
      "free": 554817146880,
      "name": "D:",
      "load": 44
    }
  ]
}

GET measurement/_search
{
  "size": 0,
  "aggs": {
    "date_histogram": {
      "date_histogram": {
        "field": "timestamp",
        "calendar_interval": "day",
        "extended_bounds": {
          "min": "now-29d/d",
          "max": "now/d"
        }
      },
      "aggs": {
        "disk": {
          "terms": {
            "field": "points.name.keyword"
          },
          "aggs": {
            "max": {
              "max": {
                "field": "points.load"
              }
            }
          }
        }
      }
    }
  }
}

PUT measurement_nested
{
  "mappings": {
    "properties": {
      "points": {
        "type": "nested"
      }
    }
  }
}

POST _reindex
{
  "source": {
    "index": "measurement"
  },
  "dest": {
    "index": "measurement_nested"
  }
}

GET measurement_nested/_search
{
  "size": 0,
  "aggs": {
    "date_histogram": {
      "date_histogram": {
        "field": "timestamp",
        "calendar_interval": "day",
        "extended_bounds": {
          "min": "now-29d/d",
          "max": "now/d"
        }
      },
      "aggs": {
        "disk": {
          "nested": {
            "path": "points"
          },
          "aggs": {
            "NAME": {
              "terms": {
                "field": "points.name.keyword"
              },
              "aggs": {
                "max": {
                  "max": {
                    "field": "points.load"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}