Limit number of results from array_agg

Question

I'm struggling with limiting the number of results returned from array_agg(). This is my query so far:

SELECT s.*,
(
  SELECT array_agg(row_to_json(smr.*))
  AS "monthRatings"
)
FROM services s
INNER JOIN servicemonthratings smr ON s._id = smr.service_id
GROUP BY s._id

Is there maybe a better way to accomplish the same thing as above?

Answer 1

Simpler:

SELECT s.*, array_agg(row_to_json(smr)) AS "monthRatings"
FROM   services s
JOIN   servicemonthratings smr ON s._id = smr.service_id
GROUP  BY s._id;

• No need for a subselect.
• You can just use the table alias smr (meaning the row type) instead of smr.*, same result here.
• services._id must be the PK, or the query is nonsense.

I suspect you really want a JSON array of rows, and you don't want to eliminate services that have no ratings (yet):

SELECT s.*, smr."monthRatings"
FROM   services s, LATERAL (
   SELECT json_agg(smr) AS "monthRatings"
   FROM   servicemonthratings smr
   WHERE  smr.service_id = s._id
   ) smr;

Now you don't need to aggregate services, and _id does not have to be the PK.

And since this LATERAL subquery always returns a row (due to the aggregate function in it) no services are eliminated.