Did you try
this approach, adjusting to field names.
Passed test on ES 8.2.2:
{
"aggs" : {
"@timestamp": {
"terms" : { "field" : "@timestamp"},
"aggs": {
"shift_count": {
"value_count": { "field": "shifts.id" }
},
"shift_count_filter": {
"bucket_selector": {
"buckets_path": {
"shiftCount": "shift_count"
},
"script": "params.shiftCount > 1"
}
}
}}}
}
I am still searching how to apply filter over aggregate using "filtered" query approach, something like
select * from ( select count(*) as count_of_items, item_name from item_collection group by item_name ) as inner_query
where count_of_items > 1
as this approach, using aggregates feels more like a workaround ( thought robust to use in production ).
If you come over something interesting, please post here.
---
I don't know the reason as to why, since I just installed elasticsearch to investigate your question, the approach with measuring array using property values and function size() didn't work.
Based on all I have learned so far, the dynamic property, currently set to false on the shifts field may need to be modified to false, to enable dynamic scripting.
For aggregate approach above to work over nested field with text sub - field, the field may need to be applied with "fielddata" attribute set to true, by default if is false on such fields. It could be this is a new behavior since version 8.2.2. Here is error message I was getting in some experiments
"type" : "illegal_argument_exception",
"reason" : "Text fields are not optimised for operations that require per-document field data like aggregations and sorting, so these operations are disabled by default. Please use a keyword field instead. Alternatively, set fielddata=true on [...] in order to load field data by uninverting the inverted index. Note that this can use significant memory."
here is script to apply fielddata attribute:
shifts: {
type: 'object',
dynamic: false,
fielddata: true,
properties: { id: { type: 'keyword' }, { type: 'keyword' } },
}
-- cheers