How to group on labels in aggregate query to Promscale

Question

I am creating SQL queries from Grafana into Promscale. There are the metric and the labels. I can not get the correct way to group by some of the labels. I tried:

SELECT time_bucket('$__interval', "time") AS "time",
       AVG("value") AS "used"
  FROM "disk_used_percent"
 WHERE $__timeFilter("time") AND
       "labels" ? ('host' == '$host_pg')
 GROUP BY 1, "labels" --> 'path'
 ORDER BY 1;

as well as:

SELECT time_bucket('$__interval', "time") AS "time",
       AVG("value") AS "used"
  FROM "disk_used_percent"
 WHERE $__timeFilter("time") AND
       "labels" ? ('host' == '$host_pg')
 GROUP BY 1, "path_id"
 ORDER BY 1;

but it does not seem the grouping works as expected. What is wrong? Corresponding PromQL query would be:

avg(disk_used_percent{host=~"$host_prom"}) by(path))

Answer 1

You can use VAL("<label>_id") to group on:

SELECT time_bucket('$__interval', "time") AS "time",
       VAL("path_id") AS "path",
       AVG("value") AS "used"
  FROM "disk_used_percent"
 WHERE $__timeFilter("time") AND
       "labels" ? ('host' == '$host_pg')
 GROUP BY 1, 2
 ORDER BY 1;

Side note: also avoid using the $__timeFilter("time") templating macro in Grafana because it generates the following predicate:

"time" BETWEEN 'time range begin' AND 'time range end'

which may be problematic under certain circumstances.