Introdução
Neste laboratório, você explorará os tipos de dados avançados do PostgreSQL, concentrando-se em JSON/JSONB, arrays e UUIDs. Você aprenderá como armazenar, consultar e manipular dados utilizando esses tipos.
O laboratório começa demonstrando como armazenar e consultar dados JSON e JSONB, incluindo a criação de uma tabela com uma coluna JSONB, a inserção de dados JSON e o uso de operadores como -> e ->> para extrair valores específicos. Em seguida, você aprenderá sobre colunas de array e UUIDs.
Armazenar e Consultar Dados JSON e JSONB
Nesta etapa, você aprenderá a armazenar e consultar dados JSON e JSONB no PostgreSQL. O PostgreSQL oferece dois tipos de dados para armazenar JSON: JSON e JSONB. O tipo de dado JSON armazena uma cópia exata do texto JSON de entrada, enquanto o tipo JSONB armazena os dados JSON em um formato binário decomposto. O JSONB é geralmente preferido por oferecer melhor desempenho para consultas e indexação.
Vamos começar abrindo o shell do PostgreSQL. Primeiro, conecte-se ao banco de dados labex:
sudo -u postgres psql -d labex
Você deverá ver o prompt do PostgreSQL:
labex=#
Agora, vamos criar uma tabela para armazenar dados JSONB:
CREATE TABLE products (id SERIAL PRIMARY KEY, data JSONB);
Este comando SQL cria uma tabela chamada products. A tabela possui duas colunas: id (uma chave primária inteira com incremento automático) e data (uma coluna JSONB para armazenar dados JSON).
Você deverá ver uma saída semelhante a esta:
CREATE TABLE
Agora, vamos inserir alguns dados na tabela 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"]}');
Esses comandos inserem duas linhas na tabela products. Cada linha contém um objeto JSON com informações sobre um produto.
Você deverá ver uma saída semelhante a esta para cada inserção:
INSERT 0 1
Para consultar os dados JSON, você pode usar os operadores -> e ->>. O operador -> retorna um objeto JSON, enquanto o operador ->> retorna um valor JSON como texto.
Por exemplo, para recuperar o nome do primeiro produto, você pode usar a seguinte consulta:
SELECT data ->> 'name' FROM products WHERE id = 1;
Este comando seleciona o valor associado à chave name da coluna data da tabela products, onde o id é 1. O operador ->> garante que o resultado seja retornado como texto.
Você deverá ver uma saída semelhante a esta:
?column?
----------
Laptop
(1 row)
Você também pode consultar objetos JSON aninhados. Por exemplo, para recuperar o primeiro recurso do primeiro produto, você pode usar a seguinte consulta:
SELECT data -> 'features' ->> 0 FROM products WHERE id = 1;
Este comando primeiro seleciona o array features da coluna data e, em seguida, seleciona o elemento no índice 0 do array. O operador ->> garante que o resultado seja retornado como texto.
Você deverá ver uma saída semelhante a esta:
?column?
----------
16GB RAM
(1 row)
Você também pode usar o operador @> para verificar se um objeto JSON contém um par chave-valor específico. Por exemplo, para encontrar todos os produtos com preço de 75, você pode usar a seguinte consulta:
SELECT data ->> 'name' FROM products WHERE data @> '{"price": 75}';
Este comando seleciona o nome de todas as linhas da tabela products onde a coluna data contém um objeto JSON com uma chave price e um valor de 75.
Você deverá ver uma saída semelhante a esta:
?column?
----------
Keyboard
(1 row)
Ótimo! Criamos com sucesso nossa primeira tabela com dados JSONB e aprendemos como consultá-la. Continuaremos trabalhando com esta tabela na próxima etapa para adicionar recursos mais avançados.
Adicionar Colunas de Array a uma Tabela Existente
Nesta etapa, você aprenderá como adicionar e manipular colunas de array no PostgreSQL. Vamos estender nossa tabela products existente adicionando uma coluna de array para armazenar tags. Colunas de array permitem armazenar múltiplos valores do mesmo tipo de dado em uma única coluna, o que é útil para armazenar listas de itens como tags, categorias ou recursos.
Como já estamos conectados ao banco de dados e temos nossa tabela products da etapa anterior, vamos adicionar colunas de array à nossa tabela existente:
ALTER TABLE products ADD COLUMN name VARCHAR(255);
ALTER TABLE products ADD COLUMN tags TEXT[];
Esses comandos adicionam duas novas colunas à nossa tabela products existente: name (uma string) e tags (um array de strings). O tipo de dado TEXT[] especifica que a coluna tags é um array de valores de texto.
Você deverá ver uma saída semelhante a esta para cada comando de alteração:
ALTER TABLE
Agora, vamos atualizar nossos dados existentes e inserir novos dados com as novas colunas:
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;
Esses comandos atualizam nossos produtos existentes com informações de nome e tags extraídas dos dados JSONB e novos valores de array.
Você deverá ver uma saída semelhante a esta para cada atualização:
UPDATE 1
Para consultar os dados do array, você pode usar a indexação de array. Os índices de array no PostgreSQL começam em 1.
Por exemplo, para recuperar a primeira tag do primeiro produto, você pode usar a seguinte consulta:
SELECT tags[1] FROM products WHERE id = 1;
Este comando seleciona o elemento no índice 1 do array tags da tabela products, onde o id é 1.
Você deverá ver uma saída semelhante a esta:
tags
-----------
electronics
(1 row)
Você também pode usar a função UNNEST para expandir um array em um conjunto de linhas.
Por exemplo, para recuperar todas as tags de todos os produtos, você pode usar a seguinte consulta:
SELECT name, UNNEST(tags) AS tag FROM products;
Este comando seleciona o name e cada tag individual do array tags, criando uma nova linha para cada tag.
Você deverá ver uma saída semelhante a esta:
name | tag
---------+-------------
Laptop | electronics
Laptop | computers
Laptop | portable
Keyboard| electronics
Keyboard| accessories
Keyboard| input
(6 rows)
Você pode usar o operador @> para verificar se um array contém um valor específico.
Por exemplo, para encontrar todos os produtos com a tag 'electronics', você pode usar a seguinte consulta:
SELECT name FROM products WHERE tags @> ARRAY['electronics'];
Este comando seleciona o nome de todas as linhas da tabela products onde o array tags contém o valor 'electronics'.
Você deverá ver uma saída semelhante a esta:
name
----------
Laptop
Keyboard
(2 rows)
Você também pode usar o operador && para verificar se dois arrays possuem elementos em comum.
Por exemplo, para encontrar todos os produtos que compartilham alguma tag com o primeiro produto, você pode usar a seguinte consulta:
SELECT p2.name FROM products p1, products p2 WHERE p1.id = 1 AND p1.tags && p2.tags AND p2.id != 1;
Este comando seleciona o nome de todas as linhas da tabela products (com alias p2) que possuem pelo menos uma tag em comum com as tags do primeiro produto (com alias p1), excluindo o próprio primeiro produto.
Você deverá ver uma saída semelhante a esta:
name
----------
Keyboard
(1 row)
Perfeito! Adicionamos com sucesso colunas de array à nossa tabela existente e aprendemos como trabalhar com arrays. Nossa tabela products agora contém tipos de dados JSONB e array, tornando-a pronta para a próxima etapa.
Adicionar Coluna UUID e Aprender a Gerar UUIDs
Nesta etapa, você aprenderá como gerar e usar UUIDs (Universally Unique Identifiers) no PostgreSQL. UUIDs são números de 128 bits projetados para serem únicos no espaço e no tempo. Eles são frequentemente usados como identificadores únicos em tabelas de banco de dados para evitar conflitos ao mesclar dados de diferentes fontes.
Como estamos continuando com nossa sessão e tabela existentes, adicionaremos uma coluna UUID à nossa tabela products atual para demonstrar a funcionalidade de UUID.
Primeiro, vamos habilitar a extensão UUID que fornece funções de geração de UUID:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
Este comando cria a extensão uuid-ossp se ela ainda não existir.
Você deverá ver uma saída semelhante a esta:
CREATE EXTENSION
Agora, vamos adicionar uma coluna UUID à nossa tabela products existente:
ALTER TABLE products ADD COLUMN uuid_id UUID;
Este comando adiciona uma nova coluna UUID à nossa tabela products existente.
Você deverá ver uma saída semelhante a esta:
ALTER TABLE
Agora podemos atualizar nossas linhas existentes com valores UUID usando a função 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;
Esses comandos atualizam nossos produtos existentes com valores UUID únicos. A função uuid_generate_v4() gera um novo UUID para cada linha.
Você deverá ver uma saída semelhante a esta para cada atualização:
UPDATE 1
Para consultar os dados usando UUIDs, você pode usar os valores UUID em suas cláusulas WHERE. Como os UUIDs são gerados aleatoriamente, vamos primeiro visualizar nossos dados atuais:
SELECT id, name, uuid_id FROM products;
Este comando seleciona todas as colunas da tabela products para ver os UUIDs gerados junto com nossos outros dados.
Você deverá ver uma saída semelhante a esta (seu UUID será diferente):
id | name | uuid_id
----+----------+--------------------------------------
1 | Laptop | 8f14e45f-ea7b-4f9f-a2b0-73f9c3f85a9b
2 | Keyboard | c9f0f895-fb98-4635-bd31-4f7f4d8f9e7a
(2 rows)
Agora, use esse UUID no comando a seguir, substituindo <YOUR_UUID_HERE> pelo UUID real que você recuperou:
SELECT name FROM products WHERE uuid_id = '<YOUR_UUID_HERE>';
Este comando seleciona o name da tabela products onde o uuid_id corresponde ao UUID especificado.
Você deverá ver uma saída semelhante a esta (dependendo de qual UUID você escolheu):
name
----------
Laptop
(1 row)
Mantenha a tabela products e a extensão uuid-ossp ativas, pois a próxima etapa continuará a utilizá-las.
Extrair Dados de Tipos Avançados
Nesta etapa final, você praticará a extração de dados de todos os tipos de dados avançados que adicionamos à nossa tabela products ao longo deste laboratório. Nossa tabela agora contém colunas JSONB, array e UUID, fornecendo um exemplo abrangente dos tipos de dados avançados do PostgreSQL.
Como estamos continuando com nossa sessão e tabela existentes, podemos começar imediatamente a trabalhar com os dados que construímos nas etapas anteriores.
Primeiro, vamos adicionar alguns dados JSONB adicionais para tornar nossos exemplos de extração mais completos:
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;
Esses comandos atualizam nossos dados JSONB existentes com informações mais detalhadas, incluindo objetos aninhados e campos adicionais.
Você deverá ver uma saída semelhante a esta para cada atualização:
UPDATE 1
Agora vamos praticar a extração de dados de todos os nossos tipos de dados avançados. Para extrair dados da coluna JSONB data, você pode usar os operadores -> e ->>. Por exemplo, para extrair a marca dos nossos dados atualizados:
SELECT data ->> 'brand' FROM products WHERE id = 1;
Este comando recupera o valor associado à chave brand da coluna data.
Você deverá ver uma saída semelhante a esta:
?column?
----------
Dell
(1 row)
Para extrair dados aninhados da coluna JSONB, você pode encadear os operadores -> e ->>. Por exemplo, para extrair a especificação de RAM:
SELECT data -> 'specs' ->> 'ram' FROM products WHERE id = 1;
Este comando recupera o valor associado à chave ram dentro do objeto specs.
Você deverá ver uma saída semelhante a esta:
?column?
----------
16GB
(1 row)
Para extrair dados da coluna tags (array), você pode usar a indexação de array, conforme aprendido na Etapa 2. Por exemplo, para extrair a primeira tag do primeiro produto:
SELECT tags[1] FROM products WHERE id = 1;
Este comando recupera o elemento no índice 1 do array tags da tabela products onde o id é 1.
Você deverá ver uma saída semelhante a esta:
tags
-----------
electronics
(1 row)
Agora vamos criar uma consulta abrangente que extrai dados de todos os nossos tipos de dados avançados - JSONB, arrays e UUIDs:
SELECT
id,
name,
data ->> 'brand' AS brand,
data -> 'specs' ->> 'ram' AS ram,
tags[1] AS first_tag,
uuid_id
FROM products;
Este comando recupera dados de todos os tipos de dados avançados com os quais trabalhamos: o id inteiro, o name, a brand da coluna JSONB data, a especificação ram aninhada, o primeiro elemento do array tags e o uuid_id.
Você deverá ver uma saída semelhante a esta:
name | ?column? | tags
----------+------------+-----------
Laptop | Dell | electronics
Keyboard | Logitech | electronics
(2 rows)
Excelente! Você trabalhou com sucesso com todos os três tipos de dados avançados do PostgreSQL em uma única tabela. Este exemplo abrangente demonstra como JSONB, arrays e UUIDs podem ser usados juntos para criar esquemas de banco de dados flexíveis e poderosos.
Quando terminar todas as etapas, você pode sair do shell do PostgreSQL digitando:
\q
Você também pode optar por manter a tabela para experimentações adicionais com os tipos de dados avançados do PostgreSQL.
Resumo
Neste laboratório, você construiu e trabalhou progressivamente com uma tabela products abrangente que demonstra os tipos de dados avançados do PostgreSQL em ação. Você começou criando uma tabela com colunas JSONB e aprendeu a armazenar e consultar dados JSON usando operadores como -> e ->>.
Em seguida, você estendeu a tabela adicionando colunas de array, aprendendo como armazenar múltiplos valores em uma única coluna e consultá-los usando indexação de array e funções como UNNEST. Depois, você adicionou a funcionalidade de UUID habilitando a extensão uuid-ossp e adicionando uma coluna UUID para gerar identificadores únicos.
Por fim, você praticou técnicas abrangentes de extração de dados que combinaram todos os três tipos de dados avançados - JSONB, arrays e UUIDs - em consultas sofisticadas. Essa abordagem passo a passo demonstrou como esses tipos de dados podem trabalhar juntos em esquemas de banco de dados do mundo real, proporcionando flexibilidade e recursos de consulta poderosos para aplicações modernas.
