Пошук уроків, статей та іншого контенту
Розглянете зберігання, пошук, оновлення та індексацію структурованих даних у JSON і JSONB.
PostgreSQL має два типи для зберігання JSON-документів:
json — зберігає JSON як текст;
jsonb — зберігає JSON у бінарному, оптимізованому для обробки форматі.
Обидва типи перевіряють, чи є значення коректним JSON:
SELECT '{"name": "Anna"}'::json;
SELECT '{"name": "Anna"}'::jsonb;Тип JSON може містити об’єкти, масиви, рядки, числа, логічні значення та null.
jsonjson зберігає початкове текстове представлення документа. Це означає, що PostgreSQL зберігає форматування, пробіли та порядок ключів.
SELECT '{"b": 2, "a": 1}'::json;Тип json може бути корисним, якщо важливо зберегти документ саме в початковому вигляді.
Недоліки:
пошук усередині документа потребує повторного розбору тексту;
індексація значень усередині json менш зручна;
тип не підтримує основні оператори індексації jsonb.
jsonbjsonb розбирає документ під час запису та зберігає його у внутрішньому бінарному форматі.
Переваги:
швидший пошук і обробка;
підтримка індексів;
підтримка операторів порівняння та включення;
зручне оновлення окремих частин документа.
Під час перетворення в jsonb:
пробіли та форматування не зберігаються;
порядок ключів не має значення;
дублікати ключів не зберігаються як окремі значення.
SELECT '{"a": 1, "a": 2}'::jsonb;У результаті для ключа a залишиться останнє значення.
У більшості прикладних сценаріїв для роботи зі структурованими даними варто використовувати jsonb.
Розглянемо таблицю товарів, у якій частина полів має фіксовану структуру, а характеристики можуть відрізнятися для різних товарів.
CREATE TABLE products (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
data jsonb NOT NULL DEFAULT '{}'::jsonb
);Поле data може містити, наприклад, артикул, ціну, категорію, характеристики та список тегів.
INSERT INTO products (name, data)
VALUES
(
'Mechanical Keyboard',
'{
"sku": "KB-100",
"price": 89.99,
"category": "keyboards",
"attributes": {
"layout": "US",
"color": "black",
"wireless": true
},
"tags": ["mechanical", "usb", "gaming"]
}'::jsonb
),
(
'Wireless Mouse',
'{
"sku": "MS-200",
"price": 39.50,
"category": "mice",
"attributes": {
"color": "white",
"wireless": true
},
"tags": ["wireless", "office"]
}'::jsonb
);-> та ->>Оператор -> повертає JSON-значення.
Оператор ->> повертає значення як текст.
SELECT
data -> 'price' AS price_as_json,
data ->> 'price' AS price_as_text,
data -> 'attributes' AS attributes
FROM products;Результат буде мати різні типи:
data -> 'price' повертає JSONB-число;
data ->> 'price' повертає text;
data -> 'attributes' повертає вкладений JSONB-об’єкт.
Якщо значення потрібно використовувати в арифметичній операції, його можна привести до потрібного типу:
SELECT
(data ->> 'price')::numeric AS price
FROM products;Оператори можна комбінувати:
SELECT
data -> 'attributes' ->> 'color' AS color,
data -> 'attributes' ->> 'layout' AS layout
FROM products;Для доступу за шляхом використовують оператори #> та #>>.
#> повертає JSONB;
#>> повертає текст.
SELECT
data #> '{attributes,color}' AS color_as_json,
data #>> '{attributes,color}' AS color_as_text
FROM products;Елементи JSON-масиву мають індекси, що починаються з нуля.
SELECT
data -> 'tags' -> 0 AS first_tag,
data -> 'tags' ->> 0 AS first_tag_text
FROM products;Для отримання всіх елементів масиву можна використати jsonb_array_elements_text:
SELECT
products.name,
tag
FROM products
CROSS JOIN LATERAL jsonb_array_elements_text(products.data -> 'tags') AS tag;Для пошуку за конкретним значенням можна використовувати оператор ->>:
SELECT *
FROM products
WHERE data ->> 'category' = 'keyboards';Для числових значень важливо явно вказувати тип:
SELECT *
FROM products
WHERE (data ->> 'price')::numeric > 50;Оператор ? перевіряє наявність ключа у JSONB-об’єкті:
SELECT *
FROM products
WHERE data ? 'sku';Цей оператор також перевіряє наявність рядка у JSONB-масиві:
SELECT *
FROM products
WHERE data -> 'tags' ? 'wireless';Для перевірки кількох ключів використовують:
?& — присутні всі ключі;
?| — присутній хоча б один ключ.
SELECT *
FROM products
WHERE data ?& ARRAY['sku', 'price'];
SELECT *
FROM products
WHERE data ?| ARRAY['description', 'manufacturer'];@>Оператор @> перевіряє, чи містить лівий JSONB заданий фрагмент.
SELECT *
FROM products
WHERE data @> '{"category": "keyboards"}'::jsonb;Пошук за вкладеним об’єктом:
SELECT *
FROM products
WHERE data @> '{"attributes": {"wireless": true}}'::jsonb;Пошук за значенням у масиві:
SELECT *
FROM products
WHERE data @> '{"tags": ["wireless"]}'::jsonb;Такий запит знаходить документи, у яких масив tags містить значення "wireless".
Зворотний оператор <@ перевіряє, чи є лівий документ частиною правого:
SELECT
'{"category": "keyboards"}'::jsonb
<@
'{"category": "keyboards", "price": 89.99}'::jsonb
AS is_contained;Оператор || об’єднує два JSONB-об’єкти:
UPDATE products
SET data = data || '{"currency": "USD"}'::jsonb
WHERE data ->> 'sku' = 'KB-100';Якщо ключ уже існує, його значення буде замінено.
UPDATE products
SET data = data || '{"price": 99.99}'::jsonb
WHERE data ->> 'sku' = 'KB-100';Оператор || виконує поверхневе об’єднання. Він не об’єднує вкладені об’єкти рекурсивно.
Для зміни значення за шляхом використовують jsonb_set:
UPDATE products
SET data = jsonb_set(
data,
'{attributes,color}',
'"gray"'::jsonb
)
WHERE data ->> 'sku' = 'KB-100';Аргументи jsonb_set:
початковий документ;
шлях до ключа у вигляді масиву;
нове JSONB-значення.
Числове значення потрібно передати як JSONB:
UPDATE products
SET data = jsonb_set(
data,
'{price}',
'109.99'::jsonb
)
WHERE data ->> 'sku' = 'KB-100';Логічне значення:
UPDATE products
SET data = jsonb_set(
data,
'{attributes,wireless}',
'false'::jsonb
)
WHERE data ->> 'sku' = 'KB-100';За замовчуванням jsonb_set не створює відсутні проміжні ключі. Четвертий параметр true дозволяє створювати відсутні ключі на кінцевому рівні:
UPDATE products
SET data = jsonb_set(
data,
'{attributes,switch_type}',
'"linear"'::jsonb,
true
)
WHERE data ->> 'sku' = 'KB-100';Оператор - видаляє ключ з об’єкта:
UPDATE products
SET data = data - 'currency'
WHERE data ->> 'sku' = 'KB-100';Для видалення вкладеного значення за шляхом використовують #-:
UPDATE products
SET data = data #- '{attributes,switch_type}'
WHERE data ->> 'sku' = 'KB-100';Оператор - також може видалити елемент масиву за його індексом:
UPDATE products
SET data = data - 0
WHERE data ->> 'sku' = 'KB-100';У цьому прикладі оператор застосовується до самого JSONB-масиву. Щоб оновити масив усередині об’єкта, зазвичай зручніше отримати масив, змінити його та записати назад за допомогою jsonb_set.
Для об’єднання масивів можна використати оператор ||:
UPDATE products
SET data = jsonb_set(
data,
'{tags}',
(data -> 'tags') || '["new"]'::jsonb
)
WHERE data ->> 'sku' = 'KB-100';Оператор додає елемент до кінця масиву. Якщо потрібно додати елемент на початок:
UPDATE products
SET data = jsonb_set(
data,
'{tags}',
'["featured"]'::jsonb || (data -> 'tags')
)
WHERE data ->> 'sku' = 'KB-100';PostgreSQL має функції для створення JSON-значень із SQL-даних.
jsonb_build_objectФункція створює JSONB-об’єкт із пар ключ-значення:
SELECT jsonb_build_object(
'sku', 'KB-300',
'price', 129.99,
'available', true
);jsonb_build_arrayФункція створює JSONB-масив:
SELECT jsonb_build_array(
'mechanical',
'wireless',
'gaming'
);to_jsonbФункція перетворює SQL-значення або рядок у JSONB:
SELECT to_jsonb(42);
SELECT to_jsonb('hello');Під час вставки даних з окремих SQL-полів ці функції допомагають уникати ручного складання JSON-рядків.
Без індексу PostgreSQL може перевіряти JSON-документи послідовно, переглядаючи багато рядків. Для прискорення пошуку використовують індекси.
Найпоширеніший варіант — GIN-індекс на колонці jsonb:
CREATE INDEX products_data_gin_idx
ON products
USING GIN (data);Такий індекс особливо корисний для запитів із операторами:
@>;
<@;
?;
?|;
?&.
Наприклад:
SELECT *
FROM products
WHERE data @> '{"attributes": {"wireless": true}}'::jsonb;jsonb_path_opsДля запитів, що переважно використовують @>, можна створити індекс із класом операторів jsonb_path_ops:
CREATE INDEX products_data_path_gin_idx
ON products
USING GIN (data jsonb_path_ops);Такий індекс часто має менший розмір і добре підходить для перевірки включення через @>. Він не є універсальною заміною стандартному GIN-індексу для всіх операторів JSONB.
Якщо часто виконується пошук за одним простим ключем, можна створити індекс на виразі:
CREATE INDEX products_sku_idx
ON products ((data ->> 'sku'));Тепер запит за артикулом може використовувати цей індекс:
SELECT *
FROM products
WHERE data ->> 'sku' = 'KB-100';Вираз у запиті має відповідати виразу в індексі. Наприклад, індекс на (data ->> 'sku') не є тим самим, що індекс на (data -> 'sku').
Для числового значення можна створити індекс із приведенням типу:
CREATE INDEX products_price_idx
ON products (((data ->> 'price')::numeric));Відповідний запит:
SELECT *
FROM products
WHERE (data ->> 'price')::numeric > 50;json чи jsonb: вибір типуОбирайте json, якщо:
потрібно зберегти початковий текст документа;
порядок ключів або форматування мають значення;
документ майже не потрібно аналізувати всередині бази.
Обирайте jsonb, якщо:
потрібно шукати за ключами та значеннями;
потрібно оновлювати частини документа;
потрібна індексація;
документ використовується як робоча структура даних у запитах.
Для більшості прикладних даних у PostgreSQL рекомендованим вибором є jsonb.
Наведений приклад можна виконати в PostgreSQL послідовно:
-- Створюємо тимчасову таблицю, яка буде видалена після завершення сесії
CREATE TEMP TABLE products (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
data jsonb NOT NULL DEFAULT '{}'::jsonb
);
-- Додаємо товари зі структурованими характеристиками
INSERT INTO products (name, data)
VALUES
(
'Mechanical Keyboard',
'{
"sku": "KB-100",
"price": 89.99,
"category": "keyboards",
"attributes": {
"color": "black",
"wireless": true
},
"tags": ["mechanical", "gaming"]
}'::jsonb
),
(
'Wireless Mouse',
'{
"sku": "MS-200",
"price": 39.50,
"category": "mice",
"attributes": {
"color": "white",
"wireless": true
},
"tags": ["wireless", "office"]
}'::jsonb
);
-- Читаємо вкладене значення як текст
SELECT
name,
data ->> 'sku' AS sku,
data -> 'attributes' ->> 'color' AS color
FROM products;
-- Знаходимо товари з бездротовою характеристикою
SELECT name
FROM products
WHERE data @> '{"attributes": {"wireless": true}}'::jsonb;
-- Знаходимо товари з певним тегом
SELECT name
FROM products
WHERE data -> 'tags' ? 'gaming';
-- Оновлюємо вкладений ключ
UPDATE products
SET data = jsonb_set(
data,
'{attributes,color}',
'"gray"'::jsonb
)
WHERE data ->> 'sku' = 'KB-100';
-- Додаємо новий тег
UPDATE products
SET data = jsonb_set(
data,
'{tags}',
(data -> 'tags') || '["usb"]'::jsonb
)
WHERE data ->> 'sku' = 'KB-100';
-- Створюємо індекси для пошуку всередині JSONB
CREATE INDEX products_data_idx
ON products
USING GIN (data);
CREATE INDEX products_sku_idx
ON products ((data ->> 'sku'));
-- Перевіряємо фінальний документ
SELECT name, data
FROM products
ORDER BY id;-> там, де потрібен текст-> повертає JSONB, а ->> — текст.
Неправильний або непотрібно складний варіант:
WHERE data -> 'category' = 'keyboards'Правильний варіант для порівняння з текстом:
WHERE data ->> 'category' = 'keyboards'Або можна порівнювати саме JSONB зі значенням, явно перетвореним у JSONB:
WHERE data -> 'category' = '"keyboards"'::jsonbПорівняння текстових значень може дати неправильний порядок:
WHERE data ->> 'price' > '100'Для числового порівняння потрібно привести значення до числового типу:
WHERE (data ->> 'price')::numeric > 100Під час оновлення значення потрібно передавати коректний JSONB:
-- Рядок JSON
jsonb_set(data, '{price}', '100'::jsonb)
-- Число JSON
jsonb_set(data, '{price}', '100'::jsonb)
-- Рядок зі значенням "100"
jsonb_set(data, '{price}', '"100"'::jsonb)Різниця між '100'::jsonb та '"100"'::jsonb важлива: перше є числом, друге — рядком.
||Оператор || об’єднує об’єкти лише на верхньому рівні. Вкладений об’єкт може бути повністю замінений:
SELECT
'{"attributes": {"color": "black", "wireless": true}}'::jsonb
||
'{"attributes": {"color": "white"}}'::jsonb;У результаті в attributes залишиться лише color, а wireless буде втрачено. Для точкової зміни вкладеного значення використовуйте jsonb_set.
Якщо запити часто використовують @> або перевірку ключів, розгляньте GIN-індекс. Для пошуку за одним ключем може бути ефективнішим індекс на виразі, наприклад (data ->> 'sku').
json зберігає JSON як текст, а jsonb — у внутрішньому бінарному форматі.
jsonb зазвичай краще підходить для пошуку, оновлення та індексації.
-> повертає JSONB, а ->> — текст.
#> і #>> використовують для доступу до значень за вкладеним шляхом.
? перевіряє наявність ключа або елемента масиву.
@> перевіряє, чи містить документ заданий фрагмент.
jsonb_set оновлює значення за шляхом.
||, - і #- допомагають об’єднувати та видаляти частини JSONB.
GIN-індекси призначені для ефективного пошуку всередині JSONB.
Для часто використовуваного окремого ключа можна створити індекс на виразі.