Shanthi February 2016

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 to concatenate images/profiles/uploads/ for all the json key avatar.

I tried

UPDATE activity SET data->'avatar' = CONCAT('images/profiles/uploads/',data->'avatar')

Answers


klin February 2016

Example data:

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

In Postgres 9.4 you should create an auxiliary function:

create or replace function add_path_to_avatar(json)
returns json language sql as $$
    select json_object_agg(key, value)
    from (
        select 
            key, 
            case key::text when 'avatar' then
                'images/profiles/uploads/' || value
            else value
            end
        from 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 you can use the function jsonb_set():

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

Post Status

Asked in February 2016
Viewed 3,844 times
Voted 5
Answered 1 times

Search




Leave an answer