Consultando JSONB: operadores e índices GIN

1. Introdução ao JSONB no PostgreSQL

JSONB é um tipo de dado binário do PostgreSQL para armazenamento de documentos JSON. Diferentemente do tipo JSON, que armazena o texto exato da entrada (incluindo espaços e duplicatas de chaves), o JSONB converte os dados em um formato binário interno, removendo espaços desnecessários, ordenando chaves e eliminando chaves duplicadas (mantendo apenas a última ocorrência).

As principais vantagens do JSONB incluem:
- Indexação eficiente: suporte nativo a índices GIN
- Operadores de consulta poderosos: containment, existência e path expressions
- Performance superior: parsing mais rápido em consultas repetidas

Casos de uso comuns incluem armazenamento de configurações dinâmicas, logs flexíveis com estrutura variável, metadados de aplicações e dados semi-estruturados onde o schema pode evoluir rapidamente.

2. Operadores Básicos de Consulta JSONB

Os operadores fundamentais para acessar campos dentro de documentos JSONB são -> e ->>. O operador -> retorna o valor como JSONB, enquanto ->> retorna como texto.

-- Criando uma tabela de exemplo
CREATE TABLE usuarios (
    id SERIAL PRIMARY KEY,
    dados JSONB
);

INSERT INTO usuarios (dados) VALUES
('{"nome": "Ana Silva", "idade": 30, "endereco": {"cidade": "São Paulo", "uf": "SP"}}'),
('{"nome": "Carlos Lima", "idade": 25, "endereco": {"cidade": "Rio de Janeiro", "uf": "RJ"}}');

-- Acessando campos como JSONB e como texto
SELECT 
    dados->'nome' AS nome_jsonb,
    dados->>'nome' AS nome_texto,
    dados->'endereco' AS endereco_jsonb,
    dados->'endereco'->>'cidade' AS cidade
FROM usuarios;

Para navegar em estruturas aninhadas, usamos #> e #>>, que aceitam um array de caminhos (path expressions):

-- Usando path expressions
SELECT 
    dados#>'{endereco, cidade}' AS cidade_jsonb,
    dados#>>'{endereco, uf}' AS uf_texto
FROM usuarios;

3. Operadores de Containment e Existência

O operador @> verifica se um documento JSONB contém outro. É extremamente útil para filtrar documentos com estruturas específicas:

-- Encontrar usuários com status "ativo"
SELECT * FROM usuarios WHERE dados @> '{"status": "ativo"}';

-- Verificar se contém múltiplos campos
SELECT * FROM usuarios WHERE dados @> '{"endereco": {"uf": "SP"}}';

Os operadores ?, ?| e ?& verificam existência de chaves no nível raiz do documento:

-- Verificar se uma chave específica existe
SELECT * FROM usuarios WHERE dados ? 'email';

-- Verificar se alguma das chaves existe (OR lógico)
SELECT * FROM usuarios WHERE dados ?| ARRAY['email', 'telefone', 'celular'];

-- Verificar se todas as chaves existem (AND lógico)
SELECT * FROM usuarios WHERE dados ?& ARRAY['nome', 'endereco'];

4. Operadores Avançados e Funções Utilitárias

O operador || permite concatenar/mergear documentos JSONB. Para chaves duplicadas, o valor da direita sobrescreve:

-- Adicionando ou sobrescrevendo campos
UPDATE usuarios 
SET dados = dados || '{"telefone": "11999999999", "status": "ativo"}'
WHERE id = 1;

Funções para iteração e expansão de arrays:

-- Listar todas as chaves de um documento
SELECT id, jsonb_object_keys(dados) AS chave FROM usuarios;

-- Expandir arrays aninhados
CREATE TABLE produtos (
    id SERIAL PRIMARY KEY,
    info JSONB
);

INSERT INTO produtos (info) VALUES
('{"nome": "Smartphone", "cores": ["preto", "branco", "azul"], "precos": [1500, 1800, 2000]}');

SELECT 
    info->>'nome' AS produto,
    jsonb_array_elements_text(info->'cores') AS cor,
    jsonb_array_elements_text(info->'precos')::numeric AS preco
FROM produtos;

Remoção de chaves com operador - e atualização com jsonb_set():

-- Removendo uma chave
UPDATE usuarios SET dados = dados - 'telefone' WHERE id = 1;

-- Atualizando um campo aninhado
UPDATE usuarios 
SET dados = jsonb_set(dados, '{endereco, cidade}', '"Campinas"')
WHERE id = 1;

5. Índices GIN para JSONB: Conceitos e Criação

Índices GIN (Generalized Inverted Index) são projetados para lidar com tipos de dados compostos como JSONB, arrays e texto completo. Internamente, um índice GIN decompõe o documento JSONB em pares chave-valor e mapeia esses componentes para as linhas correspondentes.

Para criar um índice GIN padrão:

CREATE INDEX idx_gin_usuarios ON usuarios USING GIN (dados);

Para otimizar consultas com operador @> e path queries, podemos usar a classe de operadores jsonb_path_ops:

CREATE INDEX idx_gin_path ON usuarios USING GIN (dados jsonb_path_ops);

A diferença principal: o índice padrão armazena cada chave e valor individualmente, enquanto jsonb_path_ops armazena caminhos completos (ex: nome.Ana Silva), tornando consultas com @> mais rápidas, porém consumindo mais espaço.

6. Otimização de Consultas com Índices GIN

O índice GIN é utilizado automaticamente pelo PostgreSQL para os operadores @>, ?, ?| e ?&. Vamos verificar com EXPLAIN ANALYZE:

-- Inserindo dados de exemplo
INSERT INTO usuarios (dados)
SELECT jsonb_build_object(
    'nome', 'Usuário ' || g,
    'email', 'user' || g || '@email.com',
    'status', CASE WHEN g % 2 = 0 THEN 'ativo' ELSE 'inativo' END,
    'tags', ARRAY['tag1', 'tag2', 'tag3']
)
FROM generate_series(1, 100000) g;

-- Consulta sem índice (desabilitando o índice para teste)
SET enable_seqscan = ON;
EXPLAIN ANALYZE SELECT * FROM usuarios WHERE dados @> '{"status": "ativo"}';

-- Consulta com índice GIN
SET enable_seqscan = OFF;
EXPLAIN ANALYZE SELECT * FROM usuarios WHERE dados @> '{"status": "ativo"}';

Limitações importantes: operadores -> e ->> não se beneficiam diretamente do índice GIN. Para consultas frequentes em campos específicos, considere:

-- Criar um índice B-tree em um campo extraído
CREATE INDEX idx_nome ON usuarios ((dados->>'nome'));
-- Agora consultas como WHERE dados->>'nome' = 'Ana Silva' usarão este índice

7. Boas Práticas e Considerações Finais

Quando usar JSONB vs colunas normalizadas:
- Use JSONB para dados com schema variável ou que mudam frequentemente
- Prefira colunas normalizadas para dados consultados com alta frequência e que exigem integridade referencial
- Evite JSONB para campos que precisam de joins frequentes

Impacto em operações de escrita:
Índices GIN aumentam o tempo de INSERT/UPDATE, pois cada alteração no documento requer atualização do índice. Em tabelas com alta taxa de escrita, monitore o desempenho.

Manutenção do índice:

-- Reconstruir índice para eliminar bloat
REINDEX INDEX idx_gin_usuarios;

-- Monitorar tamanho do índice
SELECT pg_size_pretty(pg_indexes_size('usuarios'));

Exemplo completo: catálogo de produtos com JSONB e índice GIN

CREATE TABLE catalogo_produtos (
    id SERIAL PRIMARY KEY,
    produto JSONB NOT NULL
);

CREATE INDEX idx_gin_catalogo ON catalogo_produtos USING GIN (produto jsonb_path_ops);

INSERT INTO catalogo_produtos (produto) VALUES
('{"nome": "Notebook Pro", "categoria": "eletrônicos", "preco": 4500, "especificacoes": {"ram": "16GB", "armazenamento": "512GB SSD"}, "tags": ["promocao", "destaque"]}'),
('{"nome": "Smartphone X", "categoria": "eletrônicos", "preco": 2500, "especificacoes": {"ram": "8GB", "armazenamento": "256GB"}, "tags": ["lancamento"]}');

-- Consultas eficientes com o índice GIN
SELECT * FROM catalogo_produtos 
WHERE produto @> '{"categoria": "eletrônicos", "preco": 2500}';

SELECT * FROM catalogo_produtos 
WHERE produto ?| ARRAY['destaque', 'lancamento'];

Referências