PostgreSQL jsonb_path_query: выбрать по JSONPath

jsonb_path_query() выполняет JSONPath-выражение по значению jsonb и возвращает множество найденных элементов. В отличие от операторов -> и #> с фиксированным путём, JSONPath поддерживает массивы, рекурсивный поиск и фильтры по значениям. Функция появилась в PostgreSQL 12 вместе с типом jsonpath.

Отфильтровать элементы массива по цене

CREATE TABLE orders (
    id integer PRIMARY KEY,
    data jsonb NOT NULL
);

INSERT INTO orders VALUES
    (1, '{"customer": "Анна", "items": [{"sku": "A", "price": 120}, {"sku": "B", "price": 80}]}'),
    (2, '{"customer": "Игорь", "items": [{"sku": "C", "price": 300}]}');

SELECT o.id, item->>'sku' AS sku, (item->>'price')::int AS price
FROM orders AS o,
     jsonb_path_query(o.data, '$.items[*] ? (@.price > 100)') AS item
ORDER BY o.id, sku;

$ обозначает корень документа, .items[*] перебирает все элементы массива, а ? (@.price > 100) оставляет только подходящие объекты. Функция возвращает setof jsonb, поэтому её удобно указывать в FROM: каждая найденная строка превращается в отдельную запись результата.

JSONPath-строка — это значение, а не SQL-код, поэтому параметры можно передавать отдельно: jsonb_path_query(data, '$[*] ? (@.id == $id)', vars => jsonb_build_object('id', 42)). Такой вариант безопаснее конкатенации и позволяет переиспользовать запрос. Для проверки наличия подходит jsonb_path_exists(), для первого совпадения — jsonb_path_query_first().

Проверить наличие дорогой позиции

SELECT id,
       jsonb_path_exists(data, '$.items[*] ? (@.price > 200)') AS has_expensive
FROM orders
ORDER BY id;

jsonb_path_exists() не материализует все совпадения и завершается на первом, поэтому для условия «есть ли такое» он экономнее полной выборки. Для сложных отчётов всё равно измеряйте план: JSONPath-фильтр по большой таблице читает и разбирает каждый документ.

Обычный GIN-индекс jsonb_path_ops ускоряет оператор @>, но не произвольный JSONPath с фильтрами. Если запрос по одному полю выполняется часто, вынесите его в generated column и постройте B-tree индекс. Для разовых исследований JSONPath удобнее, чем набор вложенных jsonb_array_elements().

Не передавайте в путь неограниченный пользовательский ввод: сложное рекурсивное выражение может выполняться долго. Ограничивайте длину и набор допустимых путей на стороне приложения, а тяжёлые запросы запускайте с statement_timeout.

Документация: JSON functions and operators, jsonpath.