Product Count and Name Array — JSONB Aggregation in SQL

The problem

Brightlane's category management dashboard needs both a summary count and a structured product list per category.

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.

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 product count is the number of products linked to that category_id. The product-names array contains every name value of those products (one element per product).

Output:

One row per category, with columns category_id, product_count, 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,
  COUNT(id) AS product_count,
  JSONB_AGG(name) AS product_names
FROM
  products
GROUP BY
  category_id

The shape

Two aggregates over the same GROUP BY partition do separate jobs: COUNT(id) returns a single integer per category, jsonb_agg(name) returns a JSON array per category. Both look at the same set of rows for each category and produce columns of different shapes, side by side in the same result row.

Clause by clause

SELECT category_id, COUNT(id) AS product_count, jsonb_agg(name) AS product_names returns three columns: the grouping key, a scalar count, and an array of names. COUNT(id) counts the rows in each group; jsonb_agg(name) collects the name values from those same rows into a JSONB array. Both aggregates evaluate against the same per-category set of input rows, just with different output types.
FROM products reads the product records.
GROUP BY category_id defines the per-category partition. For category 8 the partition has 5 products: COUNT(id) returns 5 and jsonb_agg(name) returns the five-element array ["Men's Slim Jeans", "Men's Polo Shirt", ...]. Same partition, two different summaries.

The trap

The natural assumption is that COUNT(id) and the length of the jsonb_agg(name) array must always be equal. They are equal here because every row has a non-null name and a non-null id. They are not equal in general: both aggregates skip NULL input values, so if name were nullable, the array could be shorter than the count. Match the column inside each aggregate to whatever the question is actually asking about.

You practiced combining a scalar aggregate (COUNT) with a structural aggregate (jsonb_agg) in the same GROUP BY query — both operate over the same partition and produce columns of different shapes.

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`

The shape

Clause by clause

The trap

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