Aggregate Names Into an Array — JSONB Aggregation in SQL

The problem

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 products table has one row per product with a name and a category_id.
Each category_id with at least one product should appear once.
For each category, the array contains every name value of products in that category (one element per product, no de-duplication).

Output:

One row per category, with columns category_id and product_names.

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(name) AS product_names
FROM
  products
GROUP BY
  category_id

The shape

jsonb_agg(name) collects every product name in each group into a single JSONB array, so one row per category falls out of the GROUP BY carrying the array of names for that category.

Clause by clause

SELECT category_id, jsonb_agg(name) AS product_names returns the grouping column alongside the aggregated array. jsonb_agg runs once per group and packs every name value in the group into one JSONB array, in the order the rows are encountered. The alias product_names names the output column.
FROM products reads the product records — each row carries a name and a category_id.
GROUP BY category_id collapses the rows to one per distinct category_id, which is what makes jsonb_agg produce one array per category instead of one giant array across the whole table. The null category in the result is its own group, the same way every GROUP BY treats a NULL key as a distinct value.

You practiced jsonb_agg(column) — aggregate column values into a JSONB array, one element per record in each GROUP BY partition.

Return every category ID alongside a JSON array of product names for that category

The shape

Clause by clause

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