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.