postgresql - Postgresql 以多列分组聚合

Question

如何将此表聚合为单行 json 对象？

有这样的要求：

select 
    tb.book_id,
    json_agg(json_build_object('id', t.id, 'name', t.name, 'likes', tb.likes) order by tb.likes desc) as tags
from 
    tags_books as tb
join 
    tags as t on tb.tag_id = t.id 
where 
    tb.book_id = 2 
group by 
    tb.book_id

结果：

[{"id" : 2, "name" : "Для взрослых", "likes" : 3}, {"id" : 3, "name" : "Приключения", "likes" : 2}, {"id" : 1, "name" : "Экшн", "likes" : 1}, **{"id" : 4, "name" : "Комедия", "likes" : 1}, {"id" : 4, "name" : "Комедия", "likes" : 1}**]

我几乎得到了我想要的，但我需要以某种方式将两个标签聚合在一起（最后一行'Комедия'）并获得喜欢的总和。

结果应该是这样的：

[{"id" : 2, "name" : "Для взрослых", "likes" : 3}, {"id" : 3, "name" : "Приключения", "likes" : 2}, {"id" : 1, "name" : "Экшн", "likes" : 1}, {"id" : 4, "name" : "Комедия", "likes" : **2**}]

标签： postgresql

Answer 1

我想我走错了方向，最后决定拆分表格，所以现在一切正常：