JSONB Aggregation (jsonb_agg, json_build_object) in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this JSONB Field Extraction, GROUP BY
What is JSONB Aggregation 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.
How do you build a JSON array from SQL rows?
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.
SELECT c.name AS category,
jsonb_agg(json_build_object('name', p.name, 'price', p.price)) AS products
FROM products p
JOIN categories c ON c.id = p.category_id
GROUP BY c.name
LIMIT 5The 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.
What is the difference between json and jsonb build functions?
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.
How does jsonb_agg handle NULL values?
The one thing that trips people up
jsonb_agg does not skip NULLs, and that is what surprises people, because nearly every other aggregate does. Feed it a row whose entire expression is NULL and the array still gets an element — a JSON null. COUNT and SUM over those same rows would have ignored the row entirely. Use FILTER (WHERE ... IS NOT NULL) when you want it left out. A NULL field inside json_build_object behaves the same way: the key is still there, carrying a JSON null value.
When you only want some rows to appear in the array, use FILTER:
jsonb_agg(json_build_object('id', oi.product_id) FILTER (WHERE oi.quantity > 0))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.
Should you use jsonb_agg or STRING_AGG?
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.
Practice JSONB Aggregation in SQL
Brightlane's product catalog service needs every product name in each category collected into a structured JSON array.
Write a query to return every category ID alongside a JSON array of product names for that category.
Assumptions:
- The
productstable has one row per product with anameand acategory_id. - Each
category_idwith at least one product should appear once. - For each category, the array contains every
namevalue of products in that category (one element per product, no de-duplication).
Output:
- One row per category, with columns
category_idandproduct_names.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution10 JSONB Aggregation practice problems
Write a query to return every category ID alongside a JSON array of product names for that category.
Write a query to return a JSON object for product id = 7 containing the product's ID, name, and price under the keys 'id', 'name', and 'price'.
Write a query to return every category ID and a JSON array of objects — one per product — where each object contains the keys 'id' and 'name' for that product.
Write a query to return every category ID, the total number of products in that category as product_count, and a JSON array of those product names as product_names.
Write a query to return a JSON object for every product whose 'format' attribute is 'Paperback'. The object must have keys 'id', 'name', and 'pages', where 'pages' is the value extracted from the product's attributes under the 'pages' key.
Write a query to return one JSON array collecting every event whose event_type is 'page_view'. Each array element is a JSON object with keys 'id' (the event ID) and 'page' (the page path from the event's properties).
Write a query to return every category ID, the total price of qualifying products in that category as total_price, and a JSON array of those product names as product_names.
Write a query to return every category ID and a JSON array of 'color' attribute values for products in that category, pulling each color from the product's attributes.
Write a query to return every category ID and a JSON array of objects — one per product in that category — where each object contains the product's id under the key 'id' and the product's color attribute under the key 'color'.
Write a query to return a JSON object for product id = 9 with the keys 'name' (the product name) and 'warranty_years' (the warranty term as a JSON number).
Start learning to practice all 10 JSONB Aggregation problems, with instant grading and mastery tracking.
Deeper guides on JSONB Aggregation
- controlling the order of a JSON array
ORDER BY belongs inside the parentheses. At the end of the query it raises a GROUP BY error instead.
- collecting rows into a JSON array
jsonb_build_object for the shape, jsonb_agg to collect the rows, and one nested document from two tables.
- what json_agg returns when there are no rows
Wrap it in coalesce. After a LEFT JOIN you get [null] instead, and coalesce will not fix that one.
Common questions about JSONB Aggregation
What does jsonb_agg return for an empty group?
NULL, not an empty array. If a downstream consumer expects an array in every case, wrap it in COALESCE with an empty array literal so the shape stays consistent whether or not the group had rows.
Does json_build_object drop keys whose value is NULL?
No. The key stays and its value is JSON null, so the shape of the object is the same for every row. That is the opposite of how the aggregate treats a wholly NULL row, which it leaves out of the array entirely.
Can you nest one json_build_object inside another?
Yes, and that is how you build a structure more than one level deep. The inner object becomes the value of a key in the outer one, which is what lets a single query return the shape an API expects rather than a flat result.
