postgresql - How to concatenate a value to a value within json datatype in postgres -

the json column "data" contains value like

{"avatar":"kiran1454916822955.jpg","name":"shanthitwos charmlyi"}

i want concatenate images/profiles/uploads/ json key avatar.

i tried

update activity set data->'avatar' = concat('images/profiles/uploads/',data->'avatar') 

example data:

create table activity (data json); insert activity values ('{"avatar":"first.jpg","name":"first name"}'), ('{"avatar":"second.jpg","name":"second name"}'), ('{"avatar":"third.jpg","name":"third name"}'); 

in postgres 9.4 should create auxiliary function:

create or replace function add_path_to_avatar(json) returns json language sql $$     select json_object_agg(key, value)     (         select              key,              case key::text when 'avatar'                 'images/profiles/uploads/' || value             else value             end         json_each_text($1)     ) s $$;  update activity set data = add_path_to_avatar(data) returning data;                                      data                                      -----------------------------------------------------------------------------  { "avatar" : "images/profiles/uploads/first.jpg", "name" : "first name" }  { "avatar" : "images/profiles/uploads/second.jpg", "name" : "second name" }  { "avatar" : "images/profiles/uploads/third.jpg", "name" : "third name" } (3 rows)     

in postgres 9.5 can use function jsonb_set():

update activity set data = jsonb_set(     data::jsonb,      '{avatar}',      format('"images/profiles/uploads/%s"', data#>>'{avatar}')::jsonb);