A JSON object is an unordered collection of name-value members. It represents structured or semi-structured data that applications can store, query, and modify. In a pizza-ordering application, one object can hold the pizza size, type, crust, sauce, and nested topping categories. section 5.0 section 5.1
{
"size": "medium",
"type": "margherita",
"crust": "regular",
"sauce": "marinara",
"toppings": {
"cheese": [
{"mozzarella": "regular"}
],
"veggies": [
{"tomato": "light"}
]
}
}
Inspect a complete pizza-order JSON object with scalar fields, a nested toppings object, and arrays of ingredient objects. section 5.1
Postgres can persist an object as text when it only needs to store and retrieve the value. When database-side processing is required, the json and jsonb data types expose fields and nested structure to SQL queries, with different storage trade-offs. section 5.1
Filter orders by object contents
The jsonb containment operator checks whether one object contains another. This query counts orders whose top-level object includes a gluten-free crust: SELECT count(*) FROM pizzeria.order_items WHERE pizza @> '{"crust": "gluten_free"}'; It returns 749 rows in the supplied example. Adding "type": "custom" narrows the count to 148; adding nested extra tomatoes narrows it to 7. section 5.4.3
Modify stored JSON objects with targeted database operations:
- Use jsonb_set to replace a scalar field such as crust or an array such as toppings.veggies. section 5.5
- Use the #- operator to remove a path such as toppings.meats. section 5.5
- Fetch, change, and rewrite the entire object through the application layer when that workflow is appropriate. section 5.5
