JSON represents structured data using JavaScript Object Notation. In PostgreSQL, it stores flexible objects such as customer pizza orders, while SQL can query and manipulate their fields. section 5.0 section 5.1
Choose JSON storage according to how the data will be used:
- Use text when PostgreSQL only needs to persist and retrieve the complete JSON string. section 5.1
- Use json or jsonb when database-level queries and structural manipulation are required; prefer jsonb by default for faster searches. section 5.1 section 5.7
- Use JSON for static, sparse, or schema-flexible data, and retain relational columns for frequently searched, joined, aggregated, or integrity-critical values. section 5.3
Pizza order payload
A pizza order can keep size, type, crust, sauce, and variable toppings in one JSON object. A hybrid table can store that object alongside order_id, order_item_id, and price as ordinary columns, combining flexible preferences with efficient lookup and constraints. section 5.1 section 5.3
{
"size": "medium",
"type": "margherita",
"crust": "regular",
"sauce": "marinara",
"toppings": {
"cheese": [
{"mozzarella": "regular"}
],
"veggies": [
{"tomato": "light"}
]
}
}
Inspect a representative JSON order with nested cheese and vegetable topping arrays. section 5.1
PostgreSQL supports key-existence and containment operators, JSON path expressions, jsonb_set for field updates, #- for removal, and GIN indexes for JSON access. section 5.7 section 5.6
