Unique feilds count

How can i right DSL query for count of unique value in fields (Colom) for one day.
where unique value is greater than 15

SQL example: select /+parallel(15)/ fruits ,count(1) from all_items where LOAD_DATE=trunc(sysdate) group by fruits having count(1)>15 ;

output :
17-jan-2022
apple 16
orange 18

15-jan-2022
apple 19
orange 22
pineapple 30

There are min_doc_count parameter in Terms aggregation.

Thanks for the help, tried in DEV tools works and copy pasted DSL query in kibana visual filter by query getting below error
[1:440] [bool] failed to parse field [filter]

which i tried
{
"aggs": {
"tags": {
"terms": {
"field": "CALLED_NUMBER.keyword",
"min_doc_count": 15
}
}
}
}

Where did you paste it? it is an aggregation query and not a filter query.

i want to apply that dsl query in kibana --> visuals--> tables-> filter-> filter by dsl query

That filter shoud be a query for each document. Aggregation clause must be ignored.
If you want to filter only tems with 15 or more documents, you need something like Window function of SQL but there is no such query in Elasticsearch. You may have to take some quite different alternatives depending on your goals.

The real scenario is I need a table visual with tables split based on dates that is done,
In that table i feild (called_number) if it is repeting more than 15 times in that day and no of times its repeted count.

Can you please help me with that

You could use aggregation-based "Data table" visualization.

An example:


You can't hide Count column.

1 Like

Thanks I really appreciate it. It word for my scenario.

1 Like