Подходит ли платформа для начинающих без опыта работы?

Да, курсы разбиты по уровням: Junior, Middle, Senior. Начинающие могут стартовать с базовых тем Python, Docker и алгоритмов и постепенно двигаться к более сложным темам.

Как быстро можно подготовиться к собеседованию на позицию Junior разработчика?

При занятиях 1–2 часа в день — от 2 до 4 недель на основные темы. Платформа анализирует слабые места по результатам квизов и строит персональный план подготовки.

Какие технологии охватывает платформа?

Python, FastAPI, Django, Docker, алгоритмы и структуры данных, Agile/Scrum, SQL, CI/CD, системный дизайн, код-ревью и более 50 других тем для разработчиков.

Платформа бесплатная?

Большинство учебных материалов и квизов доступны бесплатно после регистрации. Регистрация занимает менее минуты.

Как платформа помогает найти работу программистом?

Платформа даёт фундаментальные знания, которые проверяют на технических собеседованиях: алгоритмы, архитектура, фреймворки. Мок-интервью имитирует реальное собеседование. Система прогресса показывает, какие темы нужно подтянуть перед собеседованием.

jsonb_arrays

JSONB и массивы

JSONB операторы, GIN для JSONB, массивы, array_agg, unnest

JSONB и массивы в PostgreSQL

PostgreSQL — это не просто реляционная СУБД. Это гибридная база данных, которая объединяет лучшие черты реляционных и NoSQL-решений. Две ключевые возможности, делающие PostgreSQL уникальным: JSONB для работы с полуструктурированными данными и массивы для хранения коллекций значений.

Почему JSONB, а не JSON?

PostgreSQL поддерживает два типа для хранения JSON-данных:

Тип	Описание	Когда использовать
`JSON`	Текстовое представление, хранится как строка	Для аудита, логирования, когда важна точность представления
`JSONB`	Бинарное представление, разобранное и оптимизированное	В 99% случаев — для запросов, индексирования, модификации

Преимущества JSONB:

🚀 Быстрее обработка — данные уже разобраны, не нужно парсить при каждом запросе
📊 Поддержка индексов — можно создавать GIN-индексы для ускорения поиска
🔧 Поддержка модификации — можно изменять отдельные ключи без перезаписи всего документа
🎯 Проверка структуры — автоматически валидируется корректность JSON

JSONB

Создание таблицы с JSONB

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    data JSONB NOT NULL
);

INSERT INTO users (name, data) VALUES
('Alice', '{"email": "alice@example.com", "age": 25, "city": "Moscow"}'),
('Bob', '{"email": "bob@example.com", "age": 30, "city": "London"}'),
('Charlie', '{"email": "charlie@example.com", "age": 35, "city": "Moscow", "premium": true}');

Важно: Значения JSONB должны быть валидным JSON. Ключи всегда приводятся к строкам в двойных кавычках.

Операторы JSONB

PostgreSQL предоставляет богатый набор операторов для работы с JSONB:

Оператор	Описание	Возвращает	Пример	Результат
`->`	Извлечение по ключу	`jsonb`	`data->'email'`	`"alice@example.com"` (JSONB)
`->>`	Извлечение по ключу	`text`	`data->>'email'`	`alice@example.com` (текст)
`#>`	Извлечение по пути	`jsonb`	`data#>'{address,city}'`	JSONB значение
`#>>`	Извлечение по пути	`text`	`data#>>'{address,city}'`	текст
`@>`	Содержит ли документ	`boolean`	`data @> '{"city": "Moscow"}'`	`true`/`false`
`<@`	Содержится ли в документе	`boolean`	`'{"city": "Moscow"}' <@ data`	`true`/`false`
`?`	Есть ли ключ	`boolean`	`data ? 'email'`	`true`/`false`
`?	`	Есть ли любой из ключей	`boolean`	`data ?
`?&`	Есть ли все ключи	`boolean`	`data ?& array['email', 'phone']`	`true`/`false`
`		`	Конкатенация объектов	`jsonb`
`-`	Удаление ключа	`jsonb`	`data - 'age'`	объект без ключа
`#-`	Удаление по пути	`jsonb`	`data #- '{address,city}'`	объект без пути

Детальные примеры использования операторов

-- Извлечение значения как JSONB (остаётся в кавычках)
SELECT data->'email' FROM users WHERE id = 1;
-- Результат: "alice@example.com" (тип jsonb)

-- Извлечение значения как текст (без кавычек)
SELECT data->>'email' FROM users WHERE id = 1;
-- Результат: alice@example.com (тип text)

-- Извлечение по пути (для вложенных структур)
-- Предполагается структура: {"address": {"city": "Moscow"}}
SELECT data#>>'{address,city}' FROM users WHERE id = 1;

-- Поиск по точному совпадению значения
SELECT * FROM users WHERE data->>'city' = 'Moscow';

-- Поиск по содержанию (использует GIN-индекс!)
SELECT * FROM users WHERE data @> '{"city": "Moscow"}';

-- Проверка наличия ключа
SELECT * FROM users WHERE data ? 'email';

-- Проверка наличия любого из ключей
SELECT * FROM users WHERE data ?| array['email', 'phone'];

-- Проверка наличия всех ключей
SELECT * FROM users WHERE data ?& array['email', 'age'];

Работа с вложенными структурами

JSONB отлично справляется с глубоко вложенными данными:

-- Пример сложной структуры
INSERT INTO users (name, data) VALUES
('Diana', '{
    "email": "diana@example.com",
    "age": 28,
    "address": {
        "city": "Saint Petersburg",
        "street": "Nevsky",
        "building": 10
    },
    "preferences": {
        "notifications": true,
        "theme": "dark"
    },
    "tags": ["developer", "python", "postgresql"]
}');

-- Доступ к вложенным значениям
SELECT data->'address'->>'city' FROM users WHERE name = 'Diana';
-- Результат: Saint Petersburg

-- Альтернативно с использованием пути
SELECT data#>>'{address,city}' FROM users WHERE name = 'Diana';

-- Поиск по вложенному значению
SELECT * FROM users 
WHERE data->'address'->>'city' = 'Saint Petersburg';

-- Поиск по вложенному содержанию
SELECT * FROM users 
WHERE data @> '{"address": {"city": "Saint Petersburg"}}';

Индексирование JSONB

Правильное индексирование — ключ к высокой производительности запросов с JSONB.

Типы индексов для JSONB

1. GIN-индекс на весь документ

CREATE INDEX idx_data ON users USING GIN (data);

Что индексирует: Все ключи и значения в документе.

Когда использовать: Для запросов с операторами @>, <@, ?, ?|, ?&.

Пример запроса:

-- Будет использовать GIN-индекс
SELECT * FROM users WHERE data @> '{"city": "Moscow"}';

-- Будет использовать GIN-индекс
SELECT * FROM users WHERE data ? 'email';

2. GIN-индекс с jsonb_path_ops

CREATE INDEX idx_data_keys ON users USING GIN (data jsonb_path_ops);

Что индексирует: Только ключи и значения, но более компактно.

Когда использовать: Только для запросов с @> и <@. Меньше места, быстрее обновление.

Ограничение: Не поддерживает операторы ?, ?|, ?&.

3. Индекс по выражению

CREATE INDEX idx_city ON users ((data->>'city'));

Что индексирует: Конкретное значение по пути.

Когда использовать: Для частых запросов по конкретному полю.

Пример запроса:

-- Будет использовать индекс по выражению
SELECT * FROM users WHERE data->>'city' = 'Moscow';

Сравнение стратегий индексирования

Стратегия	Размер	Скорость записи	Поддерживаемые операторы	Лучший сценарий
`GIN (data)`	Большой	Медленнее	Все	Разнообразные запросы
`GIN (data jsonb_path_ops)`	Малый	Быстрее	Только `@>`, `<@`	Частый поиск по содержанию
`Индекс по выражению`	Малый	Быстро	Только `=` для конкретного поля	Частые запросы по одному полю

Проверка использования индекса

-- Включите отображение плана выполнения
EXPLAIN ANALYZE
SELECT * FROM users WHERE data @> '{"city": "Moscow"}';

-- Вывод покажет: "Index Scan using idx_data ..."
-- Если видите "Seq Scan" — индекс не используется

Модификация JSONB

Одно из главных преимуществ JSONB перед JSON — возможность изменять отдельные части документа.

Функции для модификации

jsonb_set(target, path, new_value, create_missing)

Обновляет значение по указанному пути.

-- Обновление значения
UPDATE users
SET data = jsonb_set(data, '{city}', '"Saint Petersburg"')
WHERE id = 1;

-- Обновление вложенного значения
UPDATE users
SET data = jsonb_set(
    data,
    '{address,city}',
    '"Saint Petersburg"'::jsonb
)
WHERE data->'address'->>'city' = 'Moscow';

-- Четвёртый параметр: создавать ли отсутствующие ключи (по умолчанию true)
UPDATE users
SET data = jsonb_set(data, '{new_key}', '"value"', false)
WHERE id = 1;

Оператор конкатенации ||

Объединяет два JSONB-документа. При конфликте ключей побеждает правый документ.

-- Добавление нового ключа
UPDATE users
SET data = data || '{"phone": "+7-999-123-45-67"}'::jsonb
WHERE id = 1;

-- Обновление нескольких ключей
UPDATE users
SET data = data || '{"age": 26, "verified": true}'::jsonb
WHERE id = 1;

-- Слияние объектов
SELECT 
    '{"a": 1, "b": 2}'::jsonb || '{"b": 3, "c": 4}'::jsonb;
-- Результат: {"a": 1, "b": 3, "c": 4}

Оператор удаления -

Удаляет ключи из документа.

-- Удаление одного ключа
UPDATE users
SET data = data - 'age'
WHERE id = 1;

-- Удаление нескольких ключей
UPDATE users
SET data = data - '{age, phone}'
WHERE id = 1;

-- Удаление вложенного ключа
UPDATE users
SET data = data #- '{address,city}'
WHERE id = 1;

jsonb_insert(target, path, new_value, insert_after)

Вставляет значение в массив по указанному пути.

-- Вставка в массив (по умолчанию в начало)
UPDATE users
SET data = jsonb_insert(data, '{tags, 0}', '"new_tag"'::jsonb)
WHERE id = 1;

-- Вставка после указанного индекса
UPDATE users
SET data = jsonb_insert(data, '{tags, 0}', '"new_tag"'::jsonb, true)
WHERE id = 1;

Агрегатные функции JSONB

PostgreSQL предоставляет мощные функции для агрегации данных в JSONB.

Создание JSONB из строк

-- jsonb_build_object: создание объекта из пар ключ-значение
SELECT jsonb_build_object(
    'name', 'Alice',
    'age', 25,
    'active', true
);
-- Результат: {"name": "Alice", "age": 25, "active": true}

-- jsonb_build_array: создание массива из значений
SELECT jsonb_build_array(1, 2, 'text', true);
-- Результат: [1, 2, "text", true]

Агрегация строк в JSONB

-- jsonb_object_agg: создание объекта из двух колонок
SELECT jsonb_object_agg(key, value)
FROM (VALUES ('name', 'Alice'), ('age', '25')) AS t(key, value);
-- Результат: {"name": "Alice", "age": "25"}

-- jsonb_agg: агрегация значений в массив
SELECT jsonb_agg(name) FROM users;
-- Результат: ["Alice", "Bob", "Charlie"]

-- jsonb_agg с объектами
SELECT jsonb_agg(
    jsonb_build_object('id', id, 'name', name)
) FROM users;
-- Результат: [{"id": 1, "name": "Alice"}, ...]

Практический пример агрегации

-- Группировка пользователей по городам
SELECT
    data->>'city' AS city,
    jsonb_agg(
        jsonb_build_object(
            'name', name,
            'email', data->>'email'
        )
    ) AS users
FROM users
GROUP BY data->>'city';

-- Результат:
-- city         | users
-- -------------+--------------------------------------------------
-- Moscow       | [{"name": "Alice", "email": "alice@example.com"}]
-- London       | [{"name": "Bob", "email": "bob@example.com"}]

Работа с массивами внутри JSONB

JSONB может содержать массивы, которые тоже можно обрабатывать.

-- Пример данных с массивами
INSERT INTO users (name, data) VALUES
('Eve', '{"name": "Eve", "tags": ["python", "django", "postgresql"]}'),
('Frank', '{"name": "Frank", "tags": ["java", "spring", "postgresql"]}');

-- Проверка типа значения
SELECT jsonb_typeof(data->'tags') FROM users;
-- Результат: "array"

-- Доступ к элементу массива по индексу
SELECT data->'tags'->0 FROM users WHERE name = 'Eve';
-- Результат: "python"

-- Проверка наличия значения в массиве
SELECT * FROM users WHERE data->'tags' ? 'postgresql';

-- Разворачивание массива в строки
SELECT 
    name,
    unnest(data->'tags') AS tag
FROM users
WHERE jsonb_typeof(data->'tags') = 'array';

-- Результат:
-- name  | tag
-- ------+-------------
-- Eve   | python
-- Eve   | django
-- Eve   | postgresql
-- Frank | java
-- Frank | spring
-- Frank | postgresql

-- Агрегация обратно в массив
SELECT 
    name,
    jsonb_agg(unnest(data->'tags')) AS all_tags
FROM users
GROUP BY name;

Массивы

PostgreSQL поддерживает нативные массивы для любого типа данных: TEXT[], INTEGER[], TIMESTAMP[] и т.д.

Создание таблиц с массивами

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    tags TEXT[] DEFAULT '{}',
    views INTEGER[] DEFAULT '{}'
);

INSERT INTO posts (title, tags) VALUES
('PostgreSQL Guide', '{"postgresql", "sql", "database"}'),
('Python Tips', '{"python", "programming", "tips"}'),
('Web Development', '{"html", "css", "javascript"}');

Синтаксис литералов массивов:

'{a, b, c}' — строковый литерал массива
ARRAY['a', 'b', 'c'] — конструктор массивов (более читаемый)
'{}' — пустой массив

Доступ к элементам массива

Важно: В PostgreSQL индексация массивов начинается с 1, а не с 0!

-- Доступ по индексу
SELECT tags[1] FROM posts WHERE id = 1;
-- Результат: postgresql

-- Доступ к диапазону (срез)
SELECT tags[1:2] FROM posts WHERE id = 1;
-- Результат: {postgresql,sql}

-- Длина массива
SELECT array_length(tags, 1) FROM posts WHERE id = 1;
-- Результат: 3

-- Проверка на пустоту
SELECT * FROM posts WHERE tags = '{}';
SELECT * FROM posts WHERE array_length(tags, 1) IS NULL;

Модификация массивов

-- Добавление элемента в конец
UPDATE posts
SET tags = array_append(tags, 'tutorial')
WHERE id = 1;

-- Добавление элемента в начало
UPDATE posts
SET tags = array_prepend('new', tags)
WHERE id = 1;

-- Удаление элемента по значению
UPDATE posts
SET tags = array_remove(tags, 'deprecated')
WHERE id = 1;

-- Замена элемента по индексу
UPDATE posts
SET tags[1] = 'postgres'
WHERE id = 1;

-- Объединение массивов
UPDATE posts
SET tags = tags || ARRAY['new_tag', 'another_tag']
WHERE id = 1;

-- Вставка элемента на позицию
UPDATE posts
SET tags = array_insert(tags, 2, 'inserted_tag')
WHERE id = 1;

Операторы массивов

Оператор	Описание	Пример
`=`	Равенство	`tags = ARRAY['a', 'b']`
`<>`	Неравенство	`tags <> ARRAY['a']`
`@>`	Содержит	`tags @> ARRAY['postgresql']`
`<@`	Содержится в	`ARRAY['sql'] <@ tags`
`&&`	Пересекается	`tags && ARRAY['python', 'java']`
`		`

Поиск в массивах

-- Проверка: содержит ли массив значение (ANY)
SELECT * FROM posts
WHERE 'postgresql' = ANY(tags);

-- Проверка: все ли элементы соответствуют условию (ALL)
SELECT * FROM posts
WHERE 'deprecated' != ALL(tags);

-- Поиск по подмассиву (содержит все элементы)
SELECT * FROM posts
WHERE tags @> ARRAY['postgresql', 'sql'];

-- Поиск по пересечению (содержит хотя бы один элемент)
SELECT * FROM posts
WHERE tags && ARRAY['postgresql', 'python'];

-- Поиск по точному совпадению
SELECT * FROM posts
WHERE tags = ARRAY['postgresql', 'sql', 'database'];

Агрегатные функции для массивов

-- array_agg: агрегация значений в массив
SELECT user_id, array_agg(order_id) AS orders
FROM orders
GROUP BY user_id;
-- Результат: {101, 102, 105}

-- array_agg с DISTINCT
SELECT user_id, array_agg(DISTINCT product_id) AS products
FROM orders
GROUP BY user_id;

-- array_agg с сортировкой
SELECT user_id, array_agg(order_id ORDER BY created_at) AS orders
FROM orders
GROUP BY user_id;

-- unnest: разворачивание массива в строки
SELECT unnest(ARRAY[1, 2, 3]) AS num;
-- Результат:
-- num
-- ---
-- 1
-- 2
-- 3

-- unnest с несколькими массивами (как zip)
SELECT 
    unnest(ARRAY[1, 2, 3]) AS id,
    unnest(ARRAY['a', 'b', 'c']) AS letter;
-- Результат:
-- id | letter
-- ---+--------
-- 1  | a
-- 2  | b
-- 3  | c

-- unnest_with_ordinality: с номерами строк
SELECT * FROM unnest(ARRAY['a', 'b', 'c']) WITH ORDINALITY AS t(val, num);
-- Результат:
-- val | num
-- ----+-----
-- a   | 1
-- b   | 2
-- c   | 3

Индексирование массивов

Для эффективного поиска по массивам используйте GIN-индексы:

-- GIN-индекс для операторов @>, <@, &&
CREATE INDEX idx_tags ON posts USING GIN (tags);

-- Поиск будет использовать индекс
SELECT * FROM posts WHERE tags @> ARRAY['postgresql'];
SELECT * FROM posts WHERE tags && ARRAY['python', 'java'];

-- GIN-индекс с array_ops (по умолчанию)
-- Поддерживает: @>, <@, =, &&
CREATE INDEX idx_tags ON posts USING GIN (tags);

-- GIN-индекс с array_ops для оператора = (точное совпадение)
-- Создаётся отдельно для оптимизации точных совпадений
CREATE INDEX idx_tags_exact ON posts USING BTREE (tags);

Многомерные массивы

PostgreSQL поддерживает многомерные массивы:

-- Создание двумерного массива
CREATE TABLE matrix (
    id SERIAL,
    data INTEGER[][]
);

INSERT INTO matrix (data) VALUES
('{{1,2,3},{4,5,6},{7,8,9}}');

-- Доступ к элементу
SELECT data[2][3] FROM matrix WHERE id = 1;
-- Результат: 6 (вторая строка, третий столбец)

Специализированные типы данных

UUID (Universally Unique Identifier)

UUID — это 128-битные идентификаторы, уникальные во всём мире.

-- Включение расширения для генерации UUID
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Таблица с UUID
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Генерация UUID
SELECT gen_random_uuid();
-- Результат: a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

-- Вставка с автоматической генерацией
INSERT INTO users (name) VALUES ('Alice');

-- Поиск по UUID
SELECT * FROM users WHERE id = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';

-- Индекс (автоматически создаётся для PRIMARY KEY)
CREATE INDEX idx_users_id ON users(id);

Преимущества UUID:

🔒 Безопасность — невозможно угадать следующий ID
🌍 Глобальная уникальность — можно генерировать на разных серверах
🔄 Не требуют координации — нет необходимости в последовательностях

Недостатки:

📦 Больший размер — 16 байт против 4-8 байт для INTEGER
🐌 Медленнее индексация — случайное распределение в B-дереве
📝 Менее читаемы — сложнее для ручного ввода

INET и CIDR

Типы для работы с IP-адресами и сетями.

CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    ip_address INET,      -- Отдельный IP-адрес
    network CIDR,         -- Сеть (подсеть)
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO logs (ip_address, network) VALUES
('192.168.1.1', '192.168.1.0/24'),
('10.0.0.1', '10.0.0.0/8'),
('2001:db8::1', '2001:db8::/32');  -- IPv6 тоже поддерживается

Операторы для IP-адресов

Оператор	Описание	Пример
`<<`	Находится внутри сети	`ip_address << '192.168.1.0/24'`
`>>`	Содержит сеть	`'192.168.0.0/16' >> ip_address`
`<=<`	Находится внутри или граница	`ip_address <=< '192.168.1.0/24'`
`>=<`	Содержит или граница	`'192.168.0.0/16' >=< ip_address`
`&&`	Пересекается	`network && '192.168.0.0/16'`
`=`	Равно	`ip_address = '192.168.1.1'`

-- Поиск IP внутри сети
SELECT * FROM logs
WHERE ip_address << '192.168.1.0/24';

-- Поиск сетей, содержащих IP
SELECT * FROM logs
WHERE '192.168.1.1' <<= ip_address;

-- Извлечение частей адреса
SELECT 
    host(ip_address) AS ip_string,
    network(ip_address) AS network_addr,
    masklen(ip_address) AS prefix_length,
    family(ip_address) AS ip_version  -- 4 или 6
FROM logs;

-- Преобразование между типами
SELECT '192.168.1.0/24'::cidr::inet;  -- CIDR -> INET

Практические примеры

Пример 1: Система тегов с JSONB

-- Таблица статей с тегами в JSONB
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    metadata JSONB NOT NULL
);

INSERT INTO articles (title, metadata) VALUES
('PostgreSQL Guide', '{
    "tags": ["postgresql", "sql", "database"],
    "author": {"id": 1, "name": "Alice"},
    "views": 1000,
    "published": true
}');

-- Поиск статей по тегу
SELECT * FROM articles
WHERE metadata->'tags' ? 'postgresql';

-- Поиск статей по автору
SELECT * FROM articles
WHERE metadata->'author'->>'id' = '1';

-- Агрегация: топ авторов по количеству статей
SELECT 
    metadata->'author'->>'name' AS author,
    COUNT(*) AS articles_count
FROM articles
GROUP BY metadata->'author'->>'name'
ORDER BY articles_count DESC;

-- Обновление счётчика просмотров
UPDATE articles
SET metadata = jsonb_set(
    metadata,
    '{views}',
    (COALESCE((metadata->>'views')::integer, 0) + 1)::text::jsonb
)
WHERE id = 1;

Пример 2: Гибридная модель (реляционная + JSONB)

-- Основные поля — реляционные, дополнительные — JSONB
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC NOT NULL,
    category_id INTEGER REFERENCES categories(id),
    attributes JSONB,  -- Дополнительные характеристики
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO products (name, price, category_id, attributes) VALUES
('Ноутбук', 99999, 1, '{
    "cpu": "Intel Core i7",
    "ram": "16GB",
    "storage": "512GB SSD"
}'),
('Смартфон', 49999, 2, '{
    "display": "6.5 OLED",
    "camera": "48MP",
    "battery": "4000mAh"
}');

-- Индекс для поиска по характеристикам
CREATE INDEX idx_product_attributes ON products USING GIN (attributes);

-- Поиск по характеристике
SELECT * FROM products
WHERE attributes @> '{"cpu": "Intel Core i7"}';

-- Поиск по диапазону (извлечение и приведение типа)
SELECT * FROM products
WHERE (attributes->>'ram')::text LIKE '%16GB%';

Пример 3: Аудит изменений с JSONB

-- Таблица для хранения истории изменений
CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    record_id INTEGER NOT NULL,
    action TEXT NOT NULL,  -- INSERT, UPDATE, DELETE
    old_data JSONB,        -- Данные до изменения
    new_data JSONB,        -- Данные после изменения
    changed_at TIMESTAMP DEFAULT NOW(),
    changed_by INTEGER
);

-- Функция для автоматического аудита
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, record_id, action, new_data)
        VALUES (TG_TABLE_NAME, NEW.id, 'INSERT', to_jsonb(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, record_id, action, old_data, new_data)
        VALUES (TG_TABLE_NAME, NEW.id, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, record_id, action, old_data)
        VALUES (TG_TABLE_NAME, OLD.id, 'DELETE', to_jsonb(OLD));
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Создание триггера
CREATE TRIGGER users_audit
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

-- Просмотр истории
SELECT 
    action,
    old_data->>'name' AS old_name,
    new_data->>'name' AS new_name,
    changed_at
FROM audit_log
WHERE table_name = 'users'
ORDER BY changed_at DESC;

Пример 4: Массивы для хранения связей

-- Таблица задач с назначенными исполнителями
CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    assignee_ids INTEGER[] DEFAULT '{}',
    status TEXT DEFAULT 'open'
);

INSERT INTO tasks (title, assignee_ids) VALUES
('Разработать API', '{1, 2, 3}'),
('Написать тесты', '{2, 4}'),
('Деплой', '{1}');

-- Найти все задачи конкретного исполнителя
SELECT * FROM tasks
WHERE 1 = ANY(assignee_ids);

-- Найти задачи, где работают вместе два исполнителя
SELECT * FROM tasks
WHERE assignee_ids @> ARRAY[1, 2];

-- Найти задачи, где есть хотя бы один из исполнителей
SELECT * FROM tasks
WHERE assignee_ids && ARRAY[1, 5];

-- Добавить исполнителя к задаче
UPDATE tasks
SET assignee_ids = array_append(assignee_ids, 5)
WHERE id = 1;

-- Удалить исполнителя из задачи
UPDATE tasks
SET assignee_ids = array_remove(assignee_ids, 3)
WHERE id = 1;

-- Посчитать количество задач у каждого исполнителя
SELECT 
    unnest(assignee_ids) AS assignee_id,
    COUNT(*) AS tasks_count
FROM tasks
GROUP BY unnest(assignee_ids)
ORDER BY tasks_count DESC;

Пример 5: Полнотекстовый поиск с JSONB

-- Таблица документов
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content JSONB,
    search_vector TSVECTOR
);

-- Создание поискового вектора из JSONB
UPDATE documents
SET search_vector = 
    setweight(to_tsvector('russian', COALESCE(title, '')), 'A') ||
    setweight(to_tsvector('russian', COALESCE(content->>'body', '')), 'B');

-- Индекс для полнотекстового поиска
CREATE INDEX idx_docs_search ON documents USING GIN (search_vector);

-- Поиск
SELECT * FROM documents
WHERE search_vector @@ to_tsquery('russian', 'PostgreSQL & база');

-- Автоматическое обновление через триггер
CREATE TRIGGER documents_search_update
BEFORE INSERT OR UPDATE ON documents
FOR EACH ROW EXECUTE FUNCTION tsvector_update_trigger(
    search_vector,
    'pg_catalog.russian',
    title,
    content->>'body'
);

Производительность и лучшие практики

Когда использовать JSONB

✅ Хорошие сценарии:

Дополнительные атрибуты, которые отличаются между записями
Быстро меняющаяся структура данных
Интеграция с внешними API (хранение ответов)
Аудит и логирование
Прототипирование и MVP

❌ Плохие сценарии:

Критичные для производительности запросы (лучше нормализовать)
Данные, по которым часто нужна агрегация
Сложные JOIN-операции внутри JSONB
Данные, требующие строгой валидации схемы

Оптимизация запросов

-- ❌ Плохо: извлечение на каждой строке
SELECT * FROM users
WHERE (data->>'age')::integer > 25;

-- ✅ Хорошо: использование @> с GIN-индексом
SELECT * FROM users
WHERE data @> '{"age": 26}';  -- Для точных значений

-- ✅ Хорошо: индекс по выражению для диапазонов
CREATE INDEX idx_age ON users ((data->>'age')::integer);

SELECT * FROM users
WHERE (data->>'age')::integer > 25;

Валидация JSONB

-- Проверка типа значения
SELECT jsonb_typeof(data->'age') FROM users;

-- Проверка существования ключа
SELECT data ? 'email' FROM users;

-- Ограничение CHECK для обязательных ключей
ALTER TABLE users ADD CONSTRAINT check_data_structure
CHECK (
    data ? 'email' AND
    jsonb_typeof(data->'email') = 'string' AND
    data ? 'age' AND
    jsonb_typeof(data->'age') = 'number'
);

Сравнение: JSONB vs Массивы vs Отдельная таблица

Критерий	JSONB	Массивы	Отдельная таблица
Гибкость схемы	Высокая	Средняя	Низкая
Производительность чтения	Средняя	Высокая	Зависит от индексов
Производительность записи	Средняя	Высокая	Низкая (JOIN)
Валидация данных	Слабая	Типизированная	Строгая
Сложные запросы	Сложно	Ограниченно	Отлично
Целостность (FK)	Нет	Нет	Да
Размер хранения	Средний	Малый	Большой

Рекомендация: Используйте комбинацию подходов. Основные данные — в реляционной структуре, дополнительные/гибкие — в JSONB, простые коллекции — в массивах.

Распространённые ошибки

1. Извлечение значений без индекса

-- ❌ Медленно: полное сканирование
SELECT * FROM users WHERE data->>'city' = 'Moscow';

-- ✅ Быстро: индекс по выражению
CREATE INDEX idx_city ON users ((data->>'city'));
SELECT * FROM users WHERE data->>'city' = 'Moscow';

2. Неправильное приведение типов

-- ❌ Ошибка: сравнение текста с числом
SELECT * FROM users WHERE data->>'age' = 25;

-- ✅ Правильно: явное приведение
SELECT * FROM users WHERE (data->>'age')::integer = 25;

-- ✅ Лучше: использование @>
SELECT * FROM users WHERE data @> '{"age": 25}';

3. Хранение всего в JSONB

-- ❌ Плохо: всё в JSONB, невозможно обеспечить целостность
CREATE TABLE users (data JSONB);

-- ✅ Хорошо: гибридный подход
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    metadata JSONB
);

4. Игнорирование размера JSONB-документов

Большие JSONB-документы замедляют обработку. Если документ превышает 1 КБ, рассмотрите:

Вынос часто используемых полей в отдельные колонки
Разделение на несколько JSONB-полей
Нормализацию в отдельные таблицы

Контрольный список

Перед использованием JSONB/массивов проверьте:

Выбран правильный тип (JSONB, а не JSON)
Создан подходящий индекс для частых запросов
Критичные поля вынесены в отдельные колонки
Предусмотрена валидация структуры данных
Протестирована производительность на реальных объёмах
План запросов проверен через EXPLAIN ANALYZE

Готовы проверить знания? Переходите к вопросам!

Проверьте свои знания

Вопросы ещё не добавлены

Вопросы для этой подтемы ещё не добавлены.

Тип

Описание

Когда использовать

JSON

Текстовое представление, хранится как строка

Для аудита, логирования, когда важна точность представления

JSONB

Бинарное представление, разобранное и оптимизированное

В 99% случаев — для запросов, индексирования, модификации

Оператор

Описание

Возвращает

Пример

Результат

->

Извлечение по ключу

jsonb

data->'email'

"alice@example.com" (JSONB)

->>

Извлечение по ключу

text

data->>'email'

alice@example.com (текст)

#>

Извлечение по пути

jsonb

data#>'{address,city}'

JSONB значение

#>>

Извлечение по пути

text

data#>>'{address,city}'

текст

@>

Содержит ли документ

boolean

data @> '{"city": "Moscow"}'

true/false

<@

Содержится ли в документе

boolean

'{"city": "Moscow"}' <@ data

true/false

?

Есть ли ключ

boolean

data ? 'email'

true/false

Есть ли любой из ключей

boolean

`data ?

?&

Есть ли все ключи

boolean

data ?& array['email', 'phone']

true/false

Конкатенация объектов

jsonb

-

Удаление ключа

jsonb

data - 'age'

объект без ключа

#-

Удаление по пути

jsonb

data #- '{address,city}'

объект без пути

Стратегия

Размер

Скорость записи

Поддерживаемые операторы

Лучший сценарий

GIN (data)

Большой

Медленнее

Все

Разнообразные запросы

GIN (data jsonb_path_ops)

Малый

Быстрее

Только @>, <@

Частый поиск по содержанию

Индекс по выражению

Малый

Быстро

Только = для конкретного поля

Частые запросы по одному полю

Оператор

Описание

Пример

=

Равенство

tags = ARRAY['a', 'b']

<>

Неравенство

tags <> ARRAY['a']

@>

Содержит

tags @> ARRAY['postgresql']

<@

Содержится в

ARRAY['sql'] <@ tags

&&

Пересекается

tags && ARRAY['python', 'java']

Оператор

Описание

Пример

<<

Находится внутри сети

ip_address << '192.168.1.0/24'

>>

Содержит сеть

'192.168.0.0/16' >> ip_address

<=<

Находится внутри или граница

ip_address <=< '192.168.1.0/24'

>=<

Содержит или граница

'192.168.0.0/16' >=< ip_address

&&

Пересекается

network && '192.168.0.0/16'

=

Равно

ip_address = '192.168.1.1'

Критерий

JSONB

Массивы

Отдельная таблица

Гибкость схемы

Высокая

Средняя

Низкая

Производительность чтения

Средняя

Высокая

Зависит от индексов

Производительность записи

Средняя

Высокая

Низкая (JOIN)

Валидация данных

Слабая

Типизированная

Строгая

Сложные запросы

Сложно

Ограниченно

Отлично

Целостность (FK)

Нет

Да

Размер хранения

Средний

Малый

Большой