Search code examples
grafanagrafana-templating

Grafana Reference DataSet Variable to Translate Legend Values using Postgres Driver

I have a postgres data-source in Grafana that's normalized which restricts my graph-visualization legend to show only the ID (hash) of my record. I want to make this human-readable but the id -> name mapping is in a different datasource/postgres database.

Grafana supports templating-variables which I think could allow me to load my id -> naming reference data but there isn't clear documentation on how to access the label_values as a reference-table within the postgres driver's query editor.

Is there a way to configure the template variable to load reference data (id -> name) & leverage it to translate my metric/legend ids within the grafana postgres driver?

For Example (pseudo-grafana postgres query editor):

SELECT
  $__timeGroupAlias(start,$__interval),
  animal_names.__value AS metric,
  count(dog.chewed_bones) AS “# bones chewed“
FROM animals.dog dog
JOIN $TEMPLATE_VAR_REF_DATA animal_names ON dog.id = animal_names.__text
WHERE $__timeFilter(start_time)
GROUP BY 1,2
ORDER BY 1,2

Closest answer I found is here but doesn't get into details: johnymachine's comment @ https://github.com/grafana/grafana/issues/1032

Solution

  • I realized the github comment meant use a jsonb aggregate function as a variable like in the following solution:

    Dashboard Variable (Type Query): select jsonb_object_agg(id,name) from animal_names;

    Grafana Postgres Pseudo-Query: 

    SELECT
  $__timeGroupAlias(start,$__interval),
  animal_names::jsonb ->> dog.id::text AS metric,
  count(dog.chewed_bones) AS “# bones chewed“
FROM animals.dog
WHERE $__timeFilter(start_time)