PUT my-data-stream/_bulk
{"create":{}}
{"timestamp":"2022-05-06T18:25:42","search_term":"hello", "counter": 10}
{"create":{}}
{"timestamp":"2022-05-06T18:25:42","search_term":"bye", "counter": 5}
{"create":{}}
{"timestamp":"2022-05-06T17:25:42","search_term":"hello", "counter": 9}
{"create":{}}
{"timestamp":"2022-05-06T17:25:42","search_term":"bye", "counter": 7}
{"create":{}}
{"timestamp":"2022-05-06T16:25:42","search_term":"hello", "counter": 5}
{"create":{}}
{"timestamp":"2022-05-06T16:25:42","search_term":"bye", "counter": 2}

Given the data set above. I want to sort (DESC) the search terms by sum of the counters which are greater than a specific timestamp.

For example: For timestamp greater than 2022-05-06T16:35:42 (which are top 4 records). Result should be

 Hello, 19 (10   9)
 Bye, 12 (5   7)

CodePudding user response：

Tldr;

You can order your data using order in you aggregation.

As per the documentation here

In this case, the buckets are ordered by the actual term values, such as lexicographic order for keywords or numerically for numbers. This sorting is safe in both ascending and descending directions, and produces accurate results.

Solution

GET /74006495/_search
{
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "timestamp": {
              "gte": "2022-05-06T16:35:42"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "search_terms": {
      "terms": {
        "field": "search_term.keyword",
        "size": 10,
        "order": { "count": "desc" } // <- Here is the order by count agg
      },
      "aggs": {
        "count": {
          "sum": {
            "field": "counter"
          }
        }
      }
    }
  }
}

CodePudding user response：

I used a filter by date and then aggs the results.

{
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "timestamp": {
              "gte": "2022-05-06T16:35:42"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "search_by_day": {
      "date_histogram": {
        "field": "timestamp",
        "calendar_interval": "day"
      },
      "aggs": {
        "search_term": {
          "terms": {
            "field": "search_term.keyword",
            "size": 10
          },
          "aggs": {
            "total_counter": {
              "sum": {
                "field": "counter"
              }
            },
            "counter_bucket_sort": {
              "bucket_sort": {
                "sort": [
                  {
                    "total_counter": {
                      "order": "desc"
                    }
                  }
                ],
                "size": 3
              }
            }
          }
        }
      }
    }
  }
}

Results:

  "aggregations": {
    "search_by_day": {
      "buckets": [
        {
          "key_as_string": "2022-05-06T00:00:00.000Z",
          "key": 1651795200000,
          "doc_count": 4,
          "search_term": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": [
              {
                "key": "hello",
                "doc_count": 2,
                "total_counter": {
                  "value": 19
                }
              },
              {
                "key": "bye",
                "doc_count": 2,
                "total_counter": {
                  "value": 12
                }
              }
            ]
          }
        }
      ]
    }
  }