JSONB: JSON Documents in a Column
JSONB is a PostgreSQL data type for JSON documents. JSON is a common text format for data, with keys and values, like {"color": "black", "ram": 8}.
JSONB is useful when different rows have different details. In the products table, the details column is JSONB:
SELECT name, details FROM products;
Output:
name | details
------------+----------------------------------------
Laptop | {"ram": 16, "color": "silver"}
Phone | {"ram": 8, "color": "black"}
Desk | {"color": "brown", "material": "oak"}
Chair | {"color": "black", "material": "mesh"}
Headphones | {"color": "white", "wireless": true}
(5 rows)
Notice that the laptop and phone have ram, while the desk and chair have material instead. Also, JSONB may store the keys in a different order than you wrote them. The laptop's details were inserted as {"color": "silver", "ram": 16}. The order of keys does not matter in JSON.
Example 1: Get a Value with ->>
The ->> operator gets the value of a key, as text:
SELECT name, details ->> 'color' AS color
FROM products;
Output:
name | color
------------+--------
Laptop | silver
Phone | black
Desk | brown
Chair | black
Headphones | white
(5 rows)
Example 2: Filter by a JSON Value
SELECT name FROM products
WHERE details ->> 'color' = 'black';
Output:
name
-------
Phone
Chair
(2 rows)
Example 3: Compare Numbers
->> always returns text. To compare a value as a number, change its type with ::INT. This is called a cast:
SELECT name, (details ->> 'ram')::INT AS ram
FROM products
WHERE (details ->> 'ram')::INT > 8;
Output:
name | ram
--------+-----
Laptop | 16
(1 row)
Example 4: Check if a Key Exists
The ? operator checks if a JSON document has a key:
SELECT name FROM products
WHERE details ? 'wireless';
Output:
name
------------
Headphones
(1 row)
Only the headphones have a wireless key.
Tip
Use JSONB for details that change from row to row. Keep the columns that every row has, like name and price, as normal columns.
In the exercise, read a value from the details column.