Use group by with multiple columns from a nested json to get agggregate

[sandeepsingh2103]

I want to plot avg of a value picked from a nested json which is stored as jsonb object in a postgres column. Following query works for me in Postgres but I am not able to figure out how to use this in grafana for time series plotting:
Postgres Query:

SELECT 
      timestamp::TIMESTAMP::Date as date
      , http.key AS http_method
	 , hostname
     , s.key AS status_code
	 , avg((s.value->>'count')::int) AS avg_count
FROM   public.metrics t
     , jsonb_each(t.metrics->'http') http
     , jsonb_each(http.value->'statusCode') s
   GROUP  BY date, hostname, http.key, s.key;

I tried following in Grafana:

SELECT
  $__time(timestamp)
   , hostname as metric
   , avg((s.value->>'count')::int) AS value
   , http.key
   , s.key AS status_code
FROM
  public.metrics t
     , jsonb_each(t.metrics->'http') http
     , jsonb_each(http.value->'statusCode') s
WHERE
  $__timeFilter(timestamp)  and hostname like '%ABC%'
group by time, hostname, http.key, s.key
order by time

Thanks in advance for providing any guidance

[sandeepsingh2103]

I used a subquery structure in following format and it worked.

select $__time(timestamp), CONCAT(http_method, ‘-’,hostname, ‘-’, status_code) AS metric, avg(status_code_count) as value
from (
SELECT
timestamp::TIMESTAMP as timestamp
, http.key AS http_method
, hostname
, s.key AS status_code
, sum((s.value->>‘count’)::int) AS status_code_count
FROM public.metrics t
, jsonb_each(t.metrics->‘http’) http
, jsonb_each(http.value->‘statusCode’) s
where hostname like ‘%${ENVNAME}%${serviceList}%’

GROUP BY timestamp , hostname, http.key, s.key
) as RR where $__timeFilter(timestamp)
group by 1,2;