been trying to figure this out for a while now, how can i merge two JSON objects into one and add the values together? example:

To insert:
{ "a": 1, "b": 2 }

Already there:
{ "a": 2, "b": 3, "c": 1}

What I want:
{ "a": 3, "b": 5, "c": 1 }

what's the best way to do this with SQL?

Use the

||

operator: https://www.postgresql.org/docs/14/functions-json.html

already tried that, it didn't add the numbers like i wanted

aaa, sorry - I didn't see that you want to sum the values of duplicate fields

no worries haha

I am pretty sure there is no built-in operator/function to do that - you would have to iterate over the keys and merge manually

yeah i was just looking into that

You can do the merging in a Postgres function, but not sure if it would be resource-intensive or not https://docs.yugabyte.com/latest/api/ysql/datatypes/type_array/looping-through-arrays/#looping-over-the-contents-of-a-multidimensional-array-taking-advantage-of-a-non-zero-slice-operand

actually, there might be

Another example here too https://dba.stackexchange.com/questions/231016/loop-through-key-value-pairs-of-a-jsonb-object-in-postgresql-function

it took me way too long but i figured out an obscure way to do it convert the json into rows of keys/values change what i need convert back to json