In PostgreSQL, the json_agg() function is an aggregate function which returns an array in JSON format containing values of what is between the brackets ().

Structure

SELECT json_agg(expression)
FROM TABLE
WHERE [CONSTRAINTS]
GROUP BY [EXPRESSION]

Example

Suppose we want to output the sporting equipment that each individual has borrowed. Here is how we would do that:

SELECT b.name, json_agg(e.name) as equipment from borrowers b
JOIN equipment_borrowed e ON e.id = b.id
WHERE b.active = 1
GROUP BY b.name;

Output

name	equipment
Tom	[“Basketball”, “Soccer ball”]
Jenn	[“Volleyball”, “Tennis equipment”]
Tony	[“Hockey stick”, “Hockey puck”]