Building JSON Responses

PostgreSQL can build complex JSON structures directly in SQL, which is extremely useful when your application needs to return nested data. json_build_object and jsonb_build_object construct a JSON object from alternating key-value arguments. json_agg and json_build_array create arrays. row_to_json converts a full row to a JSON object. json_object_agg creates an object by aggregating key-value pairs. These functions can be composed to build deeply nested JSON that exactly matches the shape expected by your API consumers, avoiding the need to do multiple queries and assemble the result in application code. This pattern can significantly reduce latency for read-heavy endpoints.

Example

-- Build a nested JSON response in one query
SELECT json_build_object(
  'id',      u.id,
  'name',    u.name,
  'email',   u.email,
  'orders',  COALESCE(
    json_agg(
      json_build_object(
        'id',        o.id,
        'total',     o.total,
        'status',    o.status,
        'items', (
          SELECT json_agg(
            json_build_object('product', p.name, 'qty', oi.qty, 'price', oi.unit_price)
          )
          FROM order_items oi
          JOIN products p ON p.id = oi.product_id
          WHERE oi.order_id = o.id
        )
      ) ORDER BY o.created_at DESC
    ) FILTER (WHERE o.id IS NOT NULL),
    '[]'::json
  )
) AS user_with_orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.id = 1
GROUP BY u.id, u.name, u.email;

-- json_object_agg — build an object from aggregated pairs
SELECT json_object_agg(name, price)
FROM products WHERE category_id = 1;
-- {"Keyboard": 79.99, "Mouse": 39.99}

HTML

Full Editor

Related Resources

PostgreSQL Reference

Complete tag & property list

PostgreSQL How-To Guides

Step-by-step practical guides

PostgreSQL Exercises

Practice what you've learned