Update Multiple keys with jsonb_set

Game Design
3

Based on the structure of your storage object and only if you NEED to perform the update without a simple read before write using optimistic concurrency control as described in the storage engine documentation you can achieve what you want with SQL which looks like:

UPDATE storage AS s SET
    value = value 
    || jsonb_build_object(
        'Cat_Advance_Anim',
        COALESCE((s."value"->>'Cat_Advance_Anim')::INT + $4::INT, $4::INT))
    || jsonb_build_object(
        'Cat_Simple_AR',
        COALESCE((s."value"->>'Cat_Simple_AR')::INT + $4::INT, $4::INT))
WHERE user_id = $1 AND collection = 'heroes' AND key = 'hero1';

The SQL above merges two JSONB objects into the current value who’s fields are the result of fetching the current value and adding some input to their respective JSON fields. You can have a look at this helpful cheat sheet for JSONB operators: PostgreSQL JSON cheatsheet

:warning: A note of caution though; you should minimize your use of custom SQL as much as you can because you’re responsible for the performance profile of the queries and if they do not perform well it will impact the performance of the rest of the server APIs. Use custom SQL sparingly!

1 Like