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

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

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;