Введение
В этой лабораторной работе вы изучите расширенные типы данных PostgreSQL, сосредоточившись на JSON/JSONB, массивах и UUID. Вы узнаете, как хранить, запрашивать и обрабатывать данные этих типов.
Лабораторная работа начинается с демонстрации того, как хранить и запрашивать данные JSON и JSONB, включая создание таблицы со столбцом JSONB, вставку JSON-данных и использование операторов -> и ->> для извлечения конкретных значений. Затем вы перейдете к изучению столбцов-массивов и UUID.
Хранение и запрос данных JSON и JSONB
На этом этапе вы узнаете, как хранить и запрашивать данные JSON и JSONB в PostgreSQL. PostgreSQL предоставляет два типа данных для хранения JSON: JSON и JSONB. Тип данных JSON хранит точную копию входного текста JSON, в то время как тип JSONB хранит данные JSON в декомпозированном бинарном формате. JSONB обычно предпочтительнее, так как он обеспечивает более высокую производительность при выполнении запросов и индексации.
Давайте начнем с открытия оболочки PostgreSQL. Сначала подключитесь к базе данных labex:
sudo -u postgres psql -d labex
Вы должны увидеть приглашение PostgreSQL:
labex=#
Теперь создадим таблицу для хранения данных JSONB:
CREATE TABLE products (id SERIAL PRIMARY KEY, data JSONB);
Эта SQL-команда создает таблицу с именем products. В таблице есть два столбца: id (автоинкрементный целочисленный первичный ключ) и data (столбец JSONB для хранения JSON-данных).
Вы должны увидеть вывод, похожий на этот:
CREATE TABLE
Теперь вставим некоторые данные в таблицу products:
INSERT INTO products (data) VALUES ('{"name": "Laptop", "price": 1200, "features": ["16GB RAM", "512GB SSD"]}');
INSERT INTO products (data) VALUES ('{"name": "Keyboard", "price": 75, "features": ["Mechanical", "RGB Backlight"]}');
Эти команды вставляют две строки в таблицу products. Каждая строка содержит JSON-объект с информацией о продукте.
Для каждой вставки вы должны увидеть вывод, похожий на этот:
INSERT 0 1
Для запроса JSON-данных можно использовать операторы -> и ->>. Оператор -> возвращает JSON-объект, а оператор ->> возвращает значение JSON в виде текста.
Например, чтобы получить имя первого продукта, можно использовать следующий запрос:
SELECT data ->> 'name' FROM products WHERE id = 1;
Эта команда выбирает значение, связанное с ключом name из столбца data таблицы products, где id равен 1. Оператор ->> гарантирует, что результат будет возвращен в виде текста.
Вы должны увидеть вывод, похожий на этот:
?column?
----------
Laptop
(1 row)
Вы также можете запрашивать вложенные JSON-объекты. Например, чтобы получить первую характеристику первого продукта, можно использовать следующий запрос:
SELECT data -> 'features' ->> 0 FROM products WHERE id = 1;
Эта команда сначала выбирает массив features из столбца data, а затем выбирает элемент с индексом 0 из этого массива. Оператор ->> гарантирует, что результат будет возвращен в виде текста.
Вы должны увидеть вывод, похожий на этот:
?column?
----------
16GB RAM
(1 row)
Вы также можете использовать оператор @>, чтобы проверить, содержит ли JSON-объект определенную пару ключ-значение. Например, чтобы найти все продукты с ценой 75, можно использовать следующий запрос:
SELECT data ->> 'name' FROM products WHERE data @> '{"price": 75}';
Эта команда выбирает имя всех строк из таблицы products, где столбец data содержит JSON-объект с ключом price и значением 75.
Вы должны увидеть вывод, похожий на этот:
?column?
----------
Keyboard
(1 row)
Отлично! Мы успешно создали нашу первую таблицу с данными JSONB и научились делать к ней запросы. Мы продолжим работать с этой таблицей на следующем этапе, чтобы добавить более продвинутые функции.
Добавление столбцов-массивов в существующую таблицу
На этом этапе вы узнаете, как добавлять столбцы-массивы и работать с ними в PostgreSQL. Мы расширим нашу существующую таблицу products, добавив столбец-массив для хранения тегов. Столбцы-массивы позволяют хранить несколько значений одного и того же типа данных в одном столбце, что полезно для хранения списков элементов, таких как теги, категории или характеристики.
Поскольку мы уже подключены к базе данных и у нас есть таблица products с предыдущего этапа, давайте добавим столбцы-массивы в нашу таблицу:
ALTER TABLE products ADD COLUMN name VARCHAR(255);
ALTER TABLE products ADD COLUMN tags TEXT[];
Эти команды добавляют два новых столбца в нашу таблицу products: name (строка) и tags (массив строк). Тип данных TEXT[] указывает, что столбец tags является массивом текстовых значений.
Для каждой команды ALTER вы должны увидеть вывод, похожий на этот:
ALTER TABLE
Теперь обновим существующие данные и вставим новые данные с использованием новых столбцов:
UPDATE products SET name = data ->> 'name' WHERE id = 1;
UPDATE products SET name = data ->> 'name' WHERE id = 2;
UPDATE products SET tags = ARRAY['electronics', 'computers', 'portable'] WHERE id = 1;
UPDATE products SET tags = ARRAY['electronics', 'accessories', 'input'] WHERE id = 2;
Эти команды обновляют наши существующие продукты информацией об имени и тегах, извлеченной из данных JSONB, и новыми значениями массивов.
Для каждого обновления вы должны увидеть вывод, похожий на этот:
UPDATE 1
Для запроса данных массива можно использовать индексацию массива. Индексы массивов в PostgreSQL начинаются с 1.
Например, чтобы получить первый тег первого продукта, можно использовать следующий запрос:
SELECT tags[1] FROM products WHERE id = 1;
Эта команда выбирает элемент с индексом 1 из массива tags таблицы products, где id равен 1.
Вы должны увидеть вывод, похожий на этот:
tags
-----------
electronics
(1 row)
Вы также можете использовать функцию UNNEST для развертывания массива в набор строк.
Например, чтобы получить все теги всех продуктов, можно использовать следующий запрос:
SELECT name, UNNEST(tags) AS tag FROM products;
Эта команда выбирает name и каждый отдельный tag из массива tags, создавая новую строку для каждого тега.
Вы должны увидеть вывод, похожий на этот:
name | tag
---------+-------------
Laptop | electronics
Laptop | computers
Laptop | portable
Keyboard| electronics
Keyboard| accessories
Keyboard| input
(6 rows)
Вы можете использовать оператор @>, чтобы проверить, содержит ли массив определенное значение.
Например, чтобы найти все продукты с тегом 'electronics', можно использовать следующий запрос:
SELECT name FROM products WHERE tags @> ARRAY['electronics'];
Эта команда выбирает имя всех строк из таблицы products, где массив tags содержит значение 'electronics'.
Вы должны увидеть вывод, похожий на этот:
name
----------
Laptop
Keyboard
(2 rows)
Вы также можете использовать оператор &&, чтобы проверить, есть ли у двух массивов общие элементы.
Например, чтобы найти все продукты, у которых есть общие теги с первым продуктом, можно использовать следующий запрос:
SELECT p2.name FROM products p1, products p2 WHERE p1.id = 1 AND p1.tags && p2.tags AND p2.id != 1;
Эта команда выбирает имя всех строк из таблицы products (с псевдонимом p2), у которых есть хотя бы один общий тег с тегами первого продукта (с псевдонимом p1), исключая сам первый продукт.
Вы должны увидеть вывод, похожий на этот:
name
----------
Keyboard
(1 row)
Отлично! Мы успешно добавили столбцы-массивы в нашу таблицу и научились работать с массивами. Теперь наша таблица products содержит как типы данных JSONB, так и массивы, и готова к следующему этапу.
Добавление столбца UUID и генерация UUID
На этом этапе вы узнаете, как генерировать и использовать UUID (универсально уникальные идентификаторы) в PostgreSQL. UUID — это 128-битные числа, которые спроектированы так, чтобы быть уникальными в пространстве и времени. Они часто используются в качестве уникальных идентификаторов в таблицах баз данных, чтобы избежать конфликтов при объединении данных из разных источников.
Поскольку мы продолжаем работать в текущем сеансе с нашей таблицей, мы добавим столбец UUID в таблицу products, чтобы продемонстрировать функциональность UUID.
Сначала включим расширение UUID, которое предоставляет функции генерации UUID:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
Эта команда создает расширение uuid-ossp, если оно еще не существует.
Вы должны увидеть вывод, похожий на этот:
CREATE EXTENSION
Теперь добавим столбец UUID в нашу таблицу products:
ALTER TABLE products ADD COLUMN uuid_id UUID;
Эта команда добавляет новый столбец UUID в нашу таблицу products.
Вы должны увидеть вывод, похожий на этот:
ALTER TABLE
Теперь мы можем обновить существующие строки значениями UUID, используя функцию uuid_generate_v4():
UPDATE products SET uuid_id = uuid_generate_v4() WHERE id = 1;
UPDATE products SET uuid_id = uuid_generate_v4() WHERE id = 2;
Эти команды обновляют наши существующие продукты уникальными значениями UUID. Функция uuid_generate_v4() генерирует новый UUID для каждой строки.
Для каждого обновления вы должны увидеть вывод, похожий на этот:
UPDATE 1
Для запроса данных с использованием UUID можно использовать значения UUID в предложениях WHERE. Поскольку UUID генерируются случайным образом, давайте сначала посмотрим на наши текущие данные:
SELECT id, name, uuid_id FROM products;
Эта команда выбирает все столбцы из таблицы products, чтобы увидеть сгенерированные UUID вместе с другими нашими данными.
Вы должны увидеть вывод, похожий на этот (ваш UUID будет другим):
id | name | uuid_id
----+----------+--------------------------------------
1 | Laptop | 8f14e45f-ea7b-4f9f-a2b0-73f9c3f85a9b
2 | Keyboard | c9f0f895-fb98-4635-bd31-4f7f4d8f9e7a
(2 rows)
Теперь используйте этот UUID в следующей команде, заменив <YOUR_UUID_HERE> на фактический UUID, который вы получили:
SELECT name FROM products WHERE uuid_id = '<YOUR_UUID_HERE>';
Эта команда выбирает name из таблицы products, где uuid_id соответствует указанному UUID.
Вы должны увидеть вывод, похожий на этот (в зависимости от того, какой UUID вы выбрали):
name
----------
Laptop
(1 row)
Сохраните таблицу products и расширение uuid-ossp, так как на следующем этапе мы продолжим их использовать.
Извлечение данных из расширенных типов
На этом заключительном этапе вы потренируетесь извлекать данные из всех расширенных типов данных, которые мы добавили в нашу таблицу products в ходе этой лабораторной работы. Теперь наша таблица содержит столбцы JSONB, массивы и UUID, что является исчерпывающим примером расширенных типов данных PostgreSQL.
Поскольку мы продолжаем работать в текущем сеансе с нашей таблицей, мы можем сразу приступить к работе с данными, которые мы накапливали на предыдущих этапах.
Сначала добавим дополнительные данные JSONB, чтобы наши примеры извлечения стали более полными:
UPDATE products SET data = '{"brand": "Dell", "model": "XPS 13", "specs": {"ram": "16GB", "storage": "512GB SSD"}, "warranty": "3 years"}' WHERE id = 1;
UPDATE products SET data = '{"brand": "Logitech", "model": "G915", "specs": {"type": "Mechanical", "backlight": "RGB"}, "warranty": "2 years"}' WHERE id = 2;
Эти команды обновляют наши существующие данные JSONB более подробной информацией, включая вложенные объекты и дополнительные поля.
Для каждого обновления вы должны увидеть вывод, похожий на этот:
UPDATE 1
Теперь давайте потренируемся извлекать данные из всех наших расширенных типов. Чтобы извлечь данные из столбца JSONB data, можно использовать операторы -> и ->>. Например, чтобы извлечь бренд из наших обновленных данных:
SELECT data ->> 'brand' FROM products WHERE id = 1;
Эта команда извлекает значение, связанное с ключом brand из столбца data.
Вы должны увидеть вывод, похожий на этот:
?column?
----------
Dell
(1 row)
Чтобы извлечь вложенные данные из столбца JSONB, можно объединить операторы -> и ->>. Например, чтобы извлечь характеристику RAM:
SELECT data -> 'specs' ->> 'ram' FROM products WHERE id = 1;
Эта команда извлекает значение, связанное с ключом ram внутри объекта specs.
Вы должны увидеть вывод, похожий на этот:
?column?
----------
16GB
(1 row)
Чтобы извлечь данные из столбца tags (массив), можно использовать индексацию массива, как мы узнали на этапе 2. Например, чтобы извлечь первый тег первого продукта:
SELECT tags[1] FROM products WHERE id = 1;
Эта команда извлекает элемент с индексом 1 из массива tags таблицы products, где id равен 1.
Вы должны увидеть вывод, похожий на этот:
tags
-----------
electronics
(1 row)
Теперь давайте создадим комплексный запрос, который извлекает данные из всех наших расширенных типов данных — JSONB, массивов и UUID:
SELECT
id,
name,
data ->> 'brand' AS brand,
data -> 'specs' ->> 'ram' AS ram,
tags[1] AS first_tag,
uuid_id
FROM products;
Эта команда извлекает данные из всех расширенных типов данных, с которыми мы работали: целочисленный id, name, brand из столбца JSONB data, вложенную характеристику ram, первый элемент массива tags и uuid_id.
Вы должны увидеть вывод, похожий на этот:
name | ?column? | tags
----------+------------+-----------
Laptop | Dell | electronics
Keyboard | Logitech | electronics
(2 rows)
Отлично! Вы успешно поработали со всеми тремя расширенными типами данных PostgreSQL в одной таблице. Этот комплексный пример демонстрирует, как JSONB, массивы и UUID могут использоваться вместе для создания гибких и мощных схем баз данных.
Когда вы закончите все этапы, вы можете выйти из оболочки PostgreSQL, введя:
\q
Вы также можете оставить таблицу для дальнейших экспериментов с расширенными типами данных PostgreSQL.
Резюме
В этой лабораторной работе вы постепенно создали и поработали с комплексной таблицей products, которая демонстрирует расширенные типы данных PostgreSQL в действии. Вы начали с создания таблицы со столбцами JSONB и научились хранить и запрашивать данные JSON с помощью операторов -> и ->>.
Затем вы расширили таблицу, добавив столбцы-массивы, научившись хранить несколько значений в одном столбце и запрашивать их с помощью индексации массивов и функций, таких как UNNEST. Далее вы добавили функциональность UUID, включив расширение uuid-ossp и добавив столбец UUID для генерации уникальных идентификаторов.
Наконец, вы попрактиковались в комплексных методах извлечения данных, которые объединили все три расширенных типа данных — JSONB, массивы и UUID — в сложных запросах. Этот пошаговый подход продемонстрировал, как эти типы данных могут работать вместе в реальных схемах баз данных, обеспечивая гибкость и мощные возможности запросов для современных приложений.
