JSONB Aggregation (jsonb_agg, json_build_object) in SQL

jsonb_agg and json_build_object turn relational rows into JSON structure. They're what you reach for when a query needs to deliver nested data instead of the flat multi-row output SQL normally produces.

A common scenario: your API endpoint expects one JSON object per category, with all products in that category embedded as an array. Fetching categories and products with a JOIN gives you one row per product — one row per category is what the API needs. These two functions collapse that into one row per category with a structured JSON array of products.

The two functions work together. json_build_object packages multiple columns from a single row into a JSON object. jsonb_agg then collects those objects across rows into a JSON array, one object per row.

The GROUP BY collapses all products for each category into one output row. json_build_object converts each product row into a JSON object with two keys: name and price. jsonb_agg collects those objects into an array. The ORDER BY inside jsonb_agg controls the sequence of elements within each array — independent of any ORDER BY at the query level.

json_build_object takes alternating key-value arguments: key, value, key, value. Keys must be text. Values can be any type that has a JSON representation — integers become JSON numbers, text becomes JSON strings, NULL becomes JSON null.

jsonb_build_object vs json_build_object

The difference is the return type: json_build_object returns json, jsonb_build_object returns jsonb. For storing results in a jsonb column or passing them to other JSONB functions, use the jsonb_ variant. For most output purposes, both behave identically.

The one thing that trips people up

NULL handling differs between the aggregate call and the object construction. If the entire expression passed to jsonb_agg evaluates to NULL, that row is excluded from the array — same as any aggregate ignoring NULLs. But if a specific field inside json_build_object is NULL, the key is still included in the resulting object with a JSON null value. The row itself is not excluded.

When you only want some rows to appear in the array, use FILTER:

This is most useful in the final shaping step of a query — after all joins and group-bys are done and the task is packing the results into a format a downstream API or pipeline expects.

jsonb_agg vs STRING_AGG

If the goal is a display-ready list of values from one column, STRING_AGG is simpler: STRING_AGG(product_name, ', ') gives you a comma-separated string directly. Use jsonb_agg with json_build_object when you need to capture multiple fields per row, preserve the structure for downstream processing, or produce output that an API or pipeline expects to parse as JSON. The moment you need more than one field per collected row, the JSON functions are the right tool.