Postgres Practice 1/12

JSONB: read fields out of a JSON column

Standard SQL columns hold one value each: a name, an age, a price. Real data doesn't always fit that shape - a product might have a handful of optional attributes that vary from row to row (color, wireless, size...), and adding a column for every possible attribute gets unwieldy fast. Postgres's JSONB column type stores a whole JSON document in one column, so each row can carry its own shape.

To pull a value back out, Postgres gives you two arrow operators. -> returns the value as JSON (useful when you're nesting further into an object or array). ->> returns it as plain text - what you want anywhere you're displaying the value or comparing it as a string.

You have a products table with columns id, name, and attrs (a JSONB column). Every product's attrs has a "color" key.

Your task: return each product's name and its color (from attrs), as color.

You'll practice:

Pulling a field out of a JSONB column with ->>
Knowing when to reach for ->> (text) instead of -> (JSON)

-- attrs is a JSONB column with a "color" key on every row.
-- Pull it out as plain text, aliased as color.
SELECT name, attrs FROM products;