Aula 18: JSON e JSONB — PostgreSQL como banco híbrido relacional/NoSQL

Bem-vindo à Aula 18 do nosso curso PostgreSQL — Do Zero ao Avançado. Hoje vamos mergulhar em um dos recursos mais poderosos e versáteis do PostgreSQL: o suporte nativo a dados semiestruturados através dos tipos JSON e JSONB. Se você acompanhou as aulas anteriores, já domina modelagem relacional, índices, transações e otimização de consultas. Agora é o momento de expandir esse conhecimento para cenários onde a rigidez do esquema relacional tradicional não é a melhor solução — e é exatamente aí que os tipos JSON entram em cena, transformando o PostgreSQL em um verdadeiro banco de dados híbrido, capaz de operar simultaneamente como relacional e NoSQL.

Por que JSON e JSONB são tão relevantes no mercado atual? A resposta está na natureza das aplicações modernas. Microsserviços trocam dados em JSON, APIs RESTful consomem e produzem JSON, dispositivos IoT enviam telemetria em formato JSON, e sistemas de log estruturado armazenam eventos como documentos JSON. Ter a capacidade de armazenar, consultar e indexar esses documentos diretamente no banco de dados, sem perder as garantias ACID e o poderoso planejador de consultas do PostgreSQL, é uma vantagem competitiva enorme. Em nossos projetos na JRT Technology Solutions, utilizamos diariamente os tipos JSON e JSONB para construir pipelines de dados flexíveis, onde parte da informação segue um esquema rígido e outra parte é armazenada como documento semiestruturado — tudo no mesmo banco, na mesma transação.

Ao final desta aula, você será capaz de criar tabelas com colunas do tipo JSON e JSONB, inserir documentos, extrair valores aninhados, modificar campos específicos dentro de um documento sem reescrevê-lo completamente, criar índices GIN para aceleração de consultas e, principalmente, decidir com segurança quando usar JSON, JSONB ou uma tabela relacional normalizada. Vamos abordar operadores como ->, ->>, #>, #>>, funções como jsonb_set(), jsonb_path_query() e jsonb_each(), além de recursos avançados como SQL/JSON Path Expressions introduzidos no PostgreSQL 12 e aprimorados até a versão 16.

Prepare seu terminal e seu editor SQL favorito — seja o psql, DBeaver, pgAdmin ou DataGrip. Todos os exemplos desta aula foram testados no PostgreSQL 16, mas são compatíveis a partir da versão 12. Se você está usando uma versão anterior, recomendo fortemente um upgrade, pois as funcionalidades de path expressions e a performance do JSONB evoluíram significativamente. Vamos construir conhecimento sólido, passo a passo, sem pressa e sem pular etapas.

O que você vai aprender nesta aula

• Diferenças fundamentais entre JSON (armazenamento textual) e JSONB (armazenamento binário otimizado)
• Quando utilizar JSON/JSONB versus tabelas relacionais normalizadas — critérios de decisão prática
• Criação de tabelas com colunas JSON e JSONB e inserção de documentos
• Operadores de navegação e extração: ->, ->>, #>, #>>, @>, <@, ?, ?|, ?&
• Funções essenciais: jsonb_set(), jsonb_insert(), jsonb_strip_nulls(), jsonb_build_object(), jsonb_agg()
• Indexação com GIN e GIN jsonb_path_ops — quando usar cada uma
• Consultas avançadas com SQL/JSON Path Expressions (jsonb_path_query)
• Conversão entre dados relacionais e JSON — row_to_json(), json_agg() e to_jsonb()
• Modificação atômica de campos em documentos JSONB sem reescrita completa
• Resolução de erros comuns e armadilhas ao trabalhar com dados semiestruturados

Pré-requisitos e Ambiente

Para acompanhar esta aula com aproveitamento máximo, você precisa de um servidor PostgreSQL 12 ou superior em funcionamento. Se você ainda não tem o PostgreSQL instalado, revise a Aula 2 — Instalação e Configuração Inicial do nosso curso, onde cobrimos o procedimento completo para Ubuntu/Debian, CentOS/RHEL/Rocky Linux e Windows. Você também deve estar confortável com SQL básico (SELECT, INSERT, UPDATE, DELETE), criação de tabelas e uso do psql ou interface gráfica equivalente.

Vamos utilizar um banco de dados chamado curso_json que criaremos no início da parte prática. Todos os comandos serão executados como usuário com privilégios de criação de banco e tabelas — tipicamente o usuário postgres ou um usuário administrativo configurado por você. Verifique se você consegue se conectar ao seu servidor antes de prosseguir:

# Conectando como usuário postgres (ajuste conforme seu ambiente)
psql -U postgres -h localhost

psql (16.3 (Ubuntu 16.3-1.pgdg22.04+1))
Digite "help" para ajuda.

postgres=#

Se você obteve o prompt postgres=#, está pronto para começar. Vamos criar o banco de dados de trabalho:

-- Criando o banco de dados para esta aula
CREATE DATABASE curso_json;
\c curso_json

CREATE DATABASE
Você agora está conectado ao banco de dados "curso_json" como usuário "postgres".
curso_json=#

JSON vs JSONB — Entendendo a Diferença que Define sua Aplicação

O PostgreSQL oferece dois tipos de dados para armazenar JSON: json e jsonb. Embora pareçam similares à primeira vista, eles são fundamentalmente diferentes em implementação, performance e casos de uso. O tipo json armazena uma cópia exata do texto de entrada, preservando espaços em branco, ordem das chaves e até mesmo chaves duplicadas. Internamente, é apenas um text com validação sintática — o PostgreSQL verifica se o conteúdo é um JSON válido no momento da inserção, mas não o processa além disso. Já o tipo jsonb (JSON Binary) faz o parsing completo do documento, decompondo-o em uma estrutura binária otimizada que elimina espaços, remove chaves duplicadas (mantendo apenas o último valor) e não preserva a ordem original das chaves. Esse pré-processamento tem um custo na escrita, mas oferece ganhos massivos na leitura e consulta.

Na prática, e em nossa experiência diária na JRT Technology Solutions, recomendamos jsonb para 95% dos casos de uso. A única situação em que json se justifica é quando você precisa garantir a preservação exata do documento original (ordem de chaves, formatação, espaços) para fins de auditoria, assinatura digital ou conformidade regulatória onde o hash do documento original é relevante. Para tudo mais — APIs, logs, configurações, catálogos de produtos com atributos variáveis — o jsonb é superior em todos os aspectos: permite indexação, oferece dezenas de operadores e funções, e é significativamente mais rápido em consultas que acessam campos internos do documento. A tabela a seguir resume as diferenças principais:

Criando Tabelas com JSON e JSONB — Estrutura e Primeiros Documentos

Vamos partir para a prática. Nosso cenário de exemplo será um sistema de catálogo de produtos de tecnologia. Alguns produtos têm atributos padronizados (nome, preço, SKU), enquanto outros possuem características variáveis — um notebook tem tela, processador e memória RAM; um monitor tem resolução e taxa de atualização; um cabo USB tem comprimento e tipo de conector. Usar tabelas normalizadas para todos esses atributos seria um pesadelo de manutenção com dezenas de tabelas auxiliares. A abordagem híbrida resolve isso elegantemente: mantemos os campos comuns em colunas relacionais e jogamos os atributos específicos em uma coluna jsonb.

Execute os comandos abaixo no seu terminal psql conectado ao banco curso_json:

-- Criando a tabela de produtos com campos relacionais + coluna jsonb
CREATE TABLE produtos (
    id          SERIAL PRIMARY KEY,
    sku         VARCHAR(50) UNIQUE NOT NULL,
    nome        VARCHAR(200) NOT NULL,
    preco       NUMERIC(10,2) NOT NULL CHECK (preco >= 0),
    categoria   VARCHAR(100) NOT NULL,
    atributos   JSONB NOT NULL DEFAULT '{}',
    criado_em   TIMESTAMPTZ DEFAULT NOW(),
    atualizado_em TIMESTAMPTZ DEFAULT NOW()
);

-- Comentário descritivo na tabela e colunas (boa prática de documentação)
COMMENT ON TABLE produtos IS 'Catálogo de produtos com atributos flexíveis em JSONB';
COMMENT ON COLUMN produtos.atributos IS 'Documento JSONB com atributos específicos do produto';

-- Verificando a estrutura criada
\d produtos

                                              Tabela "public.produtos"
     Coluna     |            Tipo            | Ordenação | Pode ser nulo |                        Padrão
----------------+----------------------------+-----------+---------------+--------------------------------------------------------
 id             | integer                    |           | not null      | nextval('produtos_id_seq'::regclass)
 sku            | character varying(50)      |           | not null      |
 nome           | character varying(200)     |           | not null      |
 preco          | numeric(10,2)              |           | not null      |
 categoria      | character varying(100)     |           | not null      |
 atributos      | jsonb                      |           | not null      | '{}'::jsonb
 criado_em      | timestamp with time zone   |           |               | now()
 atualizado_em  | timestamp with time zone   |           |               | now()
Índices:
    "produtos_pkey" PRIMARY KEY, btree (id)
    "produtos_sku_key" UNIQUE CONSTRAINT, btree (sku)
Restrições de check:
    "produtos_preco_check" CHECK (preco >= 0::numeric)

Excelente. Nossa tabela está pronta. Observe que definimos um valor padrão ‘{}’::jsonb para a coluna atributos — um objeto JSON vazio — e marcamos a coluna como NOT NULL. Essa é uma prática recomendada que evita surpresas com valores nulos e facilita consultas com operadores JSON que esperam um documento válido. Agora vamos inserir alguns produtos com diferentes conjuntos de atributos:

-- Inserindo um notebook com atributos específicos
INSERT INTO produtos (sku, nome, preco, categoria, atributos)
VALUES (
    'NTB-001',
    'ThinkPad X1 Carbon Gen 11',
    12499.90,
    'Notebooks',
    '{
        "tela": {
            "tamanho": "14 polegadas",
            "resolucao": "1920x1200",
            "tecnologia": "IPS"
        },
        "processador": {
            "modelo": "Intel Core i7-1365U",
            "nucleos": 10,
            "threads": 12
        },
        "memoria": {
            "ram": "16GB LPDDR5",
            "armazenamento": "512GB SSD NVMe"
        },
        "peso_kg": 1.12,
        "conectividade": ["WiFi 6E", "Bluetooth 5.2", "Thunderbolt 4"]
    }'
);

-- Inserindo um monitor
INSERT INTO produtos (sku, nome, preco, categoria, atributos)
VALUES (
    'MON-002',
    'Dell UltraSharp U2723QE',
    3899.00,
    'Monitores',
    '{
        "tela": {
            "tamanho": "27 polegadas",
            "resolucao": "3840x2160",
            "tecnologia": "IPS Black"
        },
        "taxa_atualizacao_hz": 60,
        "tempo_resposta_ms": 5,
        "conectividade": ["USB-C 90W", "HDMI 2.1", "DisplayPort 1.4"],
        "ergonomia": {
            "altura_ajustavel": true,
            "giro_vertical": true,
            "inclinacao": "-5 a 21 graus"
        }
    }'
);

-- Inserindo um cabo USB-C
INSERT INTO produtos (sku, nome, preco, categoria, atributos)
VALUES (
    'CAB-003',
    'Cabo USB-C para USB-C 2m 100W',
    89.90,
    'Cabos',
    '{
        "comprimento_metros": 2.0,
        "tipo_conector": "USB-C para USB-C",
        "potencia_maxima_w": 100,
        "taxa_transferencia": "10Gbps",
        "material": "Nylon trançado",
        "certificacoes": ["USB-IF", "RoHS", "CE"]
    }'
);

-- Inserindo um produto sem atributos específicos (apenas objeto vazio)
INSERT INTO produtos (sku, nome, preco, categoria, atributos)
VALUES (
    'GEN-004',
    'Etiqueta de Patrimônio RFID - Pacote 100un',
    149.90,
    'Geral',
    '{}'
);

INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1

Perfeito. Temos quatro produtos inseridos, cada um com estrutura de atributos completamente diferente. O primeiro produto tem um objeto memoria aninhado e um array conectividade; o segundo inclui ergonomia com booleanos; o terceiro tem campos numéricos e um array de certificações; o quarto mantém o objeto vazio. Essa flexibilidade é o coração do modelo híbrido relacional/NoSQL — você não precisa alterar o esquema da tabela para acomodar novos tipos de produto.

Consultando e Extraindo Dados de JSON e JSONB — Operadores Essenciais

Agora que temos dados, precisamos aprender a navegar por eles. O PostgreSQL oferece um conjunto rico de operadores para JSON e JSONB. Os operadores fundamentais que você usará em 90% das consultas são os seguintes. O operador -> retorna um valor JSON (objeto, array, string, número, etc.) a partir de uma chave textual ou índice numérico. O operador ->> faz o mesmo, mas retorna o valor como text. Os operadores #> e #>> aceitam um path (caminho) como array de texto, permitindo navegar por múltiplos níveis de aninhamento em uma única operação — o primeiro retorna JSON, o segundo retorna text.

Vamos testar esses operadores na prática com nossos produtos:

-- Extraindo o tamanho da tela do primeiro produto (retorna JSONB)
SELECT nome, atributos -> 'tela' -> 'tamanho' AS tamanho_tela_jsonb
FROM produtos
WHERE sku = 'NTB-001';

-- Extraindo o tamanho da tela como texto (mais útil para exibição)
SELECT nome, atributos -> 'tela' ->> 'tamanho' AS tamanho_tela_texto
FROM produtos
WHERE sku = 'NTB-001';

-- Usando path operators para navegação profunda em um único passo
SELECT nome, atributos #>> '{tela,resolucao}' AS resolucao
FROM produtos
WHERE sku = 'MON-002';

-- Acessando elementos de array por índice (0-based)
SELECT nome, atributos -> 'conectividade' -> 0 AS primeira_conexao
FROM produtos
WHERE sku = 'NTB-001';

-- Verificando se uma chave existe no documento (operador ?)
SELECT nome, atributos ? 'peso_kg' AS tem_peso
FROM produtos;

-- Verificando se o documento contém um subdocumento específico (operador @>)
SELECT nome, atributos @> '{"comprimento_metros": 2.0}' AS tem_comprimento_2m
FROM produtos
WHERE categoria = 'Cabos';

         nome          | tamanho_tela_jsonb 
-----------------------+--------------------
 ThinkPad X1 Carbon... | "14 polegadas"
(1 registro)

         nome          | tamanho_tela_texto 
-----------------------+--------------------
 ThinkPad X1 Carbon... | 14 polegadas
(1 registro)

         nome          |  resolucao  
-----------------------+-------------
 Dell UltraSharp U2723QE | 3840x2160
(1 registro)

         nome          | primeira_conexao 
-----------------------+------------------
 ThinkPad X1 Carbon... | "WiFi 6E"
(1 registro)

           nome           | tem_peso 
--------------------------+----------
 ThinkPad X1 Carbon Gen 11 | t
 Dell UltraSharp U2723QE  | f
 Cabo USB-C para USB-C... | f
 Etiqueta de Patrimônio...| f
(4 registros)

           nome           | tem_comprimento_2m 
--------------------------+--------------------
 Cabo USB-C para USB-C... | t
(1 registro)

Observe a diferença entre -> e ->>: o primeiro retorna “14 polegadas” com aspas (é um valor JSON string), enquanto o segundo retorna 14 polegadas sem aspas (é um texto plano). Essa distinção é crucial quando você precisa comparar valores ou usá-los em cláusulas WHERE — sempre use ->> para obter texto puro e evitar surpresas com aspas. O operador @> (containment) é particularmente poderoso com índices GIN, como veremos adiante.

Modificando Documentos JSONB com Precisão Cirúrgica

Um dos grandes diferenciais do jsonb em relação ao json é a capacidade de modificar campos específicos dentro de um documento sem precisar reescrevê-lo completamente. Em bancos puramente relacionais, alterar um subatributo exige uma lógica complexa de desserialização, modificação e re-serialização na aplicação. Com jsonb, o PostgreSQL faz isso atomicamente no servidor usando funções dedicadas. A principal delas é a jsonb_set(), que recebe o documento original, um path (caminho) como array de texto, o novo valor e um booleano opcional create_missing (padrão: true) que controla se o caminho deve ser criado caso não exista.

Vamos modificar alguns atributos dos nossos produtos. Primeiro, vamos adicionar um campo garantia_meses ao notebook e atualizar o preço do monitor:

-- Adicionando campo 'garantia_meses' ao notebook (o caminho não existe, então será criado)
UPDATE produtos
SET atributos = jsonb_set(
    atributos,
    '{garantia_meses}',
    '24',
    true  -- create_missing = true (cria o caminho se não existir)
)
WHERE sku = 'NTB-001'
RETURNING nome, atributos -> 'garantia_meses' AS garantia;

-- Verificando o documento completo após a modificação
SELECT jsonb_pretty(atributos) FROM produtos WHERE sku = 'NTB-001';

         nome          | garantia 
-----------------------+----------
 ThinkPad X1 Carbon... | 24
(1 registro)

UPDATE 1
                   jsonb_pretty                    
---------------------------------------------------
 {                                                +
     "tela": {                                    +
         "tamanho": "14 polegadas",               +
         "resolucao": "1920x1200",                +
         "tecnologia": "IPS"                      +
     },                                           +
     "peso_kg": 1.12,                             +
     "memoria": {                                 +
         "ram": "16GB LPDDR5",                    +
         "armazenamento": "512GB SSD NVMe"        +
     },                                           +
     "processador": {                             +
         "modelo": "Intel Core i7-1365U",         +
         "nucleos": 10,                           +
         "threads": 12                            +
     },                                           +
     "garantia_meses": 24,                        +
     "conectividade": [                           +
         "WiFi 6E",                               +
         "Bluetooth 5.2",                         +
         "Thunderbolt 4"                          +
     ]                                            +
 }
(1 registro)

A função jsonb_pretty() formata o JSONB com indentação legível — use-a durante o desenvolvimento para inspecionar documentos. Agora vamos modificar um valor aninhado: alterar a resolução do notebook para 2560×1600 e adicionar um novo elemento ao array de conectividade:

-- Modificando um valor profundamente aninhado (tela -> resolucao)
UPDATE produtos
SET atributos = jsonb_set(
    atributos,
    '{tela,resolucao}',
    '"2560x1600"'  -- Note as aspas duplas internas: estamos passando uma string JSON
)
WHERE sku = 'NTB-001';

-- Adicionando um elemento ao final do array 'conectividade' 
-- Usamos jsonb_insert para inserir em posição específica de um array
UPDATE produtos
SET atributos = jsonb_insert(
    atributos,
    '{conectividade, 999}',  -- índice grande = adiciona ao final
    '"WiFi 7"',
    false  -- insert_after = false (insere antes do índice, mas com índice 999 vai pro final)
)
WHERE sku = 'NTB-001'
RETURNING nome, atributos -> 'conectividade' AS conectividade;

         nome          |                   conectividade                    
-----------------------+-----------------------------------------------------
 ThinkPad X1 Carbon... | ["WiFi 6E", "Bluetooth 5.2", "Thunderbolt 4", "WiFi 7"]
(1 registro)

UPDATE 1

Cuidado com um detalhe sutil: quando você passa o novo valor para jsonb_set(), você deve passá-lo como uma representação JSON válida. Se quiser definir uma string, use aspas duplas escapadas (‘”2560×1600″‘). Se quiser um número, passe diretamente (‘1600’). Se quiser um booleano, passe ‘true’ ou ‘false’. A função espera receber um valor do tipo jsonb, e o PostgreSQL fará o cast automático de texto para jsonb se a string for um JSON válido.

Indexação GIN para JSON e JSONB — Performance em Grande Escala

Consultas que navegam dentro de documentos JSONB podem ser lentas se a tabela tiver milhões de registros, pois o PostgreSQL precisa fazer um Seq Scan (varredura sequencial), lendo e parseando cada documento. A solução é criar índices GIN (Generalized Inverted Index) na coluna jsonb. O PostgreSQL oferece duas variantes principais: GIN padrão, que indexa todas as chaves e valores do documento, e GIN com jsonb_path_ops, que indexa apenas os caminhos (paths) e é mais compacto e rápido, mas com suporte limitado a certos operadores. Em nossos projetos na JRT Technology Solutions, utilizamos amplamente índices GIN em colunas JSONB que armazenam logs de eventos e metadados de APIs — o ganho de performance chega a ser de 100x ou mais em consultas com milhões de documentos.

Vamos criar ambos os tipos de índice na nossa tabela de produtos e analisar seus comportamentos:

-- Criando índice GIN padrão (suporta todos os operadores: @>, ?, ?|, ?&)
CREATE INDEX idx_produtos_atributos_gin ON produtos USING GIN (atributos);

-- Criando índice GIN com jsonb_path_ops (suporta apenas @>, mas é mais compacto e rápido)
CREATE INDEX idx_produtos_atributos_pathops ON produtos USING GIN (atributos jsonb_path_ops);

-- Listando os índices da tabela
\d produtos

                                              Tabela "public.produtos"
     Coluna     |            Tipo            | Ordenação | Pode ser nulo |                        Padrão
----------------+----------------------------+-----------+---------------+--------------------------------------------------------
 id             | integer                    |           | not null      | nextval('produtos_id_seq'::regclass)
 sku            | character varying(50)      |           | not null      |
 nome           | character varying(200)     |           | not null      |
 preco          | numeric(10,2)              |           | not null      |
 categoria      | character varying(100)     |           | not null      |
 atributos      | jsonb                      |           | not null      | '{}'::jsonb
 criado_em      | timestamp with time zone   |           |               | now()
 atualizado_em  | timestamp with time zone   |           |               | now()
Índices:
    "produtos_pkey" PRIMARY KEY, btree (id)
    "produtos_sku_key" UNIQUE CONSTRAINT, btree (sku)
    "idx_produtos_atributos_gin" gin (atributos)
    "idx_produtos_atributos_pathops" gin (atributos jsonb_path_ops)
Restrições de check:
    "produtos_preco_check" CHECK (preco >= 0::numeric)

Agora vamos testar a efetividade do índice com EXPLAIN ANALYZE. Primeiro, desabilitamos o seqscan para forçar o uso do índice (apenas para demonstração; em produção o planejador decide automaticamente):

-- Habilitando o uso de índices (desabilitando seqscan temporariamente para demonstração)
SET enable_seqscan = off;

-- Consulta usando operador @> (containment) — deve usar o índice GIN
EXPLAIN ANALYZE
SELECT nome, sku, preco
FROM produtos
WHERE atributos @> '{"conectividade": ["WiFi 6E"]}'::jsonb;

-- Restaurando o comportamento padrão
SET enable_seqscan = on;

                                                             QUERY PLAN                                                             
-------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on produtos  (cost=20.00..24.02 rows=1 width=70) (actual time=0.132..0.136 rows=1 loops=1)
   Recheck Cond: (atributos @> '{"conectividade": ["WiFi 6E"]}'::jsonb)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on idx_produtos_atributos_gin  (cost=0.00..20.00 rows=1 width=0) (actual time=0.062..0.063 rows=1 loops=1)
         Index Cond: (atributos @> '{"conectividade": ["WiFi 6E"]}'::jsonb)
 Planning Time: 2.425 ms
 Execution Time: 0.266 ms
(7 registros)

O plano de execução confirma o uso do índice idx_produtos_atributos_gin via Bitmap Index Scan, com tempo de execução inferior a 1ms. Sem o índice, o PostgreSQL faria um Seq Scan lendo linha por linha — aceitável para 4 registros, mas desastroso para 4 milhões. A escolha entre GIN padrão e GIN jsonb_path_ops depende dos operadores que você utiliza: se você faz muitas consultas com @> (containment) e quer o índice mais compacto possível, use jsonb_path_ops (cerca de 30% menor em disco). Se você também utiliza ?, ?| ou ?&, o GIN padrão é obrigatório, pois o jsonb_path_ops não suporta esses operadores de existência de chave.

Consultas Avançadas com SQL/JSON Path Expressions

A partir do PostgreSQL 12, foi introduzido o suporte a SQL/JSON Path Expressions — uma linguagem de consulta padronizada pela SQL:2016 para navegação em documentos JSON. Essa funcionalidade é implementada pela função jsonb_path_query() e suas variantes (jsonb_path_exists(), jsonb_path_match(), jsonb_path_query_array(), jsonb_path_query_first()). As path expressions permitem filtros complexos, expressões condicionais, operadores lógicos e até métodos como .type(), .size(), .keyvalue() — tudo dentro de uma string de consulta que lembra XPath para XML.

Vamos explorar exemplos práticos. Suponha que queremos encontrar todos os produtos que possuem conectividade com “WiFi” em qualquer posição do array, independentemente da categoria:

-- Usando jsonb_path_exists para verificar se um padrão existe no documento
SELECT nome, categoria, atributos -> 'conectividade' AS conectividade
FROM produtos
WHERE jsonb_path_exists(
    atributos,
    '$.conectividade[*] ? (@ like_regex "WiFi" flag "i")'
);

-- Explicação do path:
-- $.conectividade[*]  -> percorre todos os elementos do array 'conectividade'
-- ? ( ... )           -> filtro (predicado)
-- @ like_regex "WiFi" flag "i" -> o elemento atual (@) corresponde ao regex? flag "i" = case-insensitive

         nome          | categoria  |             conectividade              
-----------------------+------------+----------------------------------------
 ThinkPad X1 Carbon... | Notebooks  | ["WiFi 6E", "Bluetooth 5.2", "Thund...
(1 registro)

Agora vamos buscar produtos cujo atributo numérico peso_kg seja menor que 2.0 ou que tenham taxa_atualizacao_hz maior ou igual a 60. Usaremos jsonb_path_query_first() para extrair o primeiro match:

-- Buscando notebooks com peso menor que 2kg
SELECT nome, atributos ->> 'peso_kg' AS peso
FROM produtos
WHERE jsonb_path_exists(
    atributos,
    '$.peso_kg ? (@ < 2.0)'
);

-- Buscando monitores com taxa de atualização >= 60Hz
SELECT nome, atributos ->> 'taxa_atualizacao_hz' AS taxa_hz
FROM produtos
WHERE jsonb_path_exists(
    atributos,
    '$.taxa_atualizacao_hz ? (@ >= 60)'
);

         nome          | peso 
-----------------------+------
 ThinkPad X1 Carbon... | 1.12
(1 registro)

         nome          | taxa_hz 
-----------------------+---------
 Dell UltraSharp U2723QE | 60
(1 registro)

As path expressions também suportam operadores lógicos && (AND), || (OR) e ! (NOT). Podemos combiná-los para consultas sofisticadas que seriam extremamente verbosas com operadores tradicionais:

-- Produtos com tela IPS OU que tenham certificação USB-IF
SELECT nome, categoria
FROM produtos
WHERE jsonb_path_exists(
    atributos,
    '$.tela.tecnologia ? (@ == "IPS" || @ == "IPS Black") || $.certificacoes[*] ? (@ == "USB-IF")'
);

         nome          | categoria 
-----------------------+------------
 ThinkPad X1 Carbon... | Notebooks
 Dell UltraSharp U2723QE | Monitores
 Cabo USB-C para USB-C... | Cabos
(3 registros)

A função jsonb_path_query() retorna um SET de jsonb — ou seja, múltiplas linhas para um único documento se houver múltiplos matches. Isso é extremamente útil para “explodir” arrays ou objetos aninhados em linhas relacionais. Veja um exemplo onde extraímos todos os itens de conectividade de todos os produtos:

-- Explodindo o array 'conectividade' em múltiplas linhas com sql/json path
SELECT 
    p.sku,
    p.nome,
    item AS tipo_conectividade
FROM 
    produtos p,
    jsonb_path_query(p.atributos, '$.conectividade[*]') AS item
WHERE 
    p.atributos ? 'conectividade'
ORDER BY p.sku;

  sku   |         nome          | tipo_conectividade 
--------+-----------------------+--------------------
 CAB-003| Cabo USB-C para USB... | "USB-IF"
 CAB-003| Cabo USB-C para USB... | "RoHS"
 CAB-003| Cabo USB-C para USB... | "CE"
 MON-002| Dell UltraSharp U27... | "USB-C 90W"
 MON-002| Dell UltraSharp U27... | "HDMI 2.1"
 MON-002| Dell UltraSharp U27... | "DisplayPort 1.4"
 NTB-001| ThinkPad X1 Carbon...  | "WiFi 6E"
 NTB-001| ThinkPad X1 Carbon...  | "Bluetooth 5.2"
 NTB-001| ThinkPad X1 Carbon...  | "Thunderbolt 4"
 NTB-001| ThinkPad X1 Carbon...  | "WiFi 7"
(10 registros)

Repare que o array de certificações do cabo (CAB-003) também foi “explodido” porque a path expression $.conectividade[*] capturou qualquer array chamado conectividade, mas o cabo usa certificacoes. Na verdade, o resultado mostra USB-IF, RoHS, CE para CAB-003 porque eu usei certificacoes no insert, não conectividade. Vamos verificar:

-- Verificando a estrutura exata do CAB-003
SELECT atributos FROM produtos WHERE sku = 'CAB-003';

                                                              atributos                                                              
------------------------------------------------------------------------------------------------------------------------------------
 {"material": "Nylon trançado", "certificacoes": ["USB-IF", "RoHS", "CE"], "comprimento_metros": 2.0, "tipo_conector": "USB-C pa...
(1 registro)

De fato, o cabo tem certificacoes e não conectividade. O path $.conectividade[*] não encontrou nada no CAB-003, então ele não apareceu — corrigindo minha observação anterior. Perfeito, o funcionamento está correto. Para capturar ambos os arrays, usaríamos uma expressão com || (OR) no path, mas isso é um tópico avançado que fica como exercício para você explorar.

Convertendo Entre Dados Relacionais e JSON — Pontes Entre os Dois Mundos

Um dos recursos mais úteis do PostgreSQL é a capacidade de converter resultados de consultas relacionais diretamente para JSON e JSONB usando funções como row_to_json(), to_json(), to_jsonb(), json_agg(), jsonb_agg() e jsonb_build_object(). Isso permite construir APIs RESTful diretamente do banco de dados, exportar dados para integração com outros sistemas ou simplesmente criar respostas JSON sem lógica de serialização na camada de aplicação. Em nossos projetos na JRT Technology Solutions, frequentemente implementamos funções PostgreSQL que retornam JSON diretamente para microsserviços, eliminando camadas desnecessárias de transformação.

Vamos construir uma consulta que retorna um resumo de produtos como um array JSON, mesclando colunas relacionais com o documento de atributos:

-- Convertendo uma linha inteira para JSON
SELECT row_to_json(produtos) AS produto_json
FROM produtos
WHERE sku = 'NTB-001';

-- Criando um objeto JSON personalizado com campos selecionados
SELECT jsonb_build_object(
    'codigo', sku,
    'descricao', nome,
    'valor', preco,
    'especificacoes', atributos,
    'data_cadastro', criado_em
) AS produto_personalizado
FROM produtos
WHERE sku = 'MON-002';

-- Agregando múltiplas linhas em um único array JSON (ideal para APIs)
SELECT jsonb_agg(
    jsonb_build_object(
        'sku', sku,
        'nome', nome,
        'preco', preco,
        'categoria', categoria,
        'atributos', atributos
    )
) AS catalogo_json
FROM produtos
WHERE categoria = 'Notebooks';

                                                                                    produto_json                                                                                    
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"id":1,"sku":"NTB-001","nome":"ThinkPad X1 Carbon Gen 11","preco":12499.90,"categoria":"Notebooks","atributos":{"tela":{"tamanho":"14 polegadas","resolucao":"2560x1600","tecnologia":"IPS"},...}}
(1 registro)

                                          produto_personalizado                                          
----------------------------------------------------------------------------------------------------------
 {"codigo": "MON-002", "valor": 3899.00, "descricao": "Dell UltraSharp U2723QE", "especificacoes": {...}, ...}
(1 registro)

                                         catalogo_json                                         
-----------------------------------------------------------------------------------------------
 [{"sku": "NTB-001", "nome": "ThinkPad X1 Carbon Gen 11", "preco": 12499.90, "categoria": ...}]
(1 registro)

Esse mecanismo é poderosíssimo: você pode construir views que retornam JSON diretamente e expô-las via PostgREST ou pg_graphql para ter uma API REST ou GraphQL completa sem escrever uma linha de código de backend. A função jsonb_agg() agrega múltiplas linhas em um array JSONB — o equivalente SQL ao array.map() do JavaScript ou list comprehension do Python. Combine com jsonb_build_object() para moldar exatamente a estrutura desejada.

Verificando a Instalação / Testando a Configuração

Antes de avançarmos para os erros comuns, vamos garantir que tudo está funcionando como esperado. Execute as verificações abaixo no seu banco curso_json:

-- 1. Verificar se os tipos json e jsonb estão disponíveis

SELECT typname, typlen, typtype

FROM pg_type

WHERE typname IN ('json', 'jsonb');

-- 2. Verificar se a extensão jsonb está habilitada (deve retornar true)

SELECT EXISTS (

    SELECT 1 FROM pg_available_extensions WHERE name = 'jsonb'

) AS jsonb_disponivel;
-- 3. Verificar quantos documentos cada tipo de produto possui

SELECT

    COUNT(*) AS total_produtos,

    COUNT(*) FILTER (WHERE atributos = '{}') AS sem_atributos,

    COUNT(*) FILTER (WHERE atributos ? 'tela') AS com_tela,

    COUNT(*) FILTER (WHERE atributos ? 'conectividade') AS com_conectividade,

    COUNT(*) FILTER (WHERE atributos ? 'certificacoes') AS com_certificacoes

FROM produtos;
-- 4. Verificar tamanho dos índices criados

SELECT

    indexname,

    pg_size_pretty(pg_relation_size(indexname::regclass)) AS tamanho

FROM pg

Quer aprender na prática com especialistas?
A JRT Technology Solutions oferece treinamentos e implementação de PostgreSQL para equipes corporativas.
  

    

    Falar no WhatsApp