Postgres JSON_AGG and Order By not working together

Question

I have a query that looks like this:

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

That query works great, however when I try and order by a field in the deals table like this:

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

I get the following error:

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

Any ideas how to do a sort like this?

Answer 1

You can

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

See examples with array_agg and string_agg.

Answer 2

Shouldn't the syntax be this?

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

The documentation is here.

Answer 3

As a side note, if you don't limit the amount of deals queried, you should use a left join instead of a subquery:

SELECT *
FROM vendors
LEFT JOIN (
  SELECT vendorid, json_agg(* ORDER BY priority) AS deals
  FROM deals
  GROUP BY vendorid
)
ON vendors.id = deals.vendorid