How do I modify a JSON object? I have a table "raw_data" in which I have a field "data" containing a json array like:

{'a' : 1, 
'b' : 2,
'c' : 3}


I would like two different things:


Append a new item like {'d' : 4} at the end of the array, that would give me :
{'a' : 1, 'b' : 2, 'c' : 3, 'd' : 4}
Update the key 'c' with the value 5 in this array, that would give me:    {'a' : 1, 'b' : 2, 'c' : 5}
Upsert the already existing key 'd' with the value 6 in this array, that would give me:    {'a' : 1, 'b' : 2, 'c' : 3, 'd' : 6}


I found this post, but couldn't manage to understand how it applies to my specific case
The post you linked to was mine. That was on an array, but the operator is the same for an object (||) concatentation.


Append a new item like {'d' : 4}

SELECT $${"a":1,"b":2,"c":3}$$::jsonb || '{"d":4}';

Update the key 'c' with the value 5

SELECT ($${"a":1,"b":2,"c":3}$$::jsonb || '{"d":4}') || {"c":5}';

Upsert the already existing key 'd' with the value 6

SELECT (($${"a":1,"b":2,"c":3}$$::jsonb || '{"d":4}') || '{"c":5}') || '{"d":6}';



End result,

{"a": 1, "b": 2, "c": 5, "d": 6}