I need to replace the values of specific keys inside a jsonb object in Postgresql:
create table content (
id int,
dynamic_fields jsonb
);
insert into content values (0, '{
"key1": "aaaaa text1",
"key2": "text1",
"key3": "blabla"}'::jsonb);
UPDATE content
SET dynamic_fields = replace(dynamic_fields::text, 'text1', 'text2')::jsonb;
This code up here gives the following result:
id | dynamic_fields
0 | {"key1": "aaaaa text2", "key2": "text2", "key3": "blabla"}
Instead of replacing all the occurrences of "text1", I'd like to replace only that text inside the value of "key1": how do I do it?
The result of the update should be something like:
id | dynamic_fields
0 | {"key1": "aaaaa text1", "key2": "text2", "key3": "blabla"}
UPDATED the desired outcome, it wasn't clear enough.
CodePudding user response:
Use the function jsonb_build_object()
.
update content
set dynamic_fields =
dynamic_fields ||
jsonb_build_object('key1', replace(dynamic_fields->>'key1', 'text1', 'text2'))
where dynamic_fields ? 'key1'
Test it in Db<>fiddle.
CodePudding user response:
You can use the operator || to contact two JSON data and generate new JSON data.
Now we can use || to join old JSON data to new JSON data (Like: {"key2": "text2"}
)
update content
set dynamic_fields = dynamic_fields || '{"key2": "text2"}'::jsonb;
P.S:
Also, you can use jsonb_set
function to change data.
update content
set dynamic_fields = jsonb_set(dynamic_fields, '{key2}', '"text2"');