Reputation: 403
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
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