# Update Multiple keys with jsonb\_set

**URL:** <https://forum.heroiclabs.com/t/update-multiple-keys-with-jsonb-set/1509>\
**Category:** Game Design\
**Created:** [March 30, 2021, 11:04am UTC](https://forum.heroiclabs.com/t/update-multiple-keys-with-jsonb-set/1509 "2021-03-30T11:04:17Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Raja\_Nukala](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.heroiclabs.com/raja_nukala/32/233_2.png) [@Raja\_Nukala](https://forum.heroiclabs.com/u/Raja_Nukala)\
**Post date:** [March 30, 2021, 11:04am UTC](https://forum.heroiclabs.com/t/update-multiple-keys-with-jsonb-set/1509/1 "2021-03-30T11:04:17Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Raja\_Nukala](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.heroiclabs.com/raja_nukala/32/233_2.png) [@Raja\_Nukala](https://forum.heroiclabs.com/u/Raja_Nukala)\
**Post date:** [March 30, 2021, 12:42pm UTC](https://forum.heroiclabs.com/t/update-multiple-keys-with-jsonb-set/1509/2 "2021-03-30T12:42:57Z")

</div>

{“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

---

<div class="post-metadata">

**Author:** ![novabyte](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.heroiclabs.com/novabyte/32/1324_2.png) [@novabyte](https://forum.heroiclabs.com/u/novabyte)\
**Post date:** [March 31, 2021, 8:29am UTC](https://forum.heroiclabs.com/t/update-multiple-keys-with-jsonb-set/1509/3 "2021-03-31T08:29:45Z")

</div>

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:

```sql
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](https://devhints.io/postgresql-json)

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

---

<div class="post-metadata">

**Author:** ![Raja\_Nukala](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.heroiclabs.com/raja_nukala/32/233_2.png) [@Raja\_Nukala](https://forum.heroiclabs.com/u/Raja_Nukala)\
**Post date:** [March 31, 2021, 8:55am UTC](https://forum.heroiclabs.com/t/update-multiple-keys-with-jsonb-set/1509/4 "2021-03-31T08:55:27Z")

</div>

Will try and let you know

---

<div class="post-metadata">

**Author:** ![Raja\_Nukala](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.heroiclabs.com/raja_nukala/32/233_2.png) [@Raja\_Nukala](https://forum.heroiclabs.com/u/Raja_Nukala)\
**Post date:** [March 31, 2021, 9:33am UTC](https://forum.heroiclabs.com/t/update-multiple-keys-with-jsonb-set/1509/5 "2021-03-31T09:33:05Z")

</div>

> [@novabyte](#):
>
> `s."value"`

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

I am getting error

---

<div class="post-metadata">

**Author:** ![Raja\_Nukala](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.heroiclabs.com/raja_nukala/32/233_2.png) [@Raja\_Nukala](https://forum.heroiclabs.com/u/Raja_Nukala)\
**Post date:** [March 31, 2021, 9:39am UTC](https://forum.heroiclabs.com/t/update-multiple-keys-with-jsonb-set/1509/6 "2021-03-31T09:39:11Z")

</div>

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

```sql
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

---

<div class="post-metadata">

**Author:** ![novabyte](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.heroiclabs.com/novabyte/32/1324_2.png) [@novabyte](https://forum.heroiclabs.com/u/novabyte)\
**Post date:** [March 31, 2021, 10:11am UTC](https://forum.heroiclabs.com/t/update-multiple-keys-with-jsonb-set/1509/7 "2021-03-31T10:11:37Z")

</div>

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