case when max(x) = min(x) then max(x) end Julian
> On Dec 26, 2018, at 16:22, Andrei Sereda <[email protected]> wrote: > > Hello, > > 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 > > Some Options > > 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 > null). > 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 > <https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html#search-aggregations-bucket-terms-aggregation-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 > to 1. > > -- use combination of ANY_VALUE and COUNTselect date, > any_value(value), count(distinct value) from table group by date > > What do you think ? > > Regards, > Andrei.
