CODEWITHSUNDEEP
jsonpostgresql
Shanthi
Shanthi

Reputation: 716

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')

Upvotes: 0

Views: 280

Answers (1)

klin
klin

Reputation: 121574

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);

Upvotes: 1

Related Questions