Extract Warranty Years as Text — JSONB Field Extraction in SQL

The problem

Brightlane's warranty registration system retrieves the warranty term for a specific product. Product id = 9 has a 'warranty_years' entry in its attributes.

Write a query to return the ID, name, and warranty term for product id = 9. The warranty term should be returned as the text value stored under 'warranty_years' in the product's attributes (no numeric cast).

Assumptions:

The products table has one row per product with an id, a name, and an attributes JSONB column.
Product id = 9 carries a 'warranty_years' key in its attributes.
The warranty-years column holds the value extracted from attributes as plain text.

Output:

A single row with columns id, name, and warranty_years_text.

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 ->> 'warranty_years' AS warranty_years_text
FROM
  products
WHERE
  id = 9

The shape

->> returns whatever sits at the requested JSONB key as a PostgreSQL text value, regardless of what type the value carries inside the JSONB document. The warranty term is stored as a JSONB number, but the warranty-registration column needs the text form, so a single extraction with ->> is the whole shape.

Clause by clause

SELECT id, name, attributes ->> 'warranty_years' AS warranty_years_text returns the product's ID and name from their columns, then extracts the value under the 'warranty_years' key from the attributes JSONB document and returns it as text. The AS warranty_years_text alias signals in the column name that this is the text form.
FROM products reads the product catalog.
WHERE id = 9 narrows the read to the single row for the Meridian T1 Carbon, so the extraction runs against one JSONB document and the result is one row.

The trap

->> always returns text. When the underlying JSONB value is a number like 3, the operator hands back the string '3', not the integer 3. That string looks identical when displayed in a result panel, but any arithmetic or numeric comparison done against it will either coerce in unexpected ways or raise a type error. Whenever the next step on the extracted value is math or a numeric threshold, an explicit ::numeric or ::integer cast has to follow the ->>. This problem only asks for the text form, so no cast is needed here, but the same extraction with a > 1 comparison would need one.

You practiced ->> for text-typed extraction — even when the underlying JSONB value is a number, ->> returns it as text and any arithmetic requires an explicit cast.

Return the ID, name, and warranty term for product `id = 9`. The warranty term should be returned as the text value stored under `'warranty_years'` in the product's `attributes` (no numeric cast)

The shape

Clause by clause

The trap

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