Grafana 9.2.10 + Elasticsearch: How to extract part of a field for unique count aggregation

Grafana Table Panel
1

Hello,

I am using Grafana 9.2.10 with Elasticsearch as a data source.

In one of my fields (e.g., A), the data is stored in the following format:

xxx-yyy

I would like to extract only the xxx part (before the hyphen) and then perform a unique count (Uniq Count) aggregation based on this value.

What I have tried so far:

  • Created a variable and tried using a regex to extract the prefix

  • Attempted Group by and other Transform options

  • However, while the variable is recognized, the panel does not show values correctly and aggregation does not work as expected

Additional details:

  • In Grafana 9.2.10, the Query Editor does not have a Lucene Query / Field input box, so I’m not sure how to reference the variable directly in the panel

  • The existing Elasticsearch index cannot be modified

Questions:

  1. Is it possible in Grafana 9.2.10 to extract a substring from a field and perform a unique count aggregation without modifying the existing Elasticsearch index?

  2. If yes, could you provide a concrete example of how to configure Variables, Transform, and Query to achieve this?

Thank you very much for your help.