Brightlane's print fulfillment system processes orders for physical books and needs every paperback product.
Write a query to return the ID and name of every product whose 'format' attribute is 'Paperback'.
Assumptions:
- The
productstable has one row per product with anid, aname, and anattributesJSONB column. - A paperback product has its
'format'key inattributesset to the text value'Paperback'. - Only paperback products should appear in the result.
Output:
- One row per qualifying product, with columns
idandname.
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
id,
name
FROM
products
WHERE
attributes ->> 'format' = 'Paperback'The shape
The same ->> extraction that produces a column for display also works inside a WHERE predicate, because the result is plain text and the comparison target ('Paperback') is also plain text. With both sides of the comparison already typed as text, the equality check works directly with no cast needed.
Clause by clause
SELECT id, namereturns the product's ID and name from their columns. No extraction is needed in the SELECT here because the question only asks which products qualify, not what their format value is.FROM productsreads the product catalog, where each row'sattributesJSONB document carries whatever keys describe that product type.WHERE attributes ->> 'format' = 'Paperback'runs the JSONB extraction per row: for each product, it reaches intoattributes, pulls the value at the'format'key as text, and keeps the row only if that text is exactly'Paperback'. Products whoseattributeshas no'format'key get a NULL from the extraction, so the equality is false and they don't appear.
Why this and not attributes -> 'format' = 'Paperback'
The -> form would return JSONB instead of text. A JSONB string includes its surrounding quotes as part of the value, so the comparison would be against the JSONB "Paperback", not the text 'Paperback'. PostgreSQL would either need a JSONB literal on the right-hand side ('"Paperback"'::jsonb) or raise a type-mismatch error. ->> strips the type metadata so the comparison stays in plain text, which is the easier and more common pattern for filtering on a known string value.
You practiced WHERE attributes ->> 'key' = 'value' — text-typed extraction inside a WHERE predicate; the equality comparison works directly because both sides are text.
