Чуть больше подробностей про внутренности PostgreSQL можно найти в других статьях.
Мне показалось это полезным:
Так исторически сложилось, что в PostgreSQL есть несколько типов данных для JSON.
hstore - Это не JSON. Позволяет хранить набор пар key:value внутри одного поля базы данных. Поддерживает индексирование. Нет массивов и вложенности.
json - Хранит JSON в виде текста. Не оптимально для часто меняющихся данных или если выполняется много запросов к полям JSON. Не поддерживает индексирование.
jsonb - (binary json) Наследник hstore - "вложенный" hstore. Бинарное хранение. Поддерживает индексирование. В отличии от hstore поддерживает вложенные объекты и массивы. Не сохраняет форматирование (удаляет пробелы) и поддерживает только уникальные ключи в JSON (сохранен будет последний). Ключи отсортированы.
number в jsonb реализован как numeric. Это позволяет обойти ограничения 8 байт.
jsonpath - введен для поддержки jsonpath. Это двоичное представление разобранного jsonpath выражения.
json и jsonb реализации хранят данные в поле таблицы опираясь на стандартные механизм TOAST в случае большого значения ( больше 2kb ).
TOAST перезаписывает и читает данные целиком
TOAST был сделан для атомарных типов данных. Нет информации про внутреннюю структуру
Как результат:
Любое обновление JSON приведет в полной перезаписи поля целиком.
Повышенная нагрузка на VACUUM и WAL при частых изменениях в JSON.
Линейная зависимость времени доступа к любому ключу от размера json. Из-за того что нужно читать и разжимать все TOAST блоки.
Алгоритм применения обновлений если JSON не попал в TOAST:
Алгоритм применения обновлений если JSON находится в TOAST:
Маленькое обновление в больших JSON приводит к потере производительности, распуханию как таблиц так и WAL, а также к большой нагрузке на VACUUM.
SQL:16 - вводит описание для:
но пока не вводится конкретный тип данных
SQL:19 - вводит тип дынных json. Для PostgreSQL это проблема так как json уже был реализован в нём и этот тип данных очень медленный (json хранится в веде строк).
На данный момент в PostgreSQL реализован JSON Path language (в 13й версии).
В этом разделе описаны предложения компании PostgresPro для обеспечения совместимости с SQL стандартом и ряд оптимизаций, делающих использование JSON более привлекательным.
Предложения не приняты и находятся в разработке.
Pluggable toaster был отклонен.
Для совместимости с типом данных json из SQL стандарта и с предыдущими реализациями json и jsonb предлагается внутренний механизм GSON.
GSON (Generic JSON Api) - это слой поверх json и jsonb. Он наследует функции из json & jsonb. Также становится доступным индексирование для json (json text).
Реализация конкретного способа хранения json или jsonb является backend-ом для GSON, а json and jsonb функции - это frontend доступный в SQL. В результате введения слоя GSON станет возможным реализовать SQL/JSON функции (функции из стандатра) как frontend для GSON backend-а.
Так как GSON скрывает детали реализации работы с json / jsonb, то можно реализовать дополнительные методы хранения, добавив их как backend для GSON.
Для решения проблем совместимости (или для создания новых) в PostgreSQL предлагается использовать mapping (aliasing) типов данных.
По умолчанию мы имеем sql_json = json
json - алиас на json (т.е. json text, который хранится в виде текста)jsonb - алиас на jsonbно у нас есть возможность изменить это
SET sql_json = jsonb;
и теперь мы имеем
json - алиас на jsonb. Это дает возможность заявить о поддержке SQL стандарта и "быстром" типе данных.json text - алиас на json. Для тех кому все еще нужен json, который хранится как текст.Частичная декомпрессия Jsonb - частичная декомпрессия - читается и распаковывается все от начала до запрашиваемого значения.
Данные хранятся в таком виде: (key1,key2,key3,value1,value2,valu3)
key1 - будет прочитано и распаковано только [key1,key2,key3,value1key2 - будет прочитано и распаковано только [key1,key2,key3,value1,value2Сортировка ключей по длине значения - сортировка не по тексту ключа а по длине значения (т.е. большее значение будет последним).
Частичный deTOASTing - читать только нужные блоки для TOAST, не поднимать все с файловой системы.
Inline TOAST - если json попадает в TOAST, то в тупле таблицы помимо ссылки на TOAST хранится и начало json-a (осатльное в TOAST). Вместе с сортировкой это позволяетне не обращаться в TOAST для "маленьких" значений.
Shared TOAST - не перезаписывать весь json при обновлении. копируются только chunk-и которые содержит обновленное значение. Снижается нагрузка на VACUUM и WAL.
Этот раздел достаточно подробно :) описан в документации (или так), поэтому обойдемся кратким перечисление того что мы можем сделать с JSON в PostgreSQL
У нас есть возможность создавать JSON из любых данных в SQL
SELECT '{"id": "uuid-1", "plantId": "uuid-2"}'::jsonb;
-- или из таблицы
CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; -- это включение поддержки uuid
DROP TABLE IF EXISTS plant_properties;
CREATE TABLE IF NOT EXISTS plant_properties (
id UUID NOT NULL PRIMARY KEY,
plant_id UUID,
plant_color character(13)
);
INSERT INTO plant_properties (id, plant_id, plant_color) VALUES
(uuid_generate_v4(), uuid_generate_v4(), 'green'),
(uuid_generate_v4(), uuid_generate_v4(), 'blue'),
(uuid_generate_v4(), uuid_generate_v4(), 'green')
;
SELECT json_agg(plant_properties)
FROM plant_properties
WHERE plant_color = 'blue'
;
-- или
SELECT jsonb_agg(plant_prop) AS property
FROM (
SELECT id, plant_id AS "plantId" FROM plant_properties WHERE plant_color = 'blue'
) AS plant_prop
;
in Postgres 15 JSON_TABLE lets you work with a JSON structure in Postgres as if it was a regular Postgres table.
Создадим таблицу для хранения свойств растений
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
DROP TABLE IF EXISTS plant_properties;
CREATE TABLE IF NOT EXISTS plant_properties (
id UUID NOT NULL PRIMARY KEY,
plant_id UUID NOT NULL,
payload jsonb NOT NULL -- тут будем хранить описание свойства в формате JSON
);
поля
idиplant_idвынесены из JSON для того, чтобы можно было быстро найти требуемую запись по ним не заставляя PostgreSQL обращаться к JSON так как это наложет свой отпечаток на производительность.кроме полей из JSON иногда полезно вынести какой-нибудь агрегат. Например, количество свойств или наличие того или иного свойства в JSON.
Клиенты хотят видеть вот такой JSON, чтобы отрисовать UI или что-нибудь проанализировать
{
"plantId": "...",
"properties": [
{"type": "color", "value": "green"},
{"type": "perennial", "value": true},
{"type": "max_height", "value": 3.3, "units": "metre"},
{"type": "flowering_period", "from": 5, "to": 6, "units": "month"}
]
}
Если мы будем складывать все свойства одного растения в один JSON так:
CREATE TABLE IF NOT EXISTS plant_properties (
plant_id UUID NOT NULL PRIMARY KEY,
payload jsonb NOT NULL
);
INSERT INTO plant_properties (plant_id, payload) VALUES
('a03a347f-8435-4caa-aeee-3eaaa38983ba', '{
"plantId": "...",
"properties": [
{"type": "color", "value": "green"},
{"type": "perennial", "value": true},
{"type": "max_height", "value": 1.1, "units": "metre"},
{"type": "flowering_period", "from": 5, "to": 6, "units": "month"}
]
}')
;
это увеличит вероятность попасть в TOAST и при обновлении одного свойства PostgreSQL будет перезаписывать все свойства растения.
Лучше разложить так: одно свойство - одна запись
CREATE TABLE IF NOT EXISTS plant_properties (
id UUID NOT NULL PRIMARY KEY,
plant_id UUID NOT NULL,
property jsonb NOT NULL
);
INSERT INTO plant_properties (id, plant_id, property) VALUES
(uuid_generate_v4(), 'a03a347f-8435-4caa-aeee-3eaaa38983ba', '{"type": "color", "value": "green"}'),
(uuid_generate_v4(), 'a03a347f-8435-4caa-aeee-3eaaa38983ba', '{"type": "perennial", "value": true}'),
(uuid_generate_v4(), 'a03a347f-8435-4caa-aeee-3eaaa38983ba', '{"type": "max_height", "value": 1.1, "units": "metre"}'),
(uuid_generate_v4(), 'a03a347f-8435-4caa-aeee-3eaaa38983ba', '{"type": "flowering_period", "from": 5, "to": 6, "units": "month"}'),
(uuid_generate_v4(), '8b315f91-de7f-4f98-9e90-40e7a6532f69', '{"type": "color", "value": "green"}'),
(uuid_generate_v4(), '8b315f91-de7f-4f98-9e90-40e7a6532f69', '{"type": "perennial", "value": false}'),
(uuid_generate_v4(), '8b315f91-de7f-4f98-9e90-40e7a6532f69', '{"type": "max_height", "value": 2.4, "units": "metre"}'),
(uuid_generate_v4(), '8b315f91-de7f-4f98-9e90-40e7a6532f69', '{"type": "flowering_period", "from": 5, "to": 6, "units": "month"}'),
(uuid_generate_v4(), '7036a94c-6b9a-46ac-b536-47bf780bcedb', '{"type": "color", "value": "blue"}'),
(uuid_generate_v4(), '7036a94c-6b9a-46ac-b536-47bf780bcedb', '{"type": "perennial", "value": true}'),
(uuid_generate_v4(), '7036a94c-6b9a-46ac-b536-47bf780bcedb', '{"type": "max_height", "value": 3.6, "units": "metre"}')
;
поле property удовлетворяет ограничениям и не попадет в TOAST. Такая схема не будет оказывать избыточного влияния на WAL и VACUUM при редактировании данных.
JsQuery – is a language to query jsonb data type, introduced in PostgreSQL release 9.4.
Jsquery is released as jsquery data type (similar to tsquery) and @@ match operator for jsonb.
Это расширение от Postgres Pro, предоставляющее дополнительную функциональность для jsonb. В стандартной поставке его нет, а для установку нужен бубен, поэтому подробностей про него не будет. Пару слов стоило сказать так как про него часто вспонимают.
Подробности в readme тут: https://github.com/postgrespro/jsquery.
В документации описано множество операторов и функций работы с JSON. Рассмотрим только некоторые из них.
достать список всех типов свойст растения с помощью оператора можно вот так:
SELECT
distinct(properties->'type') as types
FROM plant_properties
;
здесь ->'type' - это взять значение для свойства с ключем type
Для первого варианта схемы, когда все свойства в одной записи:
SELECT
distinct(payload->'properties'->0->'type') as types
FROM plant_properties
;
->0 это взять нулевой элемент из списка.
Второй вариант можно переписать с другим оператором
SELECT
distinct(payload #> '{properties,0,type}') as types
FROM plant_properties
;
здесь мы извлекаем значение по пути '{properties,0,type}'
оператор
->>всегда возвращает text, а оператор->всегда json или jsonb
Пример: получить все записи (свойства растений) с типом "color":
SELECT *
FROM plant_properties
WHERE properties #> '{type}' = '"color"'
;
Пример: получить идентификаторы всех растений max_height которых больше 2:
SELECT plant_id
FROM plant_properties
WHERE properties #> '{type}' = '"max_height"' AND (properties -> 'value')::integer > 2
;
Пример: вывести plantId и plantColor для всех многолетних растений:
SELECT
plant_id as plantId, properties ->> 'value' as plantColor
FROM plant_properties, (
SELECT plant_id as pid
FROM plant_properties
WHERE properties #> '{type}' = '"perennial"' AND properties #> '{value}' = 'true'
) as perennial_plant_ids
WHERE plant_id = pid AND properties #> '{type}' = '"color"'
;
-->
-- "a03a347f-8435-4caa-aeee-3eaaa38983ba" "green"
-- "7036a94c-6b9a-46ac-b536-47bf780bcedb" "blue"
Из документации
similar to XPath expressions used for SQL access to XML
Получить все уникальные типы из JSON. Для первого варианта схемы:
SELECT
DISTINCT(jsonb_path_query(payload, '$.properties[*].type'))
FROM plant_properties
;
Получить все уникальные типы из JSON. Для второго варианта схемы:
SELECT
DISTINCT(jsonb_path_query(properties, '$.type'))
FROM plant_properties
;
В "SQL/JSON Path Language" используются следующие конструкции
$. - корень документа
.type - взять значения свойства с ключом type
.properties[3] - взять объект из массива по индексу 3
.properties[*] - для любого объекта из массива
"SQL/JSON Path Language" может использоваться для фильтрации записей таблицы по полям JSON:
Например, получить все записи (свойства растений) с типом "color" можно вот так:
SELECT
id, plant_id, jsonb_path_query(properties, '$ ? (@.type == "color")')
FROM plant_properties
;
здесь
$ - селектор jsonpath. проверка выполняется для каждого узла по этому селектору. В данном случае мы будем корень.
Если бы в корне находился массив то каждый элемент этого массива можно было бы взять использовав $[*]
jsonpath ? (condition) - формат фильтра. может быть несколько фильтров jsonpath ? (condition) ? (condition)
@.type == "color" - условие фильтрации. Поле type для каждого элемента из списка предоставленного jsonpath проверяется type == "color"
'Regular Expressions', 'string starts with' и другие операторы и методы для "SQL/JSON Path Language" смотрите в документации
Еще несколько примеров с использованием "SQL/JSON Path Language" в запросах
Получить все plant_id многолетников. Выполняется поиск вхождений содержащих пару {"type": "perennial"}.
SELECT DISTINCT(plant_id)
FROM plant_properties
WHERE property @> '{"type": "perennial"}'
;
Получить все plant_id для которых определено совйство "max_height". Проверка значения поля type.
SELECT DISTINCT(plant_id)
FROM plant_properties
WHERE property @? '$.type ? (@ == "max_height")'
;
Объелинение двух JSON:
SELECT
'[{"type": "color", "value": "green"}]'::jsonb || '[{"type": "perennial", "value": false}]'::jsonb
;
--> [{"type": "color", "value": "green"},{"type": "perennial", "value": false}]
Удаление элементов из JSON
SELECT
'[{"type": "color", "value": "green"}, {"type": "perennial", "value": false}]'::jsonb #- '{1}'
;
--> [{"type": "color", "value": "green"}]
Замена значения
SELECT
jsonb_set('[{"type": "color", "value": "green"}, {"type": "perennial", "value": false}]'::jsonb, '{0,value}', '"black"')
;
--> [{"type": "color", "value": "black"}, {"type": "perennial", "value": false}]
Еще есть jsonb_insert, jsonb_extract_path, jsonb_pretty и другие. Полный список в документации.
Для индексирования доступен только тип jsonb.
Доступен стандартный B-tree индекс с указанием выражения для индексирования
CREATE INDEX property_type_idx ON plant_properties ((payload ->> 'type'));
Существует два варианта использования GIN индекса
1) jsonb_ops - оп-класс по умолчанию. Поддерживает больше операций но занимает больше места и медленнее работает.
supports queries with the key-exists operators ?, ?| and ?&, the containment operator @>, and the jsonpath match operators @? and @@
CREATE INDEX property_type_idx ON plant_properties USING GIN (payload);
2) jsonb_path_ops - поддерживает меньше операций, создает индекс меньшкго размера, работает быстрее
not support the key-exists operators, but it does support @>, @? and @@
CREATE INDEX property_type_idx ON plant_properties USING GIN (payload jsonb_path_ops);
Отдельно стоит сказать про INCLUDE фичу в индексах. Она позволит включить данные из JSON в индекс и выполнять критические запросы используя только индекс. Стоит осторожно использовать эту возможность, так как можно легко увеличить размер индекса на столько что он станет совсем бесполезен.
Мы можем использовать constraints для различных ограничений в JSON.
Например, в нашем примере есть обязательное поле в JSON, которое мы не вынесли в поле таблицы - это type.
Выносить это поле не нужно, так как мы не используем его при запросах. Но вот требовать чтобы это поле было является хорошей идеей, ведь это поле - это главный признак по которому данные будут направлены в правильный обработчик.
Пример того как мы можем сделать обязательным поле type внутри jsonb поля таблицы:
DROP TABLE IF EXISTS plant_properties;
CREATE TABLE IF NOT EXISTS plant_properties (
id UUID NOT NULL PRIMARY KEY,
plant_id UUID NOT NULL,
property jsonb NOT NULL CHECK (property -> 'type' IS NOT NULL)
);
INSERT INTO plant_properties (id, plant_id, property) VALUES
(uuid_generate_v4(), 'a03a347f-8435-4caa-aeee-3eaaa38983ba', '{"type": "color", "value": "green"}'),
(uuid_generate_v4(), 'a03a347f-8435-4caa-aeee-3eaaa38983ba', '{"type": "max_height", "value": 1.1, "units": "metre"}')
;
Если мы попытаемся добавить запись с JSON в котором не будет type то получим ошибку
INSERT INTO plant_properties (id, plant_id, property) VALUES
(uuid_generate_v4(), 'a03a347f-8435-4caa-aeee-3eaaa38983ba', '{"value": true}')
;
-- ERROR: ОШИБКА: новая строка в отношении "plant_properties" нарушает ограничение-проверку "plant_properties_property_check"
-- DETAIL: Ошибочная строка содержит (9f616311-0e42-445f-b200-b0f631c39edb, a03a347f-8435-4caa-aeee-3eaaa38983ba, {"value": true}).
еще можно ограничить тип значения в поле type
CREATE TABLE IF NOT EXISTS plant_properties (
id UUID NOT NULL PRIMARY KEY,
plant_id UUID NOT NULL,
property jsonb NOT NULL CHECK (property -> 'type' IS NOT NULL AND jsonb_typeof(property -> 'type')='string')
);
-- При попытке добавить `{"type": 1, "value": true}`:
-- ERROR: ОШИБКА: новая строка в отношении "plant_properties" нарушает ограничение-проверку "plant_properties_property_check"
-- DETAIL: Ошибочная строка содержит (1b807e51-9704-4506-a4aa-3bfa711e1fd8, a03a347f-8435-4caa-aeee-3eaaa38983ba, {"type": 1, "value": true}).
-- SQL state: 23514
или можно огриничить множество значений, которое может принимать type
CREATE TABLE IF NOT EXISTS plant_properties (
id UUID NOT NULL PRIMARY KEY,
plant_id UUID NOT NULL,
property jsonb NOT NULL CHECK ((property ->> 'type') IN ('color','perennial'))
);
И так далее, любые ограничения, чтобы обеспечить целостность данных.
С помошью Constraints мы можем организовать валидацию, но если хочется большего...
NOTE: Я не использовал описанные ниже расширения так как для установки они требуют магии make install && make installcheck. Эти расширения выглядят старыми и заброшенными. Если есть необходимость иметь валидацию с использованием JSON Schema эти расширения можно попробовать.
is_jsonb_valid is a native PostgreSQL extension to validate json schemas following Draft4. The extension exposes only one function is_jsonb_valid(schema jsonb, data jsonb) which returns a boolean depending on the success of the validation.
CREATE TABLE IF NOT EXISTS plant_properties (
id UUID NOT NULL PRIMARY KEY,
plant_id UUID NOT NULL,
property jsonb NOT NULL CHECK (is_jsonb_valid('{"type": "string"}', property)
);
postgres-json-schema allows validation of JSON schemas in PostgreSQL. It is implemented as a PL/pgSQL function and you can use it as a check constraint to validate the format of your JSON columns. postgres-json-schema supports the entire JSON schema draft v4 spec.
CREATE TABLE IF NOT EXISTS plant_properties (
id UUID NOT NULL PRIMARY KEY,
plant_id UUID NOT NULL,
property jsonb NOT NULL CHECK (validate_json_schema('{"type": "string"}', property)
);
И другие подобные расширения для PostgreSQL.
На случай если имена растений - это свойство растения, которое помещено в JSON, который в PostgreSQL... И нам очень нужно сделать полнотекстовый поиск по именам растений.
Как работает полнотекстовый поиск кратко описано тут: Full-text search in PostgreSQL
Создадим таблицу и заполним ее данными
DROP TABLE IF EXISTS plant_properties;
CREATE TABLE IF NOT EXISTS plant_properties (
id UUID NOT NULL PRIMARY KEY,
plant_id UUID NOT NULL,
property jsonb NOT NULL CHECK (property -> 'type' IS NOT NULL AND (property ->> 'type') IN ('color','perennial','max_height'))
);
INSERT INTO plant_properties (id, plant_id, property) VALUES
(uuid_generate_v4(), 'a03a347f-8435-4caa-aeee-3eaaa38983ba', '{"type": "color", "value": "green"}'),
(uuid_generate_v4(), 'a03a347f-8435-4caa-aeee-3eaaa38983ba', '{"type": "perennial", "value": true}'),
(uuid_generate_v4(), 'a03a347f-8435-4caa-aeee-3eaaa38983ba', '{"type": "max_height", "value": 1.1, "units": "metre"}')
;
Вариант 1: использовать GIN индекс для jsonb (jsonb_ops) поля
-- создадим индекс
CREATE INDEX property_type_idx ON plant_properties USING GIN (property);
-- выполним поиск
SELECT *
FROM plant_properties
WHERE property ->> 'type' = 'perennial'
;
Вариант 2: Full-text search
Этот вариант полностью покрывается индексом GIN (jsonb_ops) для jsonb и приведен здесь как иллюстрация того, что при необходимости реализовать очеь специфические алгоритмы можно использовать и его.
-- создадим индекс
CREATE INDEX property_type_idx
ON plant_properties
USING GIN (to_tsvector('english', property ->> 'type'))
;
-- выполним поиск
SELECT *
FROM plant_properties
WHERE to_tsvector('english', property ->> 'type') @@ plainto_tsquery('english', 'perennial')
;
JSON в PostgreSQL стоит использовать в случаях если:
alter table может стать неразрешимой проблемой для васВ случае DWH, когда схема часто меняется, можно рассмотреть Data Vault или Anchor Modeling, что позволит получить выгоду от колоночно-ориентированных субд на больших данных.
JSON в PostgreSQL для OLTP назгрузки проигрывает в производительности реляционной схеме.
Вероятно, стоит потратить время на проектирование базы данных и получить хорошую производительность, отсутствие подводных камней и гарантии консистентности данных.