O operador JSONB || no PostgreSQL: mesclar documentos e aplicar patches

O operador || sobre jsonb faz uma mesclagem rasa de dois documentos: mantem todas as chaves da esquerda, sobrepoe todas as da direita e, em caso de colisao, vence o valor da direita. E a forma mais curta de aplicar um patch parcial ao JSON direto no banco sem reescrever o documento inteiro pela aplicacao.

Mesclar objetos: a chave da direita vence

Quando os dois operandos sao objetos JSON, o resultado e a uniao das chaves. Se uma chave aparece nos dois, o valor vem do operando da direita.

SELECT '{"verified": false, "city": "Lima"}'::jsonb
       || '{"verified": true}'::jsonb;
-- {"city": "Lima", "verified": true}

Alguns fatos para ter em mente:

• O operador nao altera a origem; ele retorna um documento novo, como toda operacao sobre jsonb.
• A ordem dos operandos importa: a || b e b || a diferem quando as chaves se sobrepoem.
• Os dois operandos precisam ser jsonb. Se a coluna for do tipo json, converta com ::jsonb.
• Uma chave da direita com valor null nao remove a chave; ela a sobrescreve com null.

Adicionar elementos a um array

Quando os dois operandos sao arrays, || os concatena. Essa e a maneira comoda de empurrar um elemento para o fim de um array JSON.

SELECT '["sql", "json"]'::jsonb || '["postgres"]'::jsonb;
-- ["sql", "json", "postgres"]

Quando um operando e um array e o outro e um escalar ou objeto, este ultimo e embrulhado em um array de um elemento e colado:

SELECT '["a", "b"]'::jsonb || '"c"'::jsonb;
-- ["a", "b", "c"]

Um caso tipico e adicionar uma tag a um array dentro de uma coluna:

UPDATE users
SET profile = profile || '{"tags": []}'::jsonb
WHERE profile -> 'tags' IS NULL;

Um patch parcial em um UPDATE

O caso principal e atualizar varios campos em uma so expressao. Suponha que orders tenha uma coluna JSONB meta; marcamos um pedido como pago e registramos o metodo:

UPDATE orders
SET meta = meta || '{"paid": true, "method": "card"}'::jsonb
WHERE id = 1001;

Voce pode montar o patch de forma dinamica a partir de colunas com jsonb_build_object em vez de colar strings na mao:

UPDATE users
SET profile = profile || jsonb_build_object(
        'country', country,
        'email', email
    )
WHERE id = 42;

Tambem fica limpo montar uma copia enriquecida dentro de um SELECT, sem tocar na tabela:

SELECT id,
       profile || jsonb_build_object('active', status = 'active') AS enriched
FROM users;

Pegadinha: objetos aninhados sao substituidos por inteiro

O erro mais comum e esperar que || faca uma mesclagem profunda; ele so opera no nivel superior. Se uma chave da direita for um objeto, ela substitui por completo o objeto da esquerda em vez de mesclar campo a campo.

SELECT '{"address": {"city": "Lima", "zip": "15001"}}'::jsonb
       || '{"address": {"city": "Quito"}}'::jsonb;
-- {"address": {"city": "Quito"}}

A chave zip sumiu porque o address da direita sobrescreveu inteiro o da esquerda. Para mudar apenas um campo aninhado, use jsonb_set com um caminho:

UPDATE users
SET profile = jsonb_set(profile, '{address,city}', '"Quito"')
WHERE id = 42;

A regra e simples: || serve para patches planos e concatenacao; jsonb_set para edicoes cirurgicas mais fundo na arvore.

Diferencas em outros motores

• MySQL nao tem operador || para JSON (la ele e um OR logico). A mesclagem e feita com JSON_MERGE_PATCH(a, b), que se comporta como um patch recursivo e ainda remove as chaves cujo valor e null. Existe tambem JSON_MERGE_PRESERVE, que junta os valores em arrays em vez de substituir.
• ClickHouse nao foi feito para edicoes parciais de JSON: suas funcoes JSON sao sobretudo de leitura, e mudar um documento costuma significar reescrever o valor inteiro.

Resumindo: no PostgreSQL || e uma mesclagem rasa rapida onde as chaves da direita vencem; lembre da pouca profundidade e troque por jsonb_set quando precisar entrar dentro.