I want to create a query variable in grafana that contains data of more than one column. The database i use is Postgresql. In my dashboard i use this query a bunch o times so i would like to obtain the data as a variable once in order to make faster my dashboard.
Imagine the query for the variable is: SELECT id, name FROM country;
The variable saves the information as a collection of the data. For example: 1,2,3,spain,germany,usa. But not as a table.
How can i access to the data of the variable as if it was a table? Is it posible?
I have tried to make a normal query and treat it as table but i couldnt. i have treated the variable as ( $variable) , ${variable}:raw, $(variable.name), ${variable:raw}.name
Grafana doesn't support multifield variables.
Closest to this supported feature would be separation of variable name (or key) and value.
How to achieve named variables within query is described in step 4 here:
Make sure that the query returns values named
__textand__valueas appropriate in your query syntax. For example, in SQL, you can use a query such asSELECT hostname AS __text, id AS __value FROM MyTable. Queries for other languages will vary depending on syntax.
So in your case it will be SELECT id as __value, name as __text FROM country. This will result in values from column name being shown in drop-down. And value from column id being substituted when using variable $variable. *
But this approach will only allow you to have two columns in use. If you need any more, you'll need to use some other approaches, as chained variables.
* : name of variable can still be used in other queries with syntax ${variable:text}, as described here.