Group into json array

Question

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

Laurenz Albe · Accepted Answer · 2016-08-28 19:30:41Z

1

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)
)

answered Aug 28, 2016 at 19:30

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Group into json array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related