Brightlane's data engineering team is validating how the two JSONB extraction operators differ in output type. The team needs the same 'pages' value extracted both ways for product id = 34.
Write a query to return the ID, the page count extracted as a JSONB value in a column named pages_jsonb, and the page count extracted as a text value in a column named pages_text for product id = 34.
Assumptions:
- The
productstable has one row per product with anidand anattributesJSONB column. - Product
id = 34has a'pages'key in itsattributeswhose value is a JSONB number. - The
pages_jsonbcolumn carries the JSONB-typed extraction of'pages'. Thepages_textcolumn carries the text-typed extraction of the same key.
Output:
- A single row with columns
id,pages_jsonb, andpages_text.
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,
attributes -> 'pages' AS pages_jsonb,
attributes ->> 'pages' AS pages_text
FROM
products
WHERE
id = 34The shape
-> and ->> are the same key-lookup operator with one difference: -> keeps the result as JSONB and ->> converts it to text. Running both against the same key on the same row puts the two return types side by side in the result, which is exactly what the data engineering team is auditing.
Clause by clause
SELECT id, attributes -> 'pages' AS pages_jsonb, attributes ->> 'pages' AS pages_textreturns the product's ID, then runs two extractions against the same'pages'key. The first uses->and labels the resultpages_jsonb— the value comes back with its JSONB type metadata intact. The second uses->>and labels the resultpages_text— the value comes back as plain text.FROM productsreads the product catalog.WHERE id = 34narrows the read to the single row for Building Scalable Systems.
Why this and not -> everywhere
The JSONB form is what's needed when the next operation is another JSONB step. Chaining -> 'address' ->> 'city' works because the first -> returns JSONB that the second operator can navigate into. The text form is what's needed when the next operation is display, comparison, or arithmetic (with a cast). Picking the right operator at each step is the everyday JSONB skill: -> while you're still inside the document, ->> at the boundary where the value leaves JSONB and becomes a regular SQL value.
The trap
The two values look identical in a result panel because both render as 600. The difference is invisible at display time but load-bearing for any downstream operation. Sending the pages_jsonb column into an arithmetic expression would raise a type error (JSONB doesn't support +), while sending pages_text would need a ::numeric cast first. The display sameness hides the type difference, which is why audits like this one matter.
You practiced -> versus ->> side by side — -> keeps the result as JSONB (preserving type structure); ->> strips it to plain text. Same source key, two different output types.
