Введение
В этой лабораторной работе вы изучите расширенные операции с типом данных JSONB в PostgreSQL. Вы научитесь эффективно хранить, управлять и запрашивать данные JSON в реляционной базе данных.
Лабораторная работа проведет вас через создание таблицы с столбцом JSONB, вставку данных, а затем применение индекса GIN для улучшения производительности поиска. Вы также попрактикуетесь в запросе вложенных структур JSON, обновлении конкретных элементов в документе JSONB и выполнении агрегированных вычислений над вашими данными JSON.
Создание таблицы и индексирование полей JSONB
На этом шаге вы создадите таблицу для хранения информации о продуктах с использованием типа данных JSONB, а затем создадите GIN-индекс для оптимизации запросов к этим данным. JSONB хранит данные JSON в разложенном бинарном формате, что немного медленнее при вводе, но значительно быстрее при обработке. GIN (Generalized Inverted Index) идеально подходит для индексации составных значений, таких как те, что содержатся в столбце JSONB.
Сначала откройте терминал и подключитесь к базе данных PostgreSQL, используя интерактивную оболочку psql:
sudo -u postgres psql
Теперь вы увидите приглашение PostgreSQL, которое выглядит как postgres=#.
Далее создайте таблицу с именем products, содержащую столбец id и столбец data типа JSONB.
CREATE TABLE products (
id SERIAL PRIMARY KEY,
data JSONB
);
Теперь вставьте некоторые примеры данных о продуктах в таблицу products.
INSERT INTO products (data) VALUES
('{"name": "Laptop", "price": 1200, "tags": ["electronics", "computer"]}'),
('{"name": "Keyboard", "price": 75, "tags": ["electronics", "accessory"]}'),
('{"name": "Mouse", "price": 30, "tags": ["electronics", "accessory"]}'),
('{"name": "Monitor", "price": 300, "tags": ["electronics", "display"]}');
Чтобы значительно ускорить поиск по столбцу data, создайте на нем GIN-индекс.
CREATE INDEX idx_products_data ON products USING GIN (data);
Вы можете проверить успешность создания индекса, используя команду \di, которая выводит список всех индексов.
\di
В списке отношений вы должны увидеть idx_products_data, аналогично выводу ниже:
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+---------------------+-------+----------+-----------+------------+-------------
public | idx_products_data | index | postgres | products | 16 kB |
public | products_pkey | index | postgres | products | 16 kB |
(2 rows)
Вы успешно настроили таблицу и проиндексировали столбец JSONB. Вы останетесь в оболочке psql для следующего шага.
Запросы к вложенным структурам JSON
На этом шаге вы научитесь запрашивать данные внутри столбцов JSONB, включая вложенные структуры. PostgreSQL предоставляет несколько операторов для этой цели.
Сначала давайте вставим несколько продуктов с более сложными, вложенными данными в таблицу products.
INSERT INTO products (data) VALUES
('{"name": "Gaming PC", "price": 1500, "specs": {"cpu": "Intel i7", "ram": "32GB", "storage": "1TB SSD"}}'),
('{"name": "Office PC", "price": 800, "specs": {"cpu": "Intel i5", "ram": "16GB", "storage": "500GB SSD"}}');
Для доступа к ключу верхнего уровня вы можете использовать оператор ->>, который возвращает значение в виде текста. Давайте найдем цену "Laptop".
SELECT data ->> 'price' FROM products WHERE data ->> 'name' = 'Laptop';
Результат будет следующим:
price
-------
1200
(1 row)
Для доступа к ключу внутри вложенного JSON-объекта вы можете последовательно использовать операторы -> и ->>. Оператор -> получает поле JSON-объекта, а ->> получает его в виде текста. Давайте получим спецификацию CPU для "Gaming PC".
SELECT data -> 'specs' ->> 'cpu' FROM products WHERE data ->> 'name' = 'Gaming PC';
Эта команда вернет тип CPU:
?column?
--------------
Intel i7
(1 row)
Вы также можете использовать эти операторы в предложении WHERE для фильтрации результатов на основе вложенных значений. Например, найдите все продукты, у которых CPU "Intel i5".
SELECT data ->> 'name' FROM products WHERE data -> 'specs' ->> 'cpu' = 'Intel i5';
Запрос вернет имя соответствующего продукта:
name
-----------
Office PC
(1 row)
Теперь вы можете запрашивать как данные верхнего уровня, так и вложенные данные в ваших столбцах JSONB.
Обновление конкретных элементов JSONB
На этом шаге вы научитесь изменять конкретные элементы в столбце JSONB с помощью функции jsonb_set. Это эффективнее, чем извлекать весь JSON-объект, изменять его в вашем приложении и затем записывать обратно.
Функция jsonb_set имеет следующий синтаксис: jsonb_set(target_jsonb, path_array, new_value_jsonb, create_if_missing).
Давайте обновим цену "Laptop" с 1200 до 1250. Путь к цене — '{price}', а новое значение должно быть приведено к типу JSONB.
UPDATE products
SET data = jsonb_set(data, '{price}', '1250'::jsonb)
WHERE data ->> 'name' = 'Laptop';
Проверьте обновление, выбрав данные ноутбука.
SELECT data FROM products WHERE data ->> 'name' = 'Laptop';
Результат должен показать новую цену:
data
--------------------------------------------------------------------------
{"name": "Laptop", "price": 1250, "tags": ["electronics", "computer"]}
(1 row)
Вы также можете обновлять вложенные значения. Давайте обновим RAM для "Office PC" до "32GB". Путь к вложенному ключу ram — '{specs,ram}'.
UPDATE products
SET data = jsonb_set(data, '{specs,ram}', '"32GB"'::jsonb)
WHERE data ->> 'name' = 'Office PC';
Теперь проверьте это вложенное обновление.
SELECT data -> 'specs' FROM products WHERE data ->> 'name' = 'Office PC';
Результат подтвердит изменение значения ram:
?column?
------------------------------------------------------------
{"cpu": "Intel i5", "ram": "32GB", "storage": "500GB SSD"}
(1 row)
Теперь вы знаете, как выполнять целевые обновления данных JSONB.
Агрегация данных JSON
На этом заключительном шаге вы выполните агрегированные расчеты над данными, извлеченными из столбцов JSONB. Это полезно для отчетности и анализа.
Для выполнения расчетов часто требуется извлечь значение и привести его к числовому типу. Давайте рассчитаем среднюю цену всех продуктов.
SELECT AVG((data ->> 'price')::numeric) FROM products;
Запрос извлекает price как текст, приводит его к типу numeric, а затем вычисляет среднее значение. Результатом будет одно число:
avg
------------------------
659.1666666666666667
(1 row)
Вы также можете использовать агрегатные функции с предложением GROUP BY. Давайте найдем общее количество продуктов для каждого тега. Для этого нам сначала нужно развернуть массив tags в отдельные строки, используя jsonb_array_elements_text.
SELECT tag, COUNT(*)
FROM products, jsonb_array_elements_text(data -> 'tags') AS tag
GROUP BY tag;
Этот запрос выдает количество для каждого уникального тега:
tag | count
-------------+-------
accessory | 2
electronics | 4
computer | 1
display | 1
(4 rows)
Наконец, давайте найдем общую стоимость всех продуктов, имеющих тег "electronics".
SELECT SUM((data ->> 'price')::numeric)
FROM products
WHERE data -> 'tags' @> '"electronics"'::jsonb;
Оператор @> проверяет, содержит ли левое значение JSONB правое значение JSONB. Здесь мы проверяем, содержит ли массив tags элемент "electronics".
Результатом является сумма цен четырех электронных продуктов:
sum
--------
1655
(1 row)
Теперь вы знаете, как использовать агрегатные функции для ваших данных JSONB. Чтобы выйти из оболочки psql, введите:
\q
Резюме
В этой лабораторной работе вы изучили несколько продвинутых операций для работы с данными JSONB в PostgreSQL. Вы начали с создания таблицы со столбцом JSONB и увидели важность использования индекса GIN для оптимизации производительности запросов. Затем вы отработали запросы к данным JSONB как верхнего уровня, так и вложенным, используя операторы -> и ->>. Кроме того, вы научились выполнять целевые модификации документов JSONB с помощью функции jsonb_set и проводить мощный анализ данных с использованием агрегатных функций, таких как AVG, COUNT и SUM, на ваших данных JSON.
