Use PostgreSQL’s -> operator to navigate JSON objects and arrays while keeping the extracted value in JSON format. Apply it when an application needs selected fields rather than an entire JSON document, such as a pizza’s size, crust, or toppings. section 5.4 section 5.4.1
Keep these behaviors in mind:
- Write object keys as single-quoted strings: pizza->'size'. The result preserves JSON formatting, so a text value appears with double quotes. section 5.4.1
- Use ->> when the extracted value must become PostgreSQL text; compare each result using its corresponding representation. section 5.4.1
- Chain -> operators to descend through nested objects, then use an integer index such as ->0 to extract an array element. section 5.4.1
Extract and filter pizza details
SELECT order_id, order_item_id, pizza->'size' AS pizza_size, pizza->>'crust' AS pizza_crust FROM pizzeria.order_items WHERE order_id = 100 AND pizza->'size' = '\"small\"' AND pizza->>'crust' = 'gluten_free'; The result includes the matching order item with pizza_size shown as \"small\" and pizza_crust shown as gluten_free. Omitting the JSON quotes from the size comparison produces an invalid JSON token error.
Reach nested toppings
SELECT order_id, order_item_id, pizza->'toppings'->'veggies'->0->>'onion' AS onions_amount FROM pizzeria.order_items WHERE order_id = 100; This traverses the toppings object, selects the veggies array’s first element, and converts onion’s value to text. The result is light for the onion pizza and empty for nonmatching items.
Listing 5.3 Chaining the -> operator
SELECT
order_id, order_item_id,
pizza->'toppings'->'veggies' as veggies_toppings
FROM pizzeria.order_items
WHERE order_id = 100;
Inspect the complete chained query for retrieving nested vegetable toppings as JSON. section 5.4.1
