JSONB replace value of a specific key Postgresql

Question 1

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.

Question 2

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.

Question 3

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"})

Demo

update content
set dynamic_fields = dynamic_fields || '{"key2": "text2"}'::jsonb;

P.S:

Also, you can use jsonb_set function to change data.

Demo

update content
set dynamic_fields = jsonb_set(dynamic_fields, '{key2}', '"text2"');

klin · Answer 1 · 2021-11-22T14:25:16

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.

Pooya · Answer 2 · 2021-11-22T14:09:06

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"})

Demo

update content
set dynamic_fields = dynamic_fields || '{"key2": "text2"}'::jsonb;

P.S:

Also, you can use jsonb_set function to change data.

Demo

update content
set dynamic_fields = jsonb_set(dynamic_fields, '{key2}', '"text2"');

I want the existing text to stay intact, so this works only if the value is "text1" and I want to change it to "text2", but if I have "aaaaaa text1" and I want to change it to "aaaaaa text2" it doesn't work. I need to replace a portion of the string, not to update the whole string.

JSONB replace value of a specific key Postgresql

In other languages

This page is in other languages

Popular in the category