jsonb_path_query evaluates a JSON path against a jsonb value and returns the matching JSON items. A path starts at $ and navigates fields with .key. section 5.4.4
Group pizzas by type
SELECT count(*) AS total_cnt, jsonb_path_query(pizza, '$.type') AS pizza_type FROM pizzeria.order_items GROUP BY pizza_type ORDER BY total_cnt DESC; The query extracts each pizza's type, groups matching values, and produces counts such as pepperoni, three cheese, veggie, custom, and margherita. section 5.4.4
Use JSON path expressions to navigate objects and arrays:
- $.toppings.cheese[*] navigates to the cheese array and returns every object in it. section 5.4.4
- Pass returned objects to jsonb_object_keys to extract cheese names such as mozzarella, cheddar, and parmesan. section 5.4.4
- Combine extraction with grouping and counting to build business reports from JSON data. section 5.4.4
Listing 5.7 Accessing JSON fields with path expressions
SELECT
count(*) as total_cnt,
jsonb_path_query(pizza,'$.type') as pizza_type
FROM pizzeria.order_items
GROUP BY pizza_type ORDER BY total_cnt DESC;
Runnable SQL demonstrates extracting pizza types with jsonb_path_query and aggregating the results. section 5.4.4
