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.

You will need to unnest the elements, aggregate (sum()) over the values and the put everything together as a JSON again - if you need to do this very often you might want to consider properly normalizing your model instead of (mis)using JSON. — user330315
– user330315, Commented Oct 24, 2017 at 11:29

klin · Accepted Answer · 2017-10-24 12:21:24Z

2

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.

answered Oct 24, 2017 at 12:21

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update json column with another json object postgres9.5

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related