CODEWITHSUNDEEP
jsonpostgresqlupdates
ssct79
ssct79

Reputation: 403

Update JSON string value using PostgreSQL

I have a column which is varchar type, but inside I have json string values like

{"namme":"john", "email":"[email protected]", "cep":"0000"}

I want to update all rows changing "namme" for "name" like this:

{"name":"john", "email":"[email protected]", "cep":"0000"}

I've tried a query like this :

update lista_distribuicao
set mycolumn = mycolumn::jsonb - 'namme'
                          || jsonb_build_object('name',
                                                mycolumn->'namme')

Bu I get the following error:

[42883] ERROR: operator does not exist: jsonb - unknown Dica: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Upvotes: 0

Views: 215

Answers (1)

TLR
TLR

Reputation: 587

You are close, the only thing missing is the here :

json_build_object('name',mycolumn::jsonb->'namme')

Working example :

CREATE TABLE test (
    te varchar
);

INSERT INTO test VALUES ('{"namme":"john", "email":"[email protected]", "cep":"0000"}');

UPDATE test
SET te = te::jsonb - 'namme' || jsonb_build_object('name', te::jsonb->'namme');

SELECT te
FROM test;

Be careful, running this query twice will set the name column to NULL

Upvotes: 1

Related Questions