Table as variable

[csekealpar12]

Hello!

Is there any way of having an (SQL-like) table as a variable? Easy or even obfuscated (e.g.: retrieving results from a hidden panel).
Which could be used for mapping user names coming from a different database than the main query, somewhat like:

SELECT (SELECT $uservar.name FROM $uservar WHERE $uservar.id = userID), ... FROM userdata ...
or
SELECT (SELECT name FROM $uservar WHERE id = userID), ... FROM userdata ... .

[wlargou]

Hi,

I guess you can achieve this by using dashboard variables of type query and then filter on those.

Hope it helps.

[csekealpar12]

If it was this simple, I wouldn’t have asked it.

Both examples in my post are modeled after how dashboard variables work, but AFAIK it cannot do what I want. If you look at my queries, I need to store and use something like (1 → alice, 2 → bob, 3 → charlie), and not simply (1, 2, 3) or (alice, bob, charlie).

And the
SELECT id as __value, name as __text ... dashboard variable syntax doesn’t help me either, because I can only get the value from it in the query editor, not the name as well.

[wlargou]

Hi,

You can try to create a variable user as query that will have “John-39” → Concatenation of username and ID and then on your select you can substring that using SQL. (check an example : Get everything after and before certain character in SQL Server - Stack Overflow).

Hope it helps.

Good Luck

[csekealpar12]

It’s been a while, but I found two solutions for anyone stumbling upon this question.

• Create the two queries on the same panel from the two different datasources.
• Make sure there is a common field (with the same name).
• On the Transformations tab, first create either a “Merge” or an “Outer Join”. Then an “Organize Field” and hide the column with the internal ID used for the join.
• Doesn’t work on timeseries graphs for some reason.

• Create a hidden grafana variable (no name and hidden label) to select all id-name pairs into a single string, e.g.:
  SELECT string_agg(CONCAT(l.id, '=', s.name), '#') ...
• In the query, join your table with this variable split up to a table:
  FROM mytable INNER JOIN (SELECT * FROM regexp_split_to_table('$myhiddenvar', E'\#+') as idcolumn) hiddentablename ON mytable.idcolumn = cast(substr(hiddentablename.idcolumn, 0, strpos(hiddentablename.idcolumn, '=')) as int)
• Now you can select the name in the query similarly to the join condition:
  SELECT substr(hiddentablename.idcolumn, strpos(hiddentablename.idcolumn, '=') + 1) as namecolumn, other_selected_fields
• Works in all cases.