SQL/JSON path и сборка

В Brew появились рецепты напитков: у каждого — массив ингредиентов с граммовками, вложенный в jsonb. Бариста-тренер просит ответить на вопросы вроде «какие ингредиенты у латте?», «что весит больше 100 граммов?», «есть ли в напитке молоко?». Цепочкой ->/#> из 07-01 это пишется неуклюже: достать массив, развернуть, отфильтровать, собрать обратно. Для вложенных документов в Postgres есть язык получше — jsonpath.

Цель юнита — научиться доставать данные из вложенного jsonb выражениями пути (jsonb_path_query_*, предикаты @?/@@) и собирать jsonb обратно (jsonb_set, jsonb_build_object, jsonb_agg). Заодно отметим версионную тонкость: JSON_TABLE (развернуть jsonb в реляционную таблицу прямо в FROM) — это PG17, не PG18; здесь он не нужен, а функции jsonb_path_* и сборочные есть ещё с PG12.

jsonpath: путь как мини-язык

jsonpath — стандартный язык адресации внутри JSON. $ — корень документа, .name — поле, [*] — все элементы массива, [0] — конкретный, а ? (@.grams > 100) — фильтр: оставить только элементы, где условие истинно (@ — текущий элемент). Так $.ingredients[*] ? (@.grams > 100).name читается как «имена ингредиентов, у которых граммов больше 100». Применяют путь функции jsonb_path_query_array (собрать все совпадения в один jsonb-массив) и jsonb_path_query_first (взять первое). Один компактный путь заменяет ручную распаковку массива через ->.

Предикаты пути: @? и @@

Иногда не нужны значения — нужен ответ «да/нет». Для этого есть два оператора. @? — «существует ли хоть одно совпадение пути»: recipe @? '$.ingredients[*] ? (@.name == "milk")' истинно, если среди ингредиентов есть молоко. @@ — «истинно ли условие как предикат»: recipe @@ '$.kcal > 100' истинно для калорийных напитков. Разница тонкая (@? про существование узла, @@ про булеву проверку), но оба возвращают boolean и оба умеют опираться на GIN-индекс (jsonb_path_ops) на больших таблицах.

Сборка: jsonb_set, build_object, jsonb_agg

Обратная задача — собрать jsonb. jsonb_set(doc, '{path}', value) точечно правит поле и возвращает новый документ (хранимая строка не меняется — это чистая функция; та самая «правка = пересборка значения», из-за которой в 07-02 возникает write-amplification). jsonb_build_object('a', x, 'b', y) лепит объект из пар «ключ-значение», а агрегат jsonb_agg(... ORDER BY ...) сворачивает строки результата в один jsonb-массив. Вместе они собирают API-ответ прямо в SQL: например, всё меню из таблицы drinks — одним документом.

Путь шаг за шагом

Выражение $.ingredients[*] ? (@.grams > 100).name — это маршрут по документу. Рецепт латте и проход по нему:

plaintext

  recipe = { "kcal": 190,
             "ingredients": [ {"name":"espresso", "grams":30},
                              {"name":"milk",     "grams":220} ] }
 
  $                   корень документа
   .ingredients       → поле ingredients (массив из двух объектов)
              [*]      → каждый элемент массива
                 ? (@.grams > 100)   → фильтр: оставить, где grams > 100
                                       espresso 30 ✗  ·  milk 220 ✓
                          .name       → взять name у прошедших фильтр
  ───────────────────────────────────────────────────────────────
  результат: ["milk"]

Каждый шаг сужает набор: поле → элементы → фильтр → поле. Ту же выборку цепочкой ->/#> пришлось бы собирать руками — достать массив, развернуть, отфильтровать, склеить обратно.

Что показывает наш код

Лабораторный стол drink_recipe_lab (рецепты с вложенным массивом ингредиентов) для путей и сборка по таблице drinks:

sql

SELECT jsonb_path_query_array(recipe, '$.ingredients[*].name'),                    -- все имена
       jsonb_path_query_array(recipe, '$.ingredients[*] ? (@.grams > 100).name'),  -- тяжёлые
       jsonb_path_query_first(recipe, '$.ingredients[0].name')                     -- первый
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 не знает сигнатур jsonb_path_*/@?/@@ из каталога и типизирует их результат как interface{}; pgx возвращает в них конкретные string/bool, поэтому в демо печатаем через %v. Юнит добавляет свой стол в схему → make db-reset накатывает его через brew.Apply.

Запуск

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

Вывод:

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}]

Фильтр пути ? (@.grams > 100) оставил из ингредиентов латте только milk (220 г), @? нашёл молоко лишь у латте, @@ отметил его же как калорийный. jsonb_set отдал kcal=130, не тронув хранимый 190. А jsonb_agg собрал все пять напитков меню в один документ — заметь, ключи в выводе идут в нормализованном порядке (sku перед price_cents): jsonb хранит ключи отсортированными.

Заборчик

jsonpath мощный — и тем соблазняет таскать в jsonb то, что давно просится в колонки:

регулярно фильтруешь по $.kcal > 100 или ? (@.name == "milk") — данные структурированы, им место в обычных столбцах с B-tree (07-02);
@?/@@/@> на большой таблице без GIN — это Seq Scan (06-05); под jsonpath-предикаты нужен индекс;
тяжёлую сборку (jsonb_agg всего каталога в один документ) лучше закэшировать, а не пересобирать на каждый запрос — в проде это нагрузка на CPU базы;
JSON_TABLE — это PG17, не PG18: читаешь чужой код с ним — не приписывай «новинкам 18».

Что забрать с собой

jsonpath: $ корень, .field, [*]/[0], фильтр ? (@.x > N); применяют jsonb_path_query_array/_first.
Предикаты @? («есть совпадение пути») и @@ («условие истинно») возвращают boolean и опираются на GIN.
Сборка: jsonb_set (точечная правка → новый документ), jsonb_build_object (объект из пар), jsonb_agg(... ORDER BY ...) (строки → массив).
JSON_TABLE — это PG17, не PG18; jsonb_path_* и сборочные функции есть с PG12.

Дальше — 07-04 «Массивы против таблицы-связки»: вернёмся от документов к спискам и сравним два способа хранить «много значений» — нативный text[] (с @>/= ANY и GIN) и классическую нормализацию в junction-таблицу, — чтобы выбирать осознанно.

Этот урок ещё впереди