Search code examples
databasetime-seriesgrafanaquestdb

Grafana variable dropdown with name and value with QuestDB data source

I am using Grafana with the QuestDB Data Source Plugin. I want to define a variable based on a query, like this query here

select symbol, first(price) as price from trades
where symbol like '%BTC%'
symbol price
DOGE-BTC 0.00000204
ETH-BTC 0.05551
BTC-USDT 37779.62
SOL-BTC 0.0015282
MATIC-BTC 0.00002074
BTC-USDC 60511.1
AVAX-BTC 0.00056124
XLM-BTC 0.00000314
LTC-BTC 0.001858
DOT-BTC 0.0001379
ADA-BTC 0.00001026
UNI-BTC 0.0001715
BTC-USD 39269.98

When I define a variable with that query, the dropdown contains only the price column. What I want is to display the symbol column as the dropdown visible label, but send behind the scenes the price column instead as the real value that will be interpolated in the SQL of the charts.

With the postgresql datasource I read that I can alias the columns with the special names __text and __value, as in:

select symbol as __name, first(price) as __value from trades
where symbol like '%BTC%'

And that works if I use the postgresql datasource even when pointing to a QuestDB instance. However, with the QuestDB datasource the aliases are ignored. If possible I want to keep using the QuestDB datasource, as it has better integration, but I would need a workaround for this issue.

Solution

  • We can take advantage of Grafana's regex variable filters. The trick is to get the results from the variable definition in a format we can easily parse via regexp.

    The goal is to have a format I can parse with a regex into two capture groups named text and value. So what I can do is concatenating my two table columns as a single string with a separator that never appears on those columns.

    with t as (
  select symbol, first(price) as price from trades where symbol like '%BTC%'
)
select concat(symbol,'#',price) from t

    Now on the variable definition I can enter a regexp filter like this

    /(?<text>[^#]+)\#(?<value>.*)/

    It will first capture everything from the beginning until the # with the name text and anything after the # and until the end with the name value. And with that Grafana will use the name at the dropdown, but the value when the variable is used anywhere in the charts.