Функции для работы с JSONB в PostgreSQL: полный справочник

JSONB — один из ключевых типов данных PostgreSQL. В отличие от JSON (текст), JSONB хранится в бинарном формате, поддерживает индексы и мощные операторы. Разберём весь арсенал.

JSONB vs JSON

Создание и вставка

CREATE TABLE products (
    id   SERIAL PRIMARY KEY,
    data JSONB NOT NULL
);

INSERT INTO products (data) VALUES
('{
    "name": "Ноутбук",
    "price": 85000,
    "specs": {
        "ram": 16,
        "storage": 512,
        "display": "15.6"
    },
    "tags": ["electronics", "laptop"],
    "in_stock": true
}');

Операторы извлечения

-- -> : получить значение (возвращает JSONB)
SELECT data -> 'name' FROM products;          -- "Ноутбук" (с кавычками!)
SELECT data -> 'specs' -> 'ram' FROM products; -- 16

-- ->> : получить как TEXT (без кавычек)
SELECT data ->> 'name' FROM products;          -- Ноутбук

-- #> : путь как массив
SELECT data #> '{specs, ram}' FROM products;   -- 16 (JSONB)
SELECT data #>> '{specs, ram}' FROM products;  -- 16 (TEXT)

-- Извлечение элемента массива по индексу
SELECT data -> 'tags' -> 0 FROM products;     -- "electronics"
SELECT data ->> 'tags' ->> 1 FROM products;  -- laptop

Операторы проверки

-- @> : содержит (contains)
SELECT * FROM products WHERE data @> '{"in_stock": true}';
SELECT * FROM products WHERE data @> '{"specs": {"ram": 16}}';  -- вложенный поиск

-- <@ : содержится в
SELECT * FROM products WHERE '{"name": "Ноутбук"}' <@ data;

-- ? : ключ существует
SELECT * FROM products WHERE data ? 'price';
SELECT * FROM products WHERE data ? 'discontinued';  -- нет такого ключа

-- ?| : хотя бы один ключ существует
SELECT * FROM products WHERE data ?| ARRAY['price', 'sale_price'];

-- ?& : все ключи существуют
SELECT * FROM products WHERE data ?& ARRAY['name', 'price', 'specs'];

Изменение JSONB

-- jsonb_set: установить значение по пути
UPDATE products
SET data = jsonb_set(data, '{price}', '90000')
WHERE id = 1;

-- Вложенный путь
UPDATE products
SET data = jsonb_set(data, '{specs, ram}', '32')
WHERE id = 1;

-- Создать ключ если не существует (4-й аргумент: true = создать)
UPDATE products
SET data = jsonb_set(data, '{discount}', '0.1', true)
WHERE id = 1;

-- || : объединить объекты (правый перезаписывает совпадающие ключи)
UPDATE products
SET data = data || '{"sale": true, "sale_price": 75000}'::jsonb
WHERE id = 1;

-- - : удалить ключ
UPDATE products
SET data = data - 'discontinued'
WHERE id = 1;

-- #- : удалить вложенный ключ
UPDATE products
SET data = data #- '{specs, display}'
WHERE id = 1;

Функции разворачивания

-- jsonb_each: пары ключ-значение как строки
SELECT key, value FROM jsonb_each('{"a": 1, "b": 2, "c": 3}'::jsonb);
-- a | 1
-- b | 2
-- c | 3

-- jsonb_each_text: значения как TEXT
SELECT key, value FROM jsonb_each_text(data) WHERE key = 'name'
FROM products;

-- jsonb_object_keys: только ключи
SELECT jsonb_object_keys(data) FROM products;

-- jsonb_array_elements: развернуть массив
SELECT elem FROM jsonb_array_elements(data -> 'tags')
FROM products;
-- "electronics"
-- "laptop"

-- jsonb_array_elements_text: массив как TEXT
SELECT elem FROM jsonb_array_elements_text(data -> 'tags')
FROM products;
-- electronics
-- laptop

-- jsonb_array_length: длина массива
SELECT jsonb_array_length(data -> 'tags') FROM products;

jsonb_path_query: JSON Path (PostgreSQL 12+)

-- JSON Path — XPath для JSON
-- Найти все RAM > 8 в любых вложенных объектах
SELECT jsonb_path_query(data, '$.specs.ram ? (@ > 8)')
FROM products;

-- Все элементы массива tags
SELECT jsonb_path_query_array(data, '$.tags[*]')
FROM products;

-- Фильтр по условию
SELECT *
FROM products
WHERE jsonb_path_exists(data, '$.specs.ram ? (@ >= 16)');

-- Операторы пути:
-- $         — корень
-- .key      — поле объекта
-- [*]       — все элементы массива
-- [0]       — первый элемент
-- ? (cond)  — фильтр
-- @         — текущий элемент в фильтре

Агрегация в JSONB

-- Собрать строки в JSON-массив
SELECT jsonb_agg(jsonb_build_object('id', id, 'name', name))
FROM products;
-- [{"id": 1, "name": "Ноутбук"}, ...]

-- Собрать в JSON-объект
SELECT jsonb_object_agg(id::text, name)
FROM products;
-- {"1": "Ноутбук", "2": "Мышь"}

-- Построить JSON-объект
SELECT jsonb_build_object(
    'total', COUNT(*),
    'avg_price', AVG((data->>'price')::numeric),
    'categories', jsonb_agg(DISTINCT data->>'category')
)
FROM products;

Индексы для JSONB

-- GIN: ускоряет @>, ?, ?|, ?&
CREATE INDEX idx_products_data ON products USING gin(data);

-- GIN jsonb_path_ops: только @> (быстрее и меньше)
CREATE INDEX idx_products_data_path ON products USING gin(data jsonb_path_ops);

-- Функциональный: для конкретного поля
CREATE INDEX idx_products_price ON products ((data->>'price')::numeric);

-- После индекса:
SELECT * FROM products WHERE data @> '{"in_stock": true}';  -- Index Scan
SELECT * FROM products WHERE (data->>'price')::numeric > 10000;  -- Index Scan

Конвертация JSONB ↔ другие форматы

-- Строка в JSONB
SELECT '{"key": "value"}'::jsonb;
SELECT to_jsonb('text'::text);

-- Запись в JSONB
SELECT to_jsonb(users) FROM users WHERE id = 42;  -- всю строку как JSONB

-- JSONB в TEXT
SELECT data::text FROM products;

-- JSONB в строку записи
SELECT * FROM jsonb_to_record('{"name": "Bob", "age": 30}')
AS x(name TEXT, age INTEGER);

-- Массив JSONB-объектов в набор строк
SELECT * FROM jsonb_to_recordset('[{"name": "Alice", "score": 95}, {"name": "Bob", "score": 87}]')
AS x(name TEXT, score INTEGER);

Итог: шпаргалка операторов