JSONB_SET no PostgreSQL: atualizar um campo dentro de um documento JSON

jsonb_set retorna uma copia de um documento JSON com o valor de um caminho dado substituido por um novo. E o cavalo de batalha das atualizacoes parciais: em vez de reescrever todo o JSON pela aplicacao, voce muda um unico campo aninhado direto no banco e deixa intactas as demais chaves.

Sintaxe e o caminho

A assinatura e jsonb_set(target, path, new_value [, create_missing]). O caminho e um array de chaves de texto que percorre o documento ate o ponto que voce quer mudar.

SELECT jsonb_set(
    '{"address": {"city": "Quito", "zip": "170150"}}'::jsonb,
    '{address,city}',
    '"Lima"'
);
-- {"address": {"city": "Lima", "zip": "170150"}}

Alguns pontos para manter em mente:

• path e escrito como literal de array: '{address,city}' significa "chave address e, dentro dela, a chave city".
• new_value precisa ser JSON valido. A string "Lima" tem que levar suas aspas; um Lima solto provoca um erro.
• Voce pode colocar indices de array no caminho: '{tags,0}' e o primeiro elemento do array tags.
• A funcao so opera sobre jsonb, nao sobre json. Converta a coluna com ::jsonb se preciso.

O flag create_missing

O quarto argumento controla o que acontece quando a chave ainda nao esta no documento. O padrao e true, entao uma chave ausente e criada.

-- key "verified" is absent; with create_missing = true (default) it is added
SELECT jsonb_set(
    '{"city": "Lima"}'::jsonb,
    '{verified}',
    'true',
    true
);
-- {"city": "Lima", "verified": true}

-- with create_missing = false the document is returned unchanged
SELECT jsonb_set(
    '{"city": "Lima"}'::jsonb,
    '{verified}',
    'true',
    false
);
-- {"city": "Lima"}

Pegadinha: create_missing so cria a chave final do caminho. Se um objeto intermediario nao existe (digamos que nao ha chave address alguma, mas voce escreve em '{address,city}'), o caminho fica inalcancavel e o documento volta sem alteracoes e sem erro nenhum. Isso parece um sucesso silencioso, entao verifique o resultado.

Atualizar um campo aninhado em um UPDATE

O caso principal e corrigir um campo direto em uma tabela. Suponha que users tenha uma coluna JSONB profile e voce precise mudar a cidade de um usuario:

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

Como jsonb_set retorna uma copia, voce atribui essa copia de volta a coluna. O resto de profile permanece como estava.

As chamadas se aninham, entao voce pode mudar varios campos em uma so passada:

UPDATE users
SET profile = jsonb_set(
        jsonb_set(profile, '{address,city}', '"Lima"'),
        '{verified}', 'true'
    )
WHERE country = 'PE';

Para numeros, insira um numero de verdade sem aspas: jsonb_set(data, '{score}', '10') produz 10, enquanto '"10"' produz a string "10". Um valor dinamico sai facil com to_jsonb:

UPDATE orders
SET meta = jsonb_set(meta, '{discount}', to_jsonb(amount * 0.1))
WHERE status = 'paid';

Remover chaves com o operador menos

Para apagar um campo voce nem precisa de jsonb_set; o operador - faz isso. Ele remove uma chave de nivel superior ou um elemento de array por indice.

-- remove a top-level key
SELECT '{"city": "Lima", "tmp": 1}'::jsonb - 'tmp';
-- {"city": "Lima"}

-- remove an array element by index
SELECT '["a", "b", "c"]'::jsonb - 1;
-- ["a", "c"]

Para uma chave aninhada use o operador #-, ao qual se passa um caminho:

UPDATE users
SET profile = profile #- '{address,zip}'
WHERE id = 42;

Varias chaves de nivel superior somem de uma vez com um array:

SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - '{a,b}'::text[];
-- {"c": 3}

Diferencas em outros motores

O PostgreSQL e o motor mais comodo para esse trabalho, mas a sintaxe muda nos demais:

• MySQL usa JSON_SET(col, '$.address.city', 'Lima') com um caminho em forma de string $.a.b. Tambem tem JSON_REMOVE para apagar e JSON_REPLACE, que nunca cria chaves ausentes, exatamente o comportamento de create_missing = false.
• ClickHouse nao foi feito para atualizacoes parciais de JSON: suas funcoes JSON sao sobretudo de leitura, e mudar um documento costuma significar reescrever o valor inteiro.

A regra pratica para o PostgreSQL: jsonb_set sempre devolve um documento novo, entao so funciona junto a uma atribuicao (SET col = jsonb_set(...)); para apagar recorra a - e #-.