Right, so you’ve got all this lovely JSONB data sitting in your columns, and you’re probably thinking, “Great, I can query it. But how do I make it?” Because sometimes you need to assemble your own JSON structures on the fly, either to return from an API, feed into another system, or just to show off. That’s where jsonb_agg() and jsonb_build_object() come in. They’re your power tools for constructing JSON directly within a SQL statement, saving you from the nightmare of string concatenation (a path that leads only to madness and escaping errors).

Think of it this way: jsonb_build_object() is your brick-and-mortar for building individual JSON objects, while jsonb_agg() is the forklift that piles those bricks (or any other rows) into a JSON array. Used together, they’re unstoppable.

Building the Bricks: jsonb_build_object()

This function is brilliantly simple. You give it a series of key-value pairs, and it returns a pristine jsonb object. The key is a text argument, and the value can be almost any data type—text, integer, boolean, even another jsonb object. PostgreSQL will happily convert its native types to their JSON equivalents.

SELECT jsonb_build_object(
  'name', u.username,
  'is_admin', u.is_admin,
  'signup_year', EXTRACT(YEAR FROM u.created_at),
  'preferences', '{}'::jsonb -- building an empty object is a common trick
)
FROM users u
LIMIT 1;

This might return: {"name": "alice", "is_admin": true, "signup_year": 2023, "preferences": {}}

The beauty here is clarity. You’re explicitly defining the structure. The alternative, using the || concatenation operator, gets messy fast: ('{"name": "' || u.username || '"})'::jsonb. Don’t do that. You’ll inevitably forget to escape a quote or a backslash, and I’ll have to come find you.

The Gotcha: The number of arguments must be even. It’s a series of key, value, key, value… If you provide an odd number, PostgreSQL will throw a fit, and rightly so. There’s no such thing as a key without a value here.

The Mighty Aggregator: jsonb_agg()

This is where the magic happens. jsonb_agg() is an aggregate function, like SUM() or COUNT(). It takes a set of values from multiple rows and packs them into a single JSON array. The values it aggregates can be simple column values or, more powerfully, complex expressions or even other JSON objects built with jsonb_build_object.

Want a simple array of usernames? Easy.

SELECT jsonb_agg(username) FROM users;
-- Result: ["alice", "bob", "carol"]

But that’s boring. Let’s get fancy. This is the classic, killer use case: building a nested JSON structure that represents a one-to-many relationship right inside your main query.

SELECT
  u.username,
  u.email,
  jsonb_agg(
    jsonb_build_object(
      'id', p.id,
      'title', p.title,
      'tags', p.tags -- assuming 'tags' is a jsonb column itself
    )
    ORDER BY p.created_at DESC -- Yep, you can order the aggregation!
  ) AS posts
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username, u.email; -- You MUST group by the non-aggregated columns

This query would return one row per user, with their details and a beautifully formatted array of their post objects, all ordered from newest to oldest. This is incredibly powerful for crafting API responses directly in the database layer.

The Pitfalls and Power Moves

First, the big one: always use a LEFT JOIN when you aggregate from another table. If you use an INNER JOIN and a user has zero posts, the entire user row will be excluded from the result because the join finds nothing to match. A LEFT JOIN ensures the user row survives, and jsonb_agg() will simply produce an empty array ([]) for them, which is almost always what you want.

Second, watch your GROUP BY. You must include every non-aggregated column from your SELECT clause in the GROUP BY clause. This can get tedious. This is where PostgreSQL 15+ shines—if your SELECT clause includes the primary key, you can just GROUP BY u.id and still select other columns from u (like username). It’s a lifesaver.

Third, jsonb_agg() returns NULL if it aggregates over zero rows. Not an empty array, but actual NULL. This is a common source of errors in application code that expects an array. You can fix this easily with COALESCE:

SELECT
  u.username,
  COALESCE(
    jsonb_agg(
      jsonb_build_object('title', p.title)
      -- FILTER (WHERE p.id IS NOT NULL) is another useful tool here
    ),
    '[]'::jsonb
  ) AS posts
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id;

Finally, remember that building massive JSON documents inside PostgreSQL is still work. It consumes memory and CPU. For truly enormous nested structures, sometimes it’s more efficient to do the aggregation in your application logic. But for 99% of use cases, doing it right in the query is the cleanest, fastest, and most elegant approach. You’re offloading the work to a system designed to handle data, and you’re moving less data over the wire. It’s a win-win. Now go build something.