postgresql - Postgres JSON_AGG 和 Order By 不能一起工作

Question

我有一个如下所示的查询:

SELECT *,
(SELECT json_agg(deals.*) FROM deals WHERE vendors.id = deals.vendorid) as deals
FROM vendors

该查询效果很好，但是当我尝试按 deals 表中的字段进行排序时，如下所示:

SELECT *,
(SELECT json_agg(deals.*) FROM deals WHERE vendors.id = deals.vendorid ORDER BY priority) as deals
FROM vendors

我收到以下错误:

error: column "deals.priority" must appear in the GROUP BY clause or be used in an aggregate function

关于如何进行这样的排序有什么想法吗？

Answer 1

你可以

SELECT *,
(SELECT json_agg(deals.* ORDER BY priority) FROM deals WHERE vendors.id = deals.vendorid) as deals
FROM vendors

参见 examples with array_agg and string_agg .