How deeply does Just Use Postgres! cover json path expression?

Builds paths from $, accessors, arrays, and filters, while deferring a complete treatment of every operator.

Json path expression

SQL/JSON path language is a standardized language for navigating and filtering JSON data within SQL expressions.

A JSON path expression navigates nested JSON objects and arrays inside PostgreSQL queries. It can retrieve values, test whether matching data exists, and replace longer subqueries with compact, readable path rules. section 5.4.4

Build a path from the JSON value outward:

Start with $ for the JSON object being queried. section 5.4.4
Follow object fields with accessors such as .type or .toppings.cheese. section 5.4.4

Find pizzas with sausage toppings

SELECT order_id, order_item_id, pizza->'toppings'->'meats' AS meats FROM pizzeria.order_items WHERE jsonb_path_exists( pizza, '$.toppings.meats[*] ? (exists(@.sausage))' ) ORDER BY order_id LIMIT 5; The same style of expression can filter custom pizzas whose parmesan topping has an extra quantity; that query returns 64 rows.

How Just Use Postgres! covers it

liveBook manning.com

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

Also explained in

liveBook manning.com

100 SQL Server Mistakes and How to Avoid ThemA JSON path is an expression that identifies a location within a JSON document, including object properties and array elements.

Compare how 2 books explain json path expression →

Related concepts

jsonb_path_queryused by · jsonb_path_query is a PostgreSQL function that evaluates a JSON path against a jsonb value and returns the matching JSON items.
jsonb_path_existsused by · jsonb_path_exists is a PostgreSQL function that returns whether a JSON path produces at least one result for a jsonb value.
json_valueused by · JSON_VALUE is a SQL Server function that extracts a scalar value from a JSON document at a specified path.
JSON objectuses · A JSON object is an unordered collection of members, each consisting of a string name paired with a JSON value and represented within curly braces.

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.