Update query on jsonb column only inserts first value

Why is it, that if you use jsonb_set as new values in an UPDATE query, that it only updates the first row of the result set?

See this example here: http://sqlfiddle.com/#!17/0bdd8/5

There are two entries in reactions for the same post, yet when I try to assign a random value keyed to the username, it only inserts it for the first value not for the second one:

UPDATE posts
SET    a_to_b = jsonb_set(posts.a_to_b, array[username::text], to_jsonb(random()))
FROM   reactions 
WHERE  posts.id = reactions.post_id;

Solution

There are more than one row in the FROM clause for a single row to be modified. The documentation explains it clearly:

When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.

You can execute a single update by aggregating the expected value in a subquery. Use the aggregate function jsonb_object_agg():

update posts p
set a_to_b = agg
from (
    select p.id, jsonb_object_agg(username, random()) as agg
    from posts p
    join reactions r on p.id = r.post_id
    group by p.id
) s
where s.id = p.id;

SqlFiddle.

Alternatively, you can use an anonymous code block to repeatedly update a single row, e.g.:

do $$
declare rec record;
begin
    for rec in
        select * 
        from posts p
        join reactions r on p.id = r.post_id
    loop
        update posts
        set a_to_b = jsonb_set(a_to_b, array[rec.username], to_jsonb(random()))
        where posts.id = rec.post_id;
    end loop;
end $$;

The second solution may turn out to be suboptimal, especially for a large number of aggregated values.