jsonb is PostgreSQL’s binary JSON type. It parses and decomposes JSON when values are written, making later field searches faster than repeatedly parsing textual JSON. The conversion removes whitespace and duplicate keys and may reorder keys, so writes can be slightly slower and returned formatting may differ. section 5.1 section 5.7
Use jsonb when an application needs to query or manipulate JSON inside PostgreSQL:
- Extract nested fields with JSON operators and traverse objects or arrays. section 5.1
- Check whether an object contains a key with ?, including array elements expanded by jsonb_array_elements(). section 5.4.2
- Filter records by nested JSON content with the containment operator @>. section 5.4.3
- Optimize searches with specialized GIN indexes; use jsonb_set() and #- for JSON updates and removals. section 5.7
Filter pizza orders by nested JSON content
Store the payload in a jsonb column: CREATE TABLE pizzeria.order_items ( order_id INT NOT NULL, order_item_id INT NOT NULL, pizza JSONB NOT NULL, price numeric(5,2) NOT NULL, PRIMARY KEY (order_id, order_item_id) ); Count gluten-free custom pizzas containing extra tomatoes: SELECT count(*) FROM pizzeria.order_items WHERE pizza @> '{"crust": "gluten_free", "type": "custom", "toppings": {"veggies": [{"tomato": "extra"}]}}'; The sample dataset returns 7 matching items. To find pizzas with meat toppings, navigate to the array and test each element: SELECT order_id, order_item_id, pizza->'toppings'->'meats' AS meats FROM pizzeria.order_items WHERE EXISTS ( SELECT 1 FROM jsonb_array_elements(pizza->'toppings'->'meats') AS meat WHERE meat ? 'sausage' ) ORDER BY order_id LIMIT 5; section 5.2 section 5.4.2 section 5.4.3
{
"size": "medium",
"type": "margherita",
"crust": "regular",
"sauce": "marinara",
"toppings": {
"cheese": [
{"mozzarella": "regular"}
],
"veggies": [
{"tomato": "light"}
]
}
}
Inspect a representative jsonb pizza payload with scalar fields, nested objects, and arrays. section 5.1
