Daily Elastic Byte - Tale of an aggregation

data.json

# Show the scoreboard in the contributor app

DELETE scoreboard

PUT scoreboard/_bulk?refresh
{"index":{}}
{ "score" : 1, "@timestamp" : "2021-02-28", "email":"peter@example.org", "name" : "Peter Parker"}
{"index":{}}
{ "score" : 4, "@timestamp" : "2021-02-01", "email":"peter@example.org", "name" : "Peter MiddleName Parker"}
{"index":{}}
{ "score" : 4, "@timestamp" : "2021-02-01", "email":"paul@example.org", "name" : "Paul Paulinson"}
{"index":{}}
{ "score" : 100, "@timestamp" : "2021-01-31", "email":"paul@example.org", "name" : "Paul Paulinson"}
{"index":{}}
{ "score" : 200, "@timestamp" : "2021-03-01", "email":"paul@example.org", "name" : "Paul Paulinson"}
{"index":{}}
{ "score" : 3, "@timestamp" : "2021-02-14", "email":"peter@otherexample.org", "name" : "Peter Parker"}
{"index":{}}
{ "score" : 1, "@timestamp" : "2021-02-28", "email":"other@example.org", "name" : "Someone Other"}
{"index":{}}
{ "score" : 1, "@timestamp" : "2021-02-28", "email":"other@example.org", "name" : "Someone Other"}
{"index":{}}
{ "score" : 1, "@timestamp" : "2021-02-26", "email":"other@example.org", "name" : "Someone Other 123"}
{"index":{}}
{ "score" : 1, "@timestamp" : "2021-02-01", "email":"other@example.org", "name" : "Someone Other 456"}

GET scoreboard/_count

GET scoreboard/_search
{
  "size": 1
}

GET scoreboard/_search
{
  "size": 0, 
  "query": {
    "range": {
      "@timestamp": {
        "gte": "2021-02-01",
        "lt": "2021-03-01"
      }
    }
  }
}

GET scoreboard/_search
{
  "size": 0,
  "query": {
    "range": {
      "@timestamp": {
        "gte": "2021-02-01",
        "lt": "2021-03-01"
      }
    }
  },
  "aggs": {
    "by_user": {
      "terms": {
        "field": "email.keyword",
        "size": 10
      },
      "aggs": {
        "total": {
          "sum": {
            "field": "score"
          }
        }
      }
    }
  }
}

GET scoreboard/_search
{
  "size": 0,
  "query": {
    "range": {
      "@timestamp": {
        "gte": "2021-02-01",
        "lt": "2021-03-01"
      }
    }
  },
  "aggs": {
    "by_user": {
      "terms": {
        "field": "email.keyword",
        "order": {
          "total.value": "desc"
        }, 
        "size": 10
      },
      "aggs": {
        "total": {
          "sum": {
            "field": "score"
          }
        }
      }
    }
  }
}

# nah this is not it
GET scoreboard/_search
{
  "size": 0,
  "query": {
    "range": {
      "@timestamp": {
        "gte": "2021-02-01",
        "lt": "2021-03-01"
      }
    }
  },
  "aggs": {
    "by_user": {
      "terms": {
        "field": "name.keyword",
        "size": 10
      },
      "aggs": {
        "total": {
          "sum": {
            "field": "score"
          }
        }
      }
    }
  }
}

# OHAI top_hits
GET scoreboard/_search
{
  "size": 0,
  "query": {
    "range": {
      "@timestamp": {
        "gte": "2021-02-01",
        "lt": "2021-03-01"
      }
    }
  },
  "aggs": {
    "by_user": {
      "terms": {
        "field": "email.keyword",
        "order": {
          "total.value": "desc"
        }, 
        "size": 10
      },
      "aggs": {
        "total": {
          "sum": {
            "field": "score"
          }
        },
        "top_hits_name": {
          "top_hits": {
            "sort": [
              {
                "@timestamp": {
                  "order": "desc"
                }
              }
            ],
            "size": 1
          }
        }
      }
    }
  }
}



# OHAI top_hits with source excludes for less data
GET scoreboard/_search
{
  "size": 0,
  "query": {
    "range": {
      "@timestamp": {
        "gte": "2021-02-01",
        "lt": "2021-03-01"
      }
    }
  },
  "aggs": {
    "by_user": {
      "terms": {
        "field": "email.keyword",
        "order": {
          "total.value": "desc"
        }, 
        "size": 10
      },
      "aggs": {
        "total": {
          "sum": {
            "field": "score"
          }
        },
        "top_hits_name": {
          "top_hits": {
            "sort": [
              {
                "@timestamp": {
                  "order": "desc"
                }
              }
            ],
            "_source": {
              "includes": "name"
            }, 
            "size": 1
          }
        }
      }
    }
  }
}


# Maybe we can do this even without top hits?
# We basically need a max value based 
# on the date, right?
GET scoreboard/_search
{
  "size": 0,
  "query": {
    "range": {
      "@timestamp": {
        "gte": "2021-02-01",
        "lt": "2021-03-01"
      }
    }
  },
  "aggs": {
    "by_user": {
      "terms": {
        "field": "email.keyword",
        "order": {
          "total.value": "desc"
        }, 
        "size": 10
      },
      "aggs": {
        "total": {
          "sum": {
            "field": "score"
          }
        },
        "name" : {
          "terms": {
            "field": "name.keyword",
            "size": 1,
            "order": {
              "latest.value": "desc"
            }
          },
          "aggs": {
            "latest": {
              "max": {
                "field": "@timestamp"
              }
            }
          }
        }
      }
    }
  }
}