Menu

JSONB no PostgreSQL: operadores, consultas e índices

Guarde e consulte JSON no PostgreSQL com jsonb: os operadores -> e ->>, contenção com @>, jsonb_set e outras atualizações, transformar arrays em linhas, índices GIN e json vs jsonb.

Esta página tem editores executáveis. Edite, execute e veja a saída na hora.

jsonb é o tipo de coluna JSON binário do PostgreSQL. Leia campos com -> (retorna jsonb) e ->> (retorna text), encontre linhas com o operador de contenção @> e altere campos com jsonb_set ou ||. Um índice GIN deixa essas consultas rápidas.

Os pedidos 1 e 3 batem. data->'customer' retorna o objeto aninhado como jsonb, ->>'name' tira o nome como texto, e ->> mais uma conversão transforma o total em número.

Tirar valores de um JSONB

OperadorRetornaExemplo
-> chave ou índicejsonbdata->'customer', data->'tags'->0
->> chave ou índicetextdata->>'status'
#> caminhojsonbdata#>'{customer,city}'
#>> caminhotextdata#>>'{customer,city}'
[...] subscrito (PostgreSQL 14+)jsonbdata['customer']['city']

Os índices de array começam em 0, e um índice negativo conta a partir do fim (-> -1 é o último elemento). Uma chave que não existe dá NULL do SQL, não um erro, então o pedido 3 acima tem a cidade NULL.

Use ->> para comparar com texto ou para converter: data->>'status' = 'paid'. Use -> para continuar navegando. Um erro comum é data->'status' = 'paid'. O PostgreSQL lê 'paid' como JSON, uma palavra sem aspas não é JSON válido, e a consulta falha com "invalid input syntax for type json".

Encontrar linhas pelo conteúdo JSON

@> testa se o valor da esquerda contém o da direita. Funciona em qualquer profundidade e dentro de arrays, e um índice GIN dá suporte a ele:

Os produtos 1 e 2 têm tamanho M e preço; só o 2 custa mais de 30. Os operadores de busca:

  • @> contém, <@ está contido em.
  • ? a chave (ou string do array) existe, ?| alguma das chaves existe, ?& todas existem.
  • @? e @@ rodam uma consulta JSON path (PostgreSQL 12+), para comparações dentro do documento.

Atualizar campos dentro de um JSONB

O PostgreSQL reescreve o valor inteiro a cada atualização, mas estas funções deixam você descrever só a mudança:

  • jsonb_set(target, path, new_value) substitui um valor num caminho.
  • - remove uma chave, e #- remove um caminho aninhado como '{notify,sms}'.
  • || adiciona ou sobrescreve chaves de primeiro nível.

Desde o PostgreSQL 14 você também pode atribuir por subscrito: SET prefs['notify']['email'] = 'false'.

Duas armadilhas:

  • jsonb_set retorna NULL quando o novo valor é NULL do SQL, e aí o UPDATE apaga a coluna inteira. Passe COALESCE(to_jsonb(x), 'null'), ou use jsonb_set_lax (PostgreSQL 13+), que por padrão grava um null do JSON.
  • jsonb_set adiciona uma última chave que falta, mas não os pais que faltam: definir '{a,b}' em {} não muda nada. A atribuição por subscrito cria os pais.

Transformar arrays JSON em linhas

jsonb_array_elements retorna uma linha por elemento do array, e jsonb_to_recordset transforma um array de objetos em colunas tipadas. Desde o PostgreSQL 17, o JSON_TABLE padrão faz as duas coisas num só lugar:

A-1 vendeu 3 e B-2 vendeu 1. No sentido contrário, jsonb_build_object('id', id, 'name', name) monta um objeto a partir de colunas e jsonb_agg junta linhas num array (veja string_agg e array_agg).

Indexar JSONB com GIN

Um índice GIN na coluna inteira atende @>, ?, ?|, ?&, @? e @@:

CREATE INDEX orders_data_idx ON orders USING gin (data);

-- smaller and faster, but only for @>, @? and @@
CREATE INDEX orders_data_path_idx ON orders USING gin (data jsonb_path_ops);

-- one field compared with = or a range: a B-tree on the expression
CREATE INDEX orders_status_idx ON orders ((data->>'status'));

Escreva a consulta na forma que o índice suporta. WHERE data @> '{"status": "paid"}' pode usar o índice GIN. WHERE data->>'status' = 'paid' não pode; precisa do índice de expressão. Um teste de intervalo como (data->>'total')::numeric > 50 precisa de um índice de expressão sobre essa expressão exata.

JSON vs JSONB

jsonjsonb
Armazenado comoO texto de entradaUma forma binária já interpretada
Espaços, ordem das chaves, chaves duplicadasMantidosRemovidos; vale a última duplicada
EscritaUm pouco mais rápida (só valida)Interpretado na entrada
Leitura e consultasReinterpretado a cada acessoRápida
@>, ? e índices GINNãoSim

json retorna o texto sem mudanças; jsonb mantém só "a": 3 e reordena as chaves. Use jsonb, a não ser que você precise devolver o texto original byte a byte, como o payload guardado de um webhook. Campos que toda linha tem, ou que precisam de chave estrangeira, ficam em colunas comuns. A referência oficial das funções JSON lista todos os operadores e funções.

Perguntas frequentes

Qual a diferença entre -> e ->> no PostgreSQL?

-> retorna o valor como jsonb, então você pode continuar navegando (data->'customer'->'city'). ->> retorna como text, que é o que você compara com uma string ou converte para número: (data->>'total')::numeric.

Como verificar se um array JSONB contém um valor?

Use contenção: data->'tags' @> '["gift"]', ou data @> '{"tags": ["gift"]}' para testar a partir da raiz do documento. Para um array de strings, data->'tags' ? 'gift' também funciona. Um índice GIN em data acelera a segunda forma.

O JSONB mantém a ordem das chaves?

Não. O jsonb guarda as chaves na própria ordem dele, remove espaços em branco e mantém só a última de chaves duplicadas. Se você precisa do texto original exatamente igual, use o tipo json.

Como verificar se uma chave existe no JSONB?

data ? 'key' é verdadeiro quando o objeto de primeiro nível tem essa chave, mesmo que o valor seja null do JSON. ?| recebe um array e é verdadeiro se alguma das chaves existir, ?& se todas existirem.

Posso pôr uma chave estrangeira ou NOT NULL num campo dentro do JSONB?

Não diretamente. Uma restrição CHECK pode testar um campo (CHECK (data ? 'email')), mas chaves estrangeiras precisam de uma coluna de verdade. Campos que toda linha tem e que outras tabelas referenciam devem ficar em colunas comuns.

Ilustração das linguagens de programação do Coddy

Aprenda a programar com o Coddy

Começar