Tarlen Tarlen - 3 months ago 9
SQL Question

Group into json array

Using Postgres 9.5

For each

app
, I am trying to count how many
conversations
was started from various urls valued by the
started_at_url
column

I've been able to get a result like this

id: 1, ..., data: {"urls" : ["/","/insights","/signin"], "counts" : [1,2,2]}


But it would rather have an array of JSON objects of form
{url, count}
instead

My query so far:

select
dt.id as app_id,
json_build_object(
'urls', array_agg(dt.started_at_url),
'counts', array_agg(dt.count)
) as data
from (
select a.id, c.started_at_url, count(c.id)
from apps a
left join conversations c on c.app_id = a.id
where started_at_url is not null and c.started_at::date > (current_date - (7 || ' days')::interval)::date
group by a.id, c.started_at_url
) as dt
group by dt.id


Any suggestions?

Thanks

Answer

Why not use

array_agg(
   json_build_object(
      'url', dt.started_at_url,
      'count', dt.count
   )
)

instead of

json_build_object(
   'urls', array_agg(dt.started_at_url),
   'counts', array_agg(dt.count)
)