SQL
Generate JSON Array from SQL Query Results
Learn how to directly construct a JSON array of objects from your SQL query results in PostgreSQL, ideal for API responses or data interchange.
-- PostgreSQL Example: Generate a JSON array of users
SELECT json_agg(row_to_json(users_alias))
FROM (
SELECT
id,
username,
email,
created_at
FROM
users
WHERE
is_active = TRUE
ORDER BY
created_at DESC
LIMIT 10
) users_alias;
-- For a JSON object where user IDs are keys
SELECT json_object_agg(id, row_to_json(users_alias))
FROM (
SELECT
id,
username,
email
FROM
users
WHERE
is_active = TRUE
) users_alias;
How it works: This snippet demonstrates PostgreSQL's powerful JSON functions to convert SQL query results directly into JSON. The `json_agg` function aggregates rows into a JSON array, where each row becomes a JSON object. `json_object_agg` can create a JSON object using a specified column as keys, which is extremely useful for web APIs and data serialization.