Been trying to write a difficult query, and it's getting the better of me.
Say I have 3 docs like these:

Doc #1:
{
  "database_backups" : [
    {
      "Date" : "2021-09-03",
      "Name" : "EventsAPI",
      "Type" : "Full"
    },
    {
      "Date" : "2021-09-04",
      "Name" : "EventsAPI",
      "Type" : "Diff"
    },
    {
      "Date" : "2021-09-05",
      "Name" : "EventsAPI",
      "Type" : "Log"
    }
  ]
}
Doc #2:
{
  "database_backups" : [
    {
      "Date" : "2021-09-01",
      "Name" : "EventsAPI",
      "Type" : "Full"
    }
  ]
}
Doc #3:
{
  "database_backups" : [
    {
      "Date" : "2021-09-02",
      "Name" : "EventsAPI",
      "Type" : "Diff"
    },
    {
      "Date" : "2021-09-01",
      "Name" : "EventsSecurity",
      "Type" : "Diff"
    }
  ]
}

I want to get latest backups of each unique Name Type. So i would need Name Type Date as a result.
I've tried composite agg to link Name Type, and that worked.
However, when I also try to add max agg on Date, it selects the max Date of the entire doc, rather than a specific object that fits Name Type in the array.

So for example EventsAPI Diff agg's max Date would be "2021-09-05", because that's a max Date in Doc#1, but it's a date for EventsAPI Log, rather than EventsAPI Diff.

Is it possible to get the latest date of the Name Type key for that particular object in the array?
The result I'm looking for should give this info somehow:

EventsSecurity   Diff   "2021-09-01"
EventsAPI   Full   "2021-09-03"
EventsAPI   Diff   "2021-09-04"
EventsAPI   Log    "2021-09-05"

CodePudding user response：

database_backups needs to be of type nested to maintain relation ship between inner object fields.

terms aggregation can be used to get max date for each event and type

Query

{
  "size": 0,
  "aggs": {
    "NAME": {
      "nested": {
        "path": "database_backups"
      },
      "aggs": {
        "NAME": {
          "terms": {
            "field": "database_backups.Name.keyword",
            "size": 10
          },
          "aggs": {
            "NAME": {
              "terms": {
                "field": "database_backups.Type.keyword",
                "size": 10
              },
              "aggs": {
                "NAME": {
                  "max": {
                    "field": "database_backups.Date"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

Result

"aggregations" : {
    "NAME" : {
      "doc_count" : 6,
      "NAME" : {
        "doc_count_error_upper_bound" : 0,
        "sum_other_doc_count" : 0,
        "buckets" : [
          {
            "key" : "EventsAPI",
            "doc_count" : 5,
            "NAME" : {
              "doc_count_error_upper_bound" : 0,
              "sum_other_doc_count" : 0,
              "buckets" : [
                {
                  "key" : "Diff",
                  "doc_count" : 2,
                  "NAME" : {
                    "value" : 1.6307136E12,
                    "value_as_string" : "2021-09-04T00:00:00.000Z"
                  }
                },
                {
                  "key" : "Full",
                  "doc_count" : 2,
                  "NAME" : {
                    "value" : 1.6306272E12,
                    "value_as_string" : "2021-09-03T00:00:00.000Z"
                  }
                },
                {
                  "key" : "Log",
                  "doc_count" : 1,
                  "NAME" : {
                    "value" : 1.6308E12,
                    "value_as_string" : "2021-09-05T00:00:00.000Z"
                  }
                }
              ]
            }
          },
          {
            "key" : "EventsSecurity",
            "doc_count" : 1,
            "NAME" : {
              "doc_count_error_upper_bound" : 0,
              "sum_other_doc_count" : 0,
              "buckets" : [
                {
                  "key" : "Diff",
                  "doc_count" : 1,
                  "NAME" : {
                    "value" : 1.6304544E12,
                    "value_as_string" : "2021-09-01T00:00:00.000Z"
                  }
                }
              ]
            }
          }
        ]
      }
    }
  }