TIL (okay, a couple days ago) that there is a json_agg()
function in PostgreSQL.
SELECT
r.review_id,
r.review_timestamp,
json_agg(a.*) AS answers
FROM $schema.review r
INNER JOIN $schema.answer a ON a.review_id = r.review_id
WHERE -- stuff
GROUP BY review_id, review_timestamp
Now I get just one row returned for each relevant review, and each row contains a JSON string with all the answers associated with that review. 🙂