"single or null" aggregation function for elastic
I’m looking for a way to simulate “single or null” (on distinct values)
aggregation function in elastic.
Example of a query
-- for multiple distinct values return nullselect date,
single_value(value) from table group by date
1. ANY_VALUE. For multiple values returns one of them (I need null).
2. SINGLE_VALUE. For multiple (or empty) values throws exception (I need
3. COLLECT / JSON_ARRAYAGG (see below).
4. Manually add column COUNT(distinct ...) and post-process the result.
COLLECT / JSON_ARRAYAGG
select date, collect(distinct value) from table group by date
This query might potentially work but the problem with elastic is that it
doesn’t return all values by default (similar issue to scrolling. see size
Currently, scrolling is not possible with aggregations.
Therefore I have to impose usage of DISTINCT / LIMIT with COLLECT :
-- enforce DISTINCT and LIMITselect date, collect(distinct value limit
2) from table group by date
Do you think it is reasonable to enforce such restriction to make it work
with elastic ?
Adding COUNT function
One can append count(distinct value) aggregation and check if it is equal
-- use combination of ANY_VALUE and COUNTselect date,
any_value(value), count(distinct value) from table group by date
What do you think ?