Filter Products by Material — JSONB Field Extraction in SQL

The problem

Brightlane's sustainability audit reviews the materials used across the clothing line.

Write a query to return the ID, name, and material attribute for every product whose 'material' attribute is 'Denim'.

Assumptions:

The products table has one row per product with an id, a name, and an attributes JSONB column.
A clothing product has a 'material' key in its attributes whose value is a text string.
Only products whose 'material' attribute is exactly 'Denim' should appear in the result.

Output:

One row per qualifying product, with columns id, name, and material.

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
  id,
  name,
  attributes ->> 'material' AS material
FROM
  products
WHERE
  attributes ->> 'material' = 'Denim'

The shape

One extraction expression, used in two places. attributes ->> 'material' runs in the SELECT to produce the output column, and the same expression runs in the WHERE to restrict the result to denim products. The extractor is just a regular SQL expression, so it can appear anywhere an expression is allowed.

Clause by clause

SELECT id, name, attributes ->> 'material' AS material returns each product's ID and name, then extracts the value under the 'material' key from the attributes JSONB document as text. The AS material alias gives the extracted value a clean column name.
FROM products reads the product catalog.
WHERE attributes ->> 'material' = 'Denim' runs the same extraction per row and keeps the row only when the text result is exactly 'Denim'. Products without a 'material' key extract to NULL, so the equality is false (NULL doesn't equal anything) and they're filtered out without needing an explicit null check.

Why this and not extracting once into a column?

PostgreSQL evaluates the JSONB extraction per row regardless of how many times the expression appears in the query, so writing it twice doesn't cost extra reads — it's the same JSONB document being navigated. The advantage of repeating it (over, say, computing it once in a subquery and reusing the result) is that the query stays a single straightforward read with no extra structure. The cost of repetition here is just a few characters of SQL. For a problem with this many comparisons over the same extracted value, that's the right trade.

You practiced ->> in both the SELECT and WHERE clauses — the same extraction expression used twice in one query, once for output and once for restriction.

Return the ID, name, and material attribute for every product whose `'material'` attribute is `'Denim'`

The shape

Clause by clause

Why this and not extracting once into a column?

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