Update json column with another json object postgres9.5

Question

I have a table with structure

id(int) | attributes (json)

1       | {"a":1,"b":2}

I want to update attributes column with another json object

{"b":5, "c":8}

such that final output looks like this

id(int) | attributes (json)

1       | {"a":1,"b":7, "c":8}

I am able to get the following result using || operator

id(int) | attributes (json)

1       | {"a":1,"b":5, "c":8}

But not the desired.

Not able to find any other specific function/operation for this task.

So any documentation will be helpfull.

Thanks in advance.

Answer 1

Create a custom function, e.g.:

create or replace function json_merge_and_sum(json, json)
returns json language sql as $$
    select json_object_agg(key, sum order by key)
    from (
        select key, sum(value::int) -- or ::numeric
        from (
            select * from json_each_text($1) 
            union 
            select * from json_each_text($2)) a
        group by key
        ) s
$$;

select json_merge_and_sum('{"a":1,"b":2}'::json, '{"b":5, "c":8}'::json);

      json_merge_and_sum       
-------------------------------
 { "a" : 1, "b" : 7, "c" : 8 }
(1 row)

Of course, the function works well only if all values of json arguments are numeric.