Operações Avançadas de JSON/JSONB no PostgreSQL

Introdução

Neste laboratório, você explorará operações avançadas utilizando o tipo de dado JSONB do PostgreSQL. Você aprenderá a armazenar, gerenciar e consultar dados JSON de forma eficaz dentro de um banco de dados relacional.

O laboratório irá guiá-lo através da criação de uma tabela com uma coluna JSONB, inserção de dados e, em seguida, a aplicação de um índice GIN para melhorar o desempenho da pesquisa. Você também praticará a consulta de estruturas JSON aninhadas, a atualização de elementos específicos dentro de um documento JSONB e a realização de cálculos agregados em seus dados JSON.

Criar uma Tabela e Indexar Campos JSONB

Nesta etapa, você criará uma tabela para armazenar informações de produtos utilizando o tipo de dado JSONB e, em seguida, criará um índice GIN para otimizar as consultas nesses dados. JSONB armazena dados JSON em um formato binário decomposto, que é ligeiramente mais lento para entrada, mas muito mais rápido para processar. Um GIN (Generalized Inverted Index) é ideal para indexar valores compostos como os de uma coluna JSONB.

Primeiro, abra um terminal e conecte-se ao banco de dados PostgreSQL usando o shell interativo psql:

sudo -u postgres psql

Agora você verá o prompt do PostgreSQL, que se parece com postgres=#.

Em seguida, crie uma tabela chamada products com uma coluna id e uma coluna data do tipo JSONB.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    data JSONB
);

Agora, insira alguns dados de exemplo de produtos na tabela 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"]}');

Para acelerar significativamente as buscas na coluna data, crie um índice GIN nela.

CREATE INDEX idx_products_data ON products USING GIN (data);

Você pode verificar se o índice foi criado com sucesso usando o comando \di, que lista todos os índices.

\di

Você deverá ver idx_products_data na lista de relações, semelhante à saída abaixo:

                                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)

Você configurou com sucesso sua tabela e indexou a coluna JSONB. Você permanecerá no shell psql para a próxima etapa.

Consultar Estruturas JSON Aninhadas

Nesta etapa, você aprenderá a consultar dados dentro de colunas JSONB, incluindo estruturas aninhadas. O PostgreSQL fornece vários operadores para esse fim.

Primeiro, vamos inserir alguns produtos com dados mais complexos e aninhados na tabela 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"}}');

Para acessar uma chave de nível superior, você pode usar o operador ->>, que retorna o valor como texto. Vamos encontrar o preço do "Laptop".

SELECT data ->> 'price' FROM products WHERE data ->> 'name' = 'Laptop';

A saída será:

 price
-------
 1200
(1 row)

Para acessar uma chave dentro de um objeto JSON aninhado, você pode encadear os operadores -> e ->>. O operador -> obtém um campo de objeto JSON, enquanto ->> o obtém como texto. Vamos recuperar a especificação da CPU para o "Gaming PC".

SELECT data -> 'specs' ->> 'cpu' FROM products WHERE data ->> 'name' = 'Gaming PC';

Este comando retornará o tipo de CPU:

   ?column?
--------------
 Intel i7
(1 row)

Você também pode usar esses operadores na cláusula WHERE para filtrar resultados com base em valores aninhados. Por exemplo, encontre todos os produtos que possuem uma CPU "Intel i5".

SELECT data ->> 'name' FROM products WHERE data -> 'specs' ->> 'cpu' = 'Intel i5';

A consulta retornará o nome do produto correspondente:

   name
-----------
 Office PC
(1 row)

Agora você é capaz de consultar dados de nível superior e aninhados dentro de suas colunas JSONB.

Atualizar Elementos Específicos de JSONB

Nesta etapa, você aprenderá a modificar elementos específicos dentro de uma coluna JSONB usando a função jsonb_set. Isso é mais eficiente do que recuperar todo o objeto JSON, modificá-lo em sua aplicação e gravá-lo de volta.

A função jsonb_set tem a seguinte sintaxe: jsonb_set(target_jsonb, path_array, new_value_jsonb, create_if_missing).

Vamos atualizar o preço do "Laptop" de 1200 para 1250. O caminho para o preço é '{price}', e o novo valor deve ser convertido para JSONB.

UPDATE products
SET data = jsonb_set(data, '{price}', '1250'::jsonb)
WHERE data ->> 'name' = 'Laptop';

Verifique a atualização selecionando os dados do laptop.

SELECT data FROM products WHERE data ->> 'name' = 'Laptop';

A saída deve mostrar o novo preço:

                                   data
--------------------------------------------------------------------------
 {"name": "Laptop", "price": 1250, "tags": ["electronics", "computer"]}
(1 row)

Você também pode atualizar valores aninhados. Vamos atualizar a RAM do "Office PC" para "32GB". O caminho para a chave aninhada ram é '{specs,ram}'.

UPDATE products
SET data = jsonb_set(data, '{specs,ram}', '"32GB"'::jsonb)
WHERE data ->> 'name' = 'Office PC';

Agora, verifique esta atualização aninhada.

SELECT data -> 'specs' FROM products WHERE data ->> 'name' = 'Office PC';

O resultado confirmará a alteração no valor da ram:

                          ?column?
------------------------------------------------------------
 {"cpu": "Intel i5", "ram": "32GB", "storage": "500GB SSD"}
(1 row)

Você agora aprendeu como realizar atualizações direcionadas em dados JSONB.

Agregar Dados JSON

Nesta etapa final, você realizará cálculos de agregação em dados extraídos de colunas JSONB. Isso é útil para relatórios e análises.

Para realizar cálculos, você geralmente precisa extrair um valor e convertê-lo para um tipo numérico. Vamos calcular o preço médio de todos os produtos.

SELECT AVG((data ->> 'price')::numeric) FROM products;

A consulta extrai o price como texto, converte-o para numeric e, em seguida, calcula a média. A saída será um único número:

          avg
------------------------
 659.1666666666666667
(1 row)

Você também pode usar funções de agregação com uma cláusula GROUP BY. Vamos encontrar o número total de produtos para cada tag. Para fazer isso, primeiro precisamos desanexar o array tags em linhas separadas usando jsonb_array_elements_text.

SELECT tag, COUNT(*)
FROM products, jsonb_array_elements_text(data -> 'tags') AS tag
GROUP BY tag;

Esta consulta produz uma contagem para cada tag única:

     tag     | count
-------------+-------
 accessory   |     2
 electronics |     4
 computer    |     1
 display     |     1
(4 rows)

Finalmente, vamos encontrar o valor total de todos os produtos que possuem a tag "electronics".

SELECT SUM((data ->> 'price')::numeric)
FROM products
WHERE data -> 'tags' @> '"electronics"'::jsonb;

O operador @> verifica se o valor JSONB à esquerda contém o valor JSONB à direita. Aqui, verificamos se o array tags contém o elemento "electronics".

O resultado é a soma dos preços dos quatro produtos eletrônicos:

  sum
--------
 1655
(1 row)

Você agora aprendeu como usar funções de agregação em seus dados JSONB. Para sair do shell psql, digite:

\q

Resumo

Neste laboratório, você aprendeu várias operações avançadas para manipulação de dados JSONB no PostgreSQL. Você começou criando uma tabela com uma coluna JSONB e viu a importância de usar um índice GIN para otimizar o desempenho das consultas. Em seguida, praticou a consulta de dados JSONB de nível superior e aninhados usando os operadores -> e ->>. Além disso, você aprendeu a realizar modificações direcionadas em documentos JSONB com a função jsonb_set e como realizar análises de dados poderosas usando funções de agregação como AVG, COUNT e SUM em seus dados JSON.