Priced Products with Name Array — JSONB Aggregation in SQL

The problem

Brightlane's warranty coverage report needs per-category pricing totals and product name lists, restricted to products that have a warranty term on record.

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.

Assumptions:

A qualifying product has a 'warranty_years' key in its attributes. Products with no 'warranty_years' key on record do not contribute to the totals or to the array.
Each category_id with at least one qualifying product should appear once.
For each category, the total price is the combined price across qualifying products. The names array contains one element per qualifying product.

Output:

One row per category with at least one qualifying product, with columns category_id, total_price, 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,
  SUM(price) AS total_price,
  JSONB_AGG(name) AS product_names
FROM
  products
WHERE
  (attributes ->> 'warranty_years') IS NOT NULL
GROUP BY
  category_id

The shape

WHERE runs before either aggregate, so restricting to products that have a 'warranty_years' key in attributes shrinks the input first; then SUM(price) and jsonb_agg(name) both operate over only the surviving rows. The total and the names array stay aligned by construction.

Clause by clause

SELECT category_id, SUM(price) AS total_price, jsonb_agg(name) AS product_names returns three columns per group: the category, a scalar sum, and a JSONB array of names. Both aggregates see the same per-category partition, so the product_names array contains exactly the products whose prices contributed to total_price.
FROM products reads the product records.
WHERE (attributes->>'warranty_years') IS NOT NULL extracts the 'warranty_years' key as text and keeps only the rows where that extraction returned a value. Rows where the key is absent in attributes get NULL back from ->> and are dropped here, before grouping. This is why categories like 8 (clothing) never appear in the output — none of their products have a warranty term on record.
GROUP BY category_id partitions the surviving rows by category. Categories with no qualifying products produce no row at all, since the filter has already removed every potential member.

The trap

The shape that looks equivalent but isn't is SUM(CASE WHEN attributes->>'warranty_years' IS NOT NULL THEN price END) with no WHERE. The sum would still come out right — SUM skips NULLs — but jsonb_agg(name) would include every product in the category, including the non-qualifying ones, because the filter is inside one aggregate and not the other. Filtering in the WHERE keeps both aggregates working over the same rows.

You practiced jsonb_agg and SUM in the same query after a JSONB-key restriction — the WHERE runs first; both aggregates operate over only the surviving records.

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`

The shape

Clause by clause

The trap

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