Extract a Color Field — JSONB Field Extraction in SQL

The problem

Brightlane's product merchandising system displays color attributes for catalog items.

Write a query to return the ID, name, and color attribute for product id = 7.

Assumptions:

The products table has one row per product with an id, a name, and an attributes JSONB column.
Each product's attributes value is a JSONB object whose keys depend on the product type.
Product id = 7 has a 'color' key in its attributes. The result should pull that value out as plain text.

Output:

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

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 ->> 'color' AS color
FROM
  products
WHERE
  id = 7

The shape

The ->> operator pulls a value out of a JSONB column and hands it back as plain text, which is exactly what the catalog display needs for the color field. With id = 7 already narrowing the read to a single row, the extraction runs once and the result is one row with three columns.

Clause by clause

SELECT id, name, attributes ->> 'color' AS color returns the product's ID and name straight from their columns, then reaches into the attributes JSONB object for the value stored under the 'color' key and returns it as text. The AS color alias gives that extracted value a clean column name in the result.
FROM products reads the product catalog, where each row's attributes is its own JSONB document with whatever keys that product type carries.
WHERE id = 7 filters the read down to the single row for product 7, so the JSONB extraction only runs against that one row and the result has exactly one row in it.

Why this and not `attributes -> 'color'`

Both operators read the same key, but -> keeps the result as JSONB and ->> strips it to text. For a display column where the consumer is the merchandising UI, text is what's wanted. A JSONB string carries its surrounding quotes as part of the value, so -> would return "Midnight" (quotes and all) instead of Midnight. The ->> form is the right one any time the next thing to do with the value is show it, compare it, or hand it to anything that isn't another JSONB operator.

You practiced attributes ->> 'key' — extract a JSONB object's value at a given key as a PostgreSQL text value.

Return the ID, name, and color attribute for product `id = 7`

The shape

Clause by clause

Why this and not `attributes -> 'color'`

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

The shape

Clause by clause

Why this and not attributes -> 'color'

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

Why this and not `attributes -> 'color'`