Postgres:如何返回json类型的投影数据

Question

这可能是个小问题，但我想知道postgres json输出的功能。

下表：

id | seconds | datetime 
1  | 10      | 2020-08-21 08:42:58.26+08
2  | 20      | 2020-08-21 10:20:00.01+08
3  | 10      | 2020-08-22 08:00:00.10+08

这样可以在json中输出吗？

[{
"date" : "2020-08-21",
"seconds_1" : 10,
"seconds_2" : 20,
},
{
"date" : "2020-08-22",
"seconds_1" : 10
}]

我可以通过php/javascript操作表结果，但只是想知道这在postgres中是否可行。

Answer 1

这需要多步骤聚合：

select jsonb_agg(item)
from (
select jsonb_build_object('date', dt)|| jsonb_object_agg(concat('seconds_', rn), seconds) item
from (
select datetime::date as dt,
row_number() over (partition by datetime::date) as rn, 
seconds
from the_table
) t
group by dt  
) r

最内部的查询用于按日期对行进行编号，这不能在按日期分组的同一级别上完成，因为这样的编号就会错误(因为窗口函数在分组后进行评估(

第二级聚合所有"；秒"；为每个日期创建一个JSON值。最后一级将所有内容聚合到一个JSON数组中。

在线示例

---

如果你不关心使"；秒"；密钥唯一，您可以使用id列并稍微简化查询：

select jsonb_agg(item)
from (
select jsonb_build_object('date', datetime::date)|| jsonb_object_agg(concat('seconds_', id), seconds) item
from the_table
group by datetime::date
) r