Brightlane's product color index records the color attribute for every product in each category.
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.
Assumptions:
- Each
category_idwith at least one product should appear once. - For each category, the array contains one element per product in that category. Products with a
'color'key inattributescontribute their text color value; products without a'color'key contribute a JSON null array element. - The order of elements within each array is unspecified.
Output:
- One row per category, with columns
category_idandcolors.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
category_id,
JSONB_AGG(attributes ->> 'color') AS colors
FROM
products
GROUP BY
category_idThe shape
attributes->>'color' extracts the color value as text for each product, returning NULL when the 'color' key is absent. jsonb_agg then collects every one of those values — NULLs included — into a JSON array, converting each NULL to a JSON null element. Every product in the category gets one element in the array, color or no color.
Clause by clause
SELECT category_id, jsonb_agg(attributes->>'color') AS colorsreturns the grouping column and the per-group array. The->>operator returns text, so present colors come out as text strings and absent colors come out as NULL text values.jsonb_aggthen represents each text value as a JSON string in the array, and each NULL as a JSON null.FROM productsreads the product records.GROUP BY category_idpartitions by category. For category4(books) every product is missing a'color'key, so the array comes out as six JSON nulls in a row. For category8(men's clothing) three of the five products have a color and two don't, so the array is[null, "Navy", null, "Olive", "Blue"].
The trap
The behaviour of jsonb_agg on NULL inputs is the load-bearing thing here, and it cuts against the usual aggregate intuition. COUNT(col) skips NULLs. SUM(col) skips NULLs. jsonb_agg(col) does not skip NULLs — it preserves them as JSON null elements in the array. That is exactly why the audit shape works: every product contributes an element, even the ones without a color on record, and the array length per category matches the product count per category. If you swap in jsonb_agg(attributes->>'color') FILTER (WHERE attributes->>'color' IS NOT NULL) thinking the nulls need explicit handling, the array shrinks to only the products with a recorded color, and categories like 4 disappear from the output entirely because every row in the group has been filtered away from the aggregate.
You practiced jsonb_agg(attributes ->> 'key') — the aggregate includes JSON null for records where the extracted value is missing, preserving one element per source record.
