Brightlane's product specification API requires the warranty term to appear as a JSON number in the output, not a JSON string.
Write a query to return a JSON object for product id = 9 with the keys 'name' (the product name) and 'warranty_years' (the warranty term as a JSON number).
Assumptions:
- The
productstable has one row per product with anid, aname, and anattributesJSONB column. - Product
id = 9carries a'warranty_years'key in itsattributeswhose value is a JSONB number. - In the result JSON object, the
'warranty_years'value must be a JSON number — not a JSON string surrounded by quotes.
Output:
- A single row with one column,
product_spec, containing the JSON document with the keys'name'and'warranty_years'.
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
JSON_BUILD_OBJECT(
'name',
name,
'warranty_years',
(attributes ->> 'warranty_years')::INTEGER
) AS product_spec
FROM
products
WHERE
id = 9The shape
attributes->>'warranty_years' returns text, and json_build_object preserves the type of each value it receives, so passing text gives JSON string output. Casting the extraction with ::integer first turns the value into a PostgreSQL integer; json_build_object then writes it as a JSON number. The cast is the entire reason the API gets 3 instead of "3".
Clause by clause
SELECT json_build_object('name', name, 'warranty_years', (attributes->>'warranty_years')::integer) AS product_specbuilds the document with two keys. The'name'value comes from the column directly — JSON string out, no cast needed. The'warranty_years'value extracts the JSONB key as text, then::integerconverts the text to an integer beforejson_build_objectsees it. JSON number out.FROM productsreads the product records.WHERE id = 9narrows the input to the one product whose ID is 9,Meridian T1 Carbon. The function runs once and the query returns one row withproduct_specset to{ "name": "Meridian T1 Carbon", "warranty_years": 3 }.
Why this and not attributes->'warranty_years'
The -> operator returns JSONB, preserving the source type. If 'warranty_years' is stored as a JSONB number, attributes->'warranty_years' would also give the API 3 as a number, and the cast would be unnecessary. The reason the canonical query goes through ->> and then casts is that the prompt does not promise the source type — only that the value is a JSONB number. The text-then-cast path produces a JSON number from either a JSONB string or a JSONB number storage shape, so it is the robust answer.
The trap
->> always returns text. Passing text to json_build_object produces a JSON string in the output, surrounded by quotes. A reviewer scanning the result for "warranty_years": 3 versus "warranty_years": "3" will catch the missing cast immediately, but the query runs either way — no error, no warning. Any time the JSON output type matters and the source is being pulled through ->>, the cast is mandatory: ::integer for whole numbers, ::numeric for decimals, ::boolean for true/false. Without it, every value the API receives is a string.
You practiced (attributes ->> 'key')::integer inside json_build_object — the cast forces JSON-number output; without it the value would render as a JSON string because ->> returns text.
