Group by on Derived string field

Elastic Stack Elasticsearch
1

Hi. I need to take the first K characters of a string field (which is not analyzed) and aggregate on that. I know how to aggregate, but is it possible to aggregate on a derived field in ES?

2

Maybe you can use a script aggregation ?

https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline-bucket-script-aggregation.html

What do you mean by :

a derived field

?

3

Hi. By 'derived field' I mean something like (using SQL) LEFT(my_field, 10) as the_value. In sql I would be doing:
select the_value, count(*) from (
select LEFT(my_field, 10) as the_value from table
) aaa group by the_value
In this case the_value is a 'derived' field.

4

OK, in this case the script aggregation is possible. the code should be something like that:

"script": "my_field == null ? null : my_field.substring(0, (my_field.length() > 10 ? 10 : my_field.length()))"

(not tested :wink: )

1 Like
5

Thank you xavierfacq!

6

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.