SQL/JSON path and building

Brew gained drink recipes: each has an array of ingredients with gram weights, nested inside jsonb. In the morning Ruslan forwards a request from the barista trainer into the chat:

Ruslan (in chat, 08:40)

Trainer's asking: which recipes have milk, and what's heavier than a hundred grams. Allergens for the new hires. Before opening.

The questions are easy with a menu in hand, but for the database each one means "walk the nested ingredient array and answer." Writing this with the ->/#> chains from 07-01 is clumsy: extract the array, unfold it, filter it, fold it back. For nested documents Postgres has a better language — jsonpath.

The goal of this unit is to learn to extract data from nested jsonb with path expressions (jsonb_path_query_*, the @?/@@ predicates) and to build jsonb back (jsonb_set, jsonb_build_object, jsonb_agg). Along the way we note a version subtlety: JSON_TABLE (unfold jsonb into a relational table right in FROM) is PG17, not PG18; we don't need it here, and the jsonb_path_* and building functions have existed since PG12.

jsonpath: a path as a mini-language

jsonpath is the standard addressing language inside JSON. $ is the document root, .name a field, [*] all array elements, [0] a specific one, and ? (@.grams > 100) is a filter: keep only elements where the condition is true (@ is the current element). So $.ingredients[*] ? (@.grams > 100).name reads as "names of ingredients with more than 100 grams." The path is applied by jsonb_path_query_array (collect all matches into one jsonb array) and jsonb_path_query_first (take the first). One compact path replaces the manual array unpacking via ->.

Path predicates: @? and @@

Sometimes you don't need values — you need a yes/no answer. There are two operators for that. @? is "does at least one path match exist": recipe @? '$.ingredients[*] ? (@.name == "milk")' is true if milk is among the ingredients. @@ is "is the condition true as a predicate": recipe @@ '$.kcal > 100' is true for high-calorie drinks. The difference is subtle (@? is about a node existing, @@ about a boolean check), but both return boolean and both can lean on a GIN index (jsonb_path_ops) on large tables.

Building: jsonb_set, build_object, jsonb_agg

The reverse task is to assemble jsonb. jsonb_set(doc, '{path}', value) patches a field precisely and returns a new document (the stored row is unchanged — it's a pure function; the same "edit = rebuild the value" that causes the write amplification in 07-02). jsonb_build_object('a', x, 'b', y) builds an object from key-value pairs, and the aggregate jsonb_agg(... ORDER BY ...) folds result rows into one jsonb array. Together they assemble an API response right in SQL: for example, the whole menu from the drinks table as one document.

Does jsonb_set edit the document right there in the database?

Dmitry

It returns a new one. What rewriting a whole document costs — we saw last week.

The path step by step

The expression $.ingredients[*] ? (@.grams > 100).name is a route through the document. The latte recipe and a walk over it:

plaintext

  recipe = { "kcal": 190,
             "ingredients": [ {"name":"espresso", "grams":30},
                              {"name":"milk",     "grams":220} ] }
 
  $                   document root
   .ingredients       → the ingredients field (an array of two objects)
              [*]      → each array element
                 ? (@.grams > 100)   → filter: keep where grams > 100
                                       espresso 30 ✗  ·  milk 220 ✓
                          .name       → take name of the survivors
  ───────────────────────────────────────────────────────────────
  result: ["milk"]

Each step narrows the set: field → elements → filter → field. The same selection via ->/#> chains would have to be assembled by hand — extract the array, unfold it, filter it, fold it back.

What our code shows

A lab table drink_recipe_lab (recipes with a nested ingredient array) for paths, and building over the drinks table:

sql

SELECT jsonb_path_query_array(recipe, '$.ingredients[*].name'),                    -- all names
       jsonb_path_query_array(recipe, '$.ingredients[*] ? (@.grams > 100).name'),  -- heavy ones
       jsonb_path_query_first(recipe, '$.ingredients[0].name')                     -- first
FROM drink_recipe_lab WHERE id = 1;                                                -- PathQueries
 
SELECT name, recipe @? '$.ingredients[*] ? (@.name == "milk")', recipe @@ '$.kcal > 100'
FROM drink_recipe_lab;                                                             -- PathPredicates
 
SELECT jsonb_set(recipe, '{kcal}', '130') ->> 'kcal' FROM drink_recipe_lab WHERE id = 1;  -- SetField
 
SELECT jsonb_agg(jsonb_build_object('sku', sku, 'price_cents', base_price) ORDER BY id)
FROM drinks;                                                                       -- BuildMenu

sqlc doesn't know the signatures of jsonb_path_*/@?/@@ from the catalog and types their result as interface{}; pgx returns concrete string/bool into them, so the demo prints via %v. The unit adds its own table to the schema → make db-reset applies it via brew.Apply.

Running it

docker compose up -d
make lecture L=07-jsonb-arrays-and-search/07-03-sql-json-path-and-building T=db-reset
make lecture L=07-jsonb-arrays-and-search/07-03-sql-json-path-and-building

Output:

plaintext

1) jsonpath по рецепту «Латте» ($.ingredients[*], фильтр ? (@.grams > 100)):
   все ингредиенты   $.ingredients[*].name              = ["espresso", "milk"]
   тяжёлые (>100 г)   ... ? (@.grams > 100).name         = ["milk"]
   первый             $.ingredients[0].name (first)      = "espresso"
 
2) предикаты пути @? и @@ по всем рецептам:
НАПИТОК   @? есть milk  @@ kcal > 100
Латте     true          true
Эспрессо  false         false
Колд брю  false         false
 
3) jsonb_set(recipe, '{kcal}', '130') — правка возвращает новый документ:
   kcal до = 190, после = 130
 
4) jsonb_agg(jsonb_build_object(...)) — меню таблицы drinks одним документом:
   [{"sku": "ESP-01", "price_cents": 300}, {"sku": "CAP-01", "price_cents": 450}, {"sku": "LAT-01", "price_cents": 480}, {"sku": "CLD-01", "price_cents": 520}, {"sku": "TEA-01", "price_cents": 250}]

The path filter ? (@.grams > 100) kept only milk (220 g) of the latte's ingredients, @? found milk in the latte alone, and @@ flagged the same one as high-calorie. jsonb_set returned kcal=130 without touching the stored 190. And jsonb_agg assembled all five menu drinks into one document — note that the keys come out in normalized order (sku before price_cents): jsonb stores keys sorted.

The fence

jsonpath is powerful — and so it tempts you to keep in jsonb what has long been asking for columns:

you regularly filter by $.kcal > 100 or ? (@.name == "milk") — the data is structured and belongs in normal columns with a B-tree (07-02);
@?/@@/@> on a large table without GIN is a Seq Scan (06-05); jsonpath predicates need an index;
heavy building (jsonb_agg of a whole catalog into one document) is better cached than rebuilt on every request — in production that's load on the database CPU;
JSON_TABLE is PG17, not PG18: if you read someone else's code using it, don't attribute it to the "18 novelties."

Takeaways

jsonpath: $ root, .field, [*]/[0], the filter ? (@.x > N); applied by jsonb_path_query_array/_first.
The predicates @? ("a path match exists") and @@ ("the condition is true") return boolean and lean on GIN.
Building: jsonb_set (precise patch → new document), jsonb_build_object (object from pairs), jsonb_agg(... ORDER BY ...) (rows → array).
JSON_TABLE is PG17, not PG18; jsonb_path_* and the building functions exist since PG12.

Next up — 07-04 "Arrays vs a junction table": we return from documents to lists and compare two ways to store "many values" — a native text[] (with @>/= ANY and GIN) and the classic normalization into a junction table — so the choice is a deliberate one.

This lesson is still ahead