Postgres json_agg() function

The Postgres json_agg() function is an aggregate function that collects values from multiple rows and returns them as a single JSON array.

It's particularly useful when you need to denormalize data for performance reasons or prepare data for front-end applications and APIs. For example, you might use it to aggregate product reviews for an e-commerce application or collect all posts by a user on a social media platform.

Function signature

The json_agg() function has this simple form:

json_agg(expression) -> json

• expression: The value to be aggregated into a JSON array. This can be a column, a complex expression, or even a subquery.

When used in this manner, the order of the values in the resulting JSON array is not guaranteed. Postgres supports an extended syntax for aggregating values in a specific order.

json_agg(expression ORDER BY sort_expression [ASC | DESC] [NULLS { FIRST | LAST }]) -> json

• expression: The value to be aggregated into a JSON array.
• ORDER BY: Specifies the order in which the values should be aggregated.
• sort_expression: The expression to sort by.
• ASC | DESC: Specifies ascending or descending order (default is ASC).
• NULLS { FIRST | LAST }: Specifies whether nulls should be first or last in the ordering (default depends on ASC or DESC).

Example usage

Consider an orders table with columns order_id, product_name, and quantity. We can use json_agg() to create a JSON array of all products in each order.

WITH orders AS (
    SELECT *
    FROM (
        VALUES
            (1, 'Widget A', 2),
            (1, 'Widget B', 1),
            (2, 'Widget C', 3),
            (2, 'Widget D', 2)
    ) AS t(order_id, product_name, quantity)
)
SELECT
  order_id,
  json_agg(json_build_object('product', product_name, 'quantity', quantity)) AS products
FROM orders
GROUP BY order_id;

This query groups the orders by order_id and creates a JSON array of products for each order.

order_id |                                       products
----------+--------------------------------------------------------------------------------------
        1 | [{"product" : "Widget A", "quantity" : 2}, {"product" : "Widget B", "quantity" : 1}]
        2 | [{"product" : "Widget C", "quantity" : 3}, {"product" : "Widget D", "quantity" : 2}]
(2 rows)

Advanced examples

Ordered aggregation

You can specify an order for the aggregated values, as suggested in the function signature section. Here's an example:

WITH reviews AS (
  SELECT 1 AS product_id, 'Great product!' AS comment, 5 AS rating, '2023-01-15'::date AS review_date
  UNION ALL SELECT 1, 'Could be better', 3, '2023-02-01'::date
  UNION ALL SELECT 1, 'Awesome!', 5, '2023-01-20'::date
  UNION ALL SELECT 2, 'Not bad', 4, '2023-01-10'::date
)
SELECT
  product_id,
  json_agg(
    comment || ' (' || rating || ' stars)'
    ORDER BY review_date DESC
  ) AS reviews
FROM reviews
GROUP BY product_id;

This query aggregates product reviews into a JSON array, ordered by the review date in descending order.

product_id |                                     reviews
------------+---------------------------------------------------------------------------------
          1 | ["Could be better (3 stars)", "Awesome! (5 stars)", "Great product! (5 stars)"]
          2 | ["Not bad (4 stars)"]
(2 rows)

Combining with other JSON functions

json_agg() can be combined with other JSON functions for more complex transformations:

WITH sales AS (
  SELECT 'North' AS region, 'Q1' AS quarter, 100000 AS amount
  UNION ALL SELECT 'North', 'Q2', 120000
  UNION ALL SELECT 'South', 'Q1', 80000
  UNION ALL SELECT 'South', 'Q2', 90000
)
SELECT
    region,
    json_agg(
        (SELECT json_build_object('quarter', quarter, 'amount', amount))
        ORDER BY quarter DESC
    ) AS quarterly_sales
FROM sales
GROUP BY region;

This query uses json_build_object() in combination with json_agg() to create an array of quarterly sales data, for each region.

region |                                quarterly_sales
--------+--------------------------------------------------------------------------------
 North  | [{"quarter" : "Q2", "amount" : 120000}, {"quarter" : "Q1", "amount" : 100000}]
 South  | [{"quarter" : "Q2", "amount" : 90000}, {"quarter" : "Q1", "amount" : 80000}]
(2 rows)

Additional considerations

Performance implications

While json_agg() is powerful for creating JSON structures, it can be memory-intensive for large datasets since its output size linearly increases with the number of rows. When working with very large tables, consider using pagination or limiting the number of rows aggregated.

Alternative functions

• array_agg(): Aggregates values into a Postgres array instead of a JSON array.
• jsonb_agg(): Similar to json_agg(), but returns a jsonb type, which is more efficient for storage and processing.
• json_agg_strict(): Aggregates values into a JSON array, skipping over the NULL values.

Resources

• PostgreSQL documentation: Aggregate Functions
• PostgreSQL documentation: JSON Functions and Operators