Brightlane's product data quality report checks which attributes are populated on each product. Product id = 34 is being inspected, and the team needs to know what value (if any) sits under its 'color' key.
Write a query to return the ID, name, and color attribute for product id = 34.
Assumptions:
- The
productstable has one row per product with anid, aname, and anattributesJSONB column. - Different products carry different keys in their
attributes. Some products have a'color'key on record; others do not. - Product
id = 34does not have a'color'key in itsattributes. As a result, the row appears in the output with a missing value in thecolorcolumn.
Output:
- A single row with columns
id,name, andcolor. Thecolorcolumn is missing for this product.
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,
attributes ->> 'color' AS color
FROM
products
WHERE
id = 34The shape
When the requested key doesn't exist in a JSONB document, ->> returns NULL instead of raising an error. The row still appears in the result, the value is just missing in the extracted column. This is the silent-NULL behavior that makes JSONB queries safe for heterogeneous data and dangerous for code that assumes the key is always present.
Clause by clause
SELECT id, name, attributes ->> 'color' AS colorreturns the product's ID and name, then attempts to extract'color'from theattributesJSONB document as text. For product 34, theattributesdocument has no'color'key (it's a book, not a styled product), so the extraction returns NULL.FROM productsreads the product catalog.WHERE id = 34narrows the read to the single row for Building Scalable Systems.
Why this and not WHERE attributes ->> 'color' IS NOT NULL
The data quality report wants to know what value (if any) sits under the 'color' key for this specific product. Filtering out NULL rows would hide exactly the answer the audit is looking for — that product 34 has no color attribute. The silent-NULL return is the signal here, not a problem to filter away. The row showing up with color = NULL is how the audit learns that the key is missing.
The trap
JSONB's silent-NULL behavior cuts both ways. For a data quality audit (like this one), it's the right behavior: the row appears with a NULL value, and the analyst can see which products are missing the key. For a downstream system that assumes every product has a color, it's a quiet data-leak: the NULL flows out of the query without any indication that a key was missing rather than empty. The rule is to be explicit about which behavior is wanted. When the analysis needs to distinguish "key present with NULL value" from "key absent entirely," PostgreSQL provides containment operators for the check; for a simple presence audit like this, the NULL in the column is the audit's whole result.
You practiced ->> over a missing key — when the requested key is absent from the JSONB object, the operator returns missing without raising an error; the row still appears in the result.
