Updating a single field in a JSON object can be achieved with a custom SQL query using our runtime provided functions in Lua/JavaScript or the *sql.DB handle in Go.
For example, if I had the following storage object:
-
collection:
economy -
key:
inventory -
owner_id:
<some_user_id>
{
"consumables":{
"item_A":{
"count":3
},
"item_B":{
"count":10
}
}
}
Let’s say we’d like to increment “item_B” count by 1 in a single operation, it can be achieved with the following custom SQL query:
UPDATE storage SET value = jsonb_set(value, '{consumables,item_B,count}', to_jsonb((value->'consumables'->'item_B'->'count')::INT + 1)) WHERE collection = 'economy' AND user_id = '<some_user_id>' AND key = 'inventory';
The full reference for jsonb type column manipulation can be found here.
We’d discourage anyone from writing custom SQL unless absolutely needed, our provided APIs ensure optimal performance when reading/writing to the db, we cannot guarantee the same in custom written SQL. The performance gain from sparing a read might arguably not be needed for most use-cases; or worth it from a code maintainability standpoint.