Filter date histogram buckets

[mwitsas]

Could anyone help me to understand how to filter the following query just to return minute buckets containing zero documents? Equivalent of a GROUP BY with a HAVING statement in SQL. My aim is to return a list of minutes which contain no documents during the day.

{
  "size": 0,
  "query": {
    "range": {
      "@timestamp": {
        "gte": "2024-02-18T00:00:00.000Z",
        "lte": "2024-02-18T23:59:59.999Z"
      }
    }
  },
  "aggs": {
    "count_per_minute": {
      "date_histogram": {
        "field": "@timestamp",
        "interval": "minute",
        "order": {
          "_key": "asc"
        }
      }
    }
  }
}

[Abinash_Raja]

Hi @mwitsas,

how to filter the following query just to return minute buckets containing zero documents?

You can achieve the same using the aggregations Value count and bucket selector with gap_policy set as insert_zeros

Sample Query:

{
  "size": 0,
  "query": {
    "range": {
      "@timestamp": {
        "gte": "2024-02-18T00:00:00.000Z",
        "lte": "2024-02-18T23:59:59.999Z"
      }
    }
  },
  "aggs": {
    "count_per_minute": {
      "date_histogram": {
        "field": "@timestamp",
        "interval": "minute",
        "order": {
          "_key": "asc"
        }
      },
      "aggregations": {
        "bucket_filter": {
          "bucket_selector": {
            "gap_policy": "insert_zeros",
            "buckets_path": {
              "ags": "timestamp_value_count"
            },
            "script": {
              "source": "params.ags == 0",
              "lang": "painless"
            }
          }
        },
        "timestamp_value_count": {
          "value_count": {
            "field": "@timestamp"
          }
        }
      }
    }
  }
}

[Abinash_Raja]

It can also be achieved by using bucket selector aggregation alone.

Sample Query:

{
  "size": 0,
  "query": {
    "range": {
      "@timestamp": {
        "gte": "2024-02-18T00:00:00.000Z",
        "lte": "2024-02-18T23:59:59.999Z"
      }
    }
  },
  "aggs": {
    "count_per_minute": {
      "date_histogram": {
        "field": "@timestamp",
        "interval": "minute",
        "order": {
          "_key": "asc"
        }
      },
      "aggregations": {
        "bucket_filter": {
          "bucket_selector": {
            "gap_policy": "insert_zeros",
            "buckets_path": {
              "ags": "_count"
            },
            "script": {
              "source": "params.ags == 0",
              "lang": "painless"
            }
          }
        }
      }
    }
  }
}

[mwitsas]

Many thanks - this did exactly what I needed