How deeply does Just Use Postgres! cover jsonb_path_exists?

Explains filtered paths, current-value variables, array traversal, and chained filters, stopping short of broader implementation internals.

Jsonb_path_exists

As explained in Just Use Postgres!

jsonb_path_exists is a PostgreSQL function that returns whether a JSON path produces at least one result for a jsonb value.

jsonb_path_exists tests whether a JSON path produces at least one value for a jsonb column. A true result keeps the current SQL row. section 5.4.4

Find pizzas containing Parmesan

Filter cheese objects with `$.toppings.cheese[*] ? (exists(@.parmesan))`. The path visits every cheese, while `@` represents the current cheese object. Grouping the matching rows by pizza type returns three cheese and custom pizzas. section 5.4.4

Listing 5.9 Using filters in path expressions

SELECT 
    count(*) AS total_cnt,
    pizza->'type' as pizza_type
FROM pizzeria.order_items
WHERE jsonb_path_exists(pizza, 
        '$.toppings.cheese[*] ? (exists(@.parmesan))')
GROUP BY pizza_type
ORDER BY total_cnt DESC;

Run the complete filter query against pizzeria.order_items and compare its grouped counts with the supplied output. section 5.4.4

Listing 5.10 Chaining multiple filter expressions

SELECT count(*)
FROM pizzeria.order_items
WHERE jsonb_path_exists(
    pizza,
    '$ ? (@.type == "custom") .toppings.cheese[*].parmesan ? (@ == "extra")'
);

Explore chained filters that first select custom pizzas and then require Parmesan marked as extra. section 5.4.4

How Just Use Postgres! covers it

liveBook manning.com

Read Just Use Postgres! on liveBook Get the book at manning.com

Related concepts

json path expressionuses · A JSON path expression identifies locations in JSON data and can navigate, select, or filter objects and array elements.
jsonb_path_querycontrasts with · jsonb_path_query is a PostgreSQL function that evaluates a JSON path against a jsonb value and returns the matching JSON items.

This explainer was generated by Manning's knowledge graph from the text of Just Use Postgres!. The section links point to the parts of the book it draws on.