mwitsas
February 19, 2024, 3:31pm
1
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"
}
}
}
}
}
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"
}
}
}
}
}
}
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
February 21, 2024, 12:32pm
4
Many thanks - this did exactly what I needed
system
(system)
Closed
March 20, 2024, 12:33pm
5
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.