sql - 分组成json数组

Question

使用 Postgres 9.5

对于每个 app，我试图计算有多少 conversations 是从 started_at_url 列的各种 url 开始的

我已经能够得到这样的结果

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

但它宁愿使用 {url, count} 形式的 JSON 对象数组来代替

到目前为止我的查询:

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

有什么建议吗？

谢谢

Answer 1

为什么不用

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

代替

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