Cast a JSON Field to Integer — JSONB Field Extraction in SQL

The problem

Brightlane's product quality team audits consumer electronics with extended warranty coverage.

Write a query to return the ID, name, and warranty length in years for every product whose warranty term exceeds 1 year.

Assumptions:

The products table has one row per product with an id, a name, and an attributes JSONB column.
Products with extended warranty have a 'warranty_years' key in their attributes whose value is a JSONB number.
The warranty-years output column carries the value extracted from attributes and represented as an integer. Only products whose warranty term is strictly greater than 1 should appear.

Output:

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

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')::INTEGER AS warranty_years
FROM
  products
WHERE
  (attributes ->> 'warranty_years')::INTEGER > 1

The shape

Composes JSONB extraction with type casting. ->> returns the warranty value as text, then ::integer converts that text to a number so the > 1 comparison is numeric. The same extract-and-cast appears twice: once in the SELECT (so the output column is an integer) and once in the WHERE (so the filter compares numbers, not strings).

Clause by clause

SELECT id, name, (attributes ->> 'warranty_years')::integer AS warranty_years returns the product's ID and name, then extracts the warranty value as text and casts it to an integer for the output column. The parentheses are required because :: would otherwise try to bind to 'warranty_years' instead of the whole extraction expression.
FROM products reads the product catalog.
WHERE (attributes ->> 'warranty_years')::integer > 1 runs the same extract-and-cast per row and keeps the row only when the integer warranty term is strictly greater than 1. Products with no 'warranty_years' key extract to NULL, the cast of NULL is NULL, and NULL > 1 is unknown, so those rows are dropped automatically.

The trap

Without the cast, the comparison would be lexical, not numeric. ->> returns text, so '10' > '1' is a string comparison that does happen to be true here, but '2' > '10' would be false because '2' sorts after '1'. With longer numbers the order breaks: '9' > '10' is true as text, false as numbers. The ::integer cast is what makes > 1 mean "greater than one as a number" instead of "lexicographically after the character one." Any time a JSONB-extracted value goes into a numeric comparison, the cast is mandatory.

You practiced (attributes ->> 'key')::integer — extract as text, then cast to a numeric type for both the output column and the threshold comparison.

Return the ID, name, and warranty length in years for every product whose warranty term exceeds `1` year

The shape

Clause by clause

The trap

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