Array of Product Objects — JSONB Aggregation in SQL

The problem

Brightlane's category listing endpoint needs each product represented as a structured object inside a per-category JSON array.

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.

Assumptions:

The products table has one row per product with an id, a name, and a category_id.
Each category_id with at least one product should appear once.
For each category, the array contains one object per product. Each object has exactly two keys, 'id' and 'name', populated from the product's record.

Output:

One row per category, with columns category_id and products.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  category_id,
  JSONB_AGG(JSON_BUILD_OBJECT('id', id, 'name', name)) AS products
FROM
  products
GROUP BY
  category_id

The shape

jsonb_agg(json_build_object('id', id, 'name', name)) runs the inner function once per product to build a two-key JSON object, then the outer aggregate collects those objects into one JSONB array per category. The two functions compose: json_build_object shapes each row, jsonb_agg collects the shaped rows.

Clause by clause

SELECT category_id, jsonb_agg(json_build_object('id', id, 'name', name)) AS products returns the grouping column and the per-group array of product objects. The inner json_build_object('id', id, 'name', name) is evaluated once for each input row, producing an object like { "id": 14, "name": "Men's Slim Jeans" }. The outer jsonb_agg then collects those objects across every row in the group into a single JSONB array.
FROM products reads the product records.
GROUP BY category_id defines the groups the outer aggregate works over. One distinct category_id per output row, with the array of that category's product objects in the products column.

Why this and not `json_build_object('id', id, 'name', name)` alone

Without jsonb_agg wrapping it, json_build_object runs once per row and the query returns one row per product — not one row per category. The aggregate is what collapses the per-row objects into a per-group array.

You practiced jsonb_agg(json_build_object(...)) — combine per-row object construction with per-group array aggregation; the inner json_build_object runs once per record, the outer jsonb_agg collects the results into an array.

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

The shape

Clause by clause

Why this and not `json_build_object('id', id, 'name', name)` alone

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

The shape

Clause by clause

Why this and not json_build_object('id', id, 'name', name) alone

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

Why this and not `json_build_object('id', id, 'name', name)` alone