Example dataset

Id:1 Year:1999 Series title : Cool Series Episode title: ep1

Id:2 Year:2000 Series title : Cool Series Episode title: ep2

Id:3 Year:2001 Series title : Cool Series Episode title: ep3

Id:4 Year:2005 Series title : Another series Episode title: ep1

Id:5 Year:2006 Series title : Another series Episode title: ep2

How could I collapse/aggregate by series title. And in output have custom calculated fields: min-max years, count of episodes

Expected result Would be 1 object per series title.

Ex:

Series title: Cool Series Custom-field-year: 1999-2001 Total-count: 3

Series title: Another series Custom-field-year: 2005-2006 Total-count: 2

CodePudding user response：

If I understand correctly you can use the terms aggs and as sub aggs Max and Min.

PUT idx_test/_bulk
{"index":{"_id":1}}
{"year":1999, "series_title" : "Cool Series Episode", "title": "ep1"}
{"index":{"_id":2}}
{"year":2000, "series_title" : "Cool Series Episode", "title": "ep2"}
{"index":{"_id":3}}
{"year":2001, "series_title" : "Cool Series Episode", "title": "ep4"}
{"index":{"_id":4}}
{"year":2005, "series_title" : "Another series Episode", "title": "ep1"}
{"index":{"_id":5}}
{"year":2006, "series_title" : "Another series Episode", "title": "ep1"}


GET idx_test/_search
{
  "size": 0,
  "aggs": {
    "group_by_series_title": {
      "terms": {
        "field": "series_title.keyword",
        "size": 10
      },
      "aggs": {
        "min_year": {
          "min": {
            "field": "year"
          }
        },
        "max_year": {
          "max": {
            "field": "year"
          }
        }
      }
    }
  }
}

Results

 "aggregations": {
    "group_by_series_title": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "Cool Series Episode",
          "doc_count": 3,
          "min_year": {
            "value": 1999
          },
          "max_year": {
            "value": 2001
          }
        },
        {
          "key": "Another series Episode",
          "doc_count": 2,
          "min_year": {
            "value": 2005
          },
          "max_year": {
            "value": 2006
          }
        }
      ]
    }
  }

CodePudding user response：

You can use multiple aggregations to achieve this

1. Terms aggregation

2. Min aggregation

3. Max aggregation

4. Value count aggregation

{
  "size": 0,
  "aggs": {
    "NAME": {
      "terms": {
        "field": "Series title.keyword",
        "size": 10
      },
      "aggs": {
        "min-year": {
          "min": {
            "field": "Year"
          }
        },
        "max-year": {
          "max": {
            "field": "Year"
          }
        },
        "episode-count":{
           "value_count": {
             "field": "Episode title.keyword"
           }
        }
      }
    }
  }
}

Result

"buckets" : [
        {
          "key" : "Cool Series",
          "doc_count" : 3,
          "episode-count" : {
            "value" : 3
          },
          "max-year" : {
            "value" : 2001
          },
          "min-year" : {
            "value" : 1999
          }
        },
        {
          "key" : "Another series",
          "doc_count" : 2,
          "episode-count" : {
            "value" : 2
          },
          "max-year" : {
            "value" : 2006
          },
          "min-year" : {
            "value" : 2005
          }
        }
      ]