Update Multiple keys with jsonb_set

Development
#1

Hi this is raja ,
I want to update the multiple key values on collection on single query. i tried following

UPDATE storage SET
value = jsonb_set(value, ‘{level}’::VARCHAR, (COALESCE(value->>‘level’,‘0’)::INT + 1)::VARCHAR::JSONB, true)
WHERE id = $1 AND collection = ‘heroes’ AND key = ‘hero1’;

its working fine for single key

I want to increment values for multiple keys in single query , Like
power, health,speed (these are for example in my game i have another fields)

i want to update all of them in single query ( my game needs this)
all if them are increment values only
how to do that?

Thank you

#2

{“Cat_Advance_Anim”: 9, “Cat_Simple_Anim”: 34,“Cat_Simple_AR”: 2}

this is my table
i want to update(increment ) above values in single sql query

#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!

#4

Will try and let you know

#5

May i know whis this “s” here?
will it work for lua?

I am getting error

#6

i changed your code little bit like below, and working fine

UPDATE storage SET value = value
    || jsonb_build_object('Cat_Advance_Anim', COALESCE((value->> 'Cat_Advance_Anim')::INT + 4::INT, 4::INT))
    || jsonb_build_object('Cat_Simple_Anim', COALESCE((value->> 'Cat_Simple_Anim')::INT + 4::INT, 4::INT))
WHERE collection = 'heroes'

thank you so much for your reply

1 Like
#7

It was a typo. I fixed the code example above.