I have a table that has the following columns (sender_id, reciever_id, messages) the messages column takes in a json object that looks like this:

[{"message":"final in numbers","sender":"test","reciever":"try"}]

Currently, I'm able to UPDATE the messages column with new messages objects, using this query:

"UPDATE chats SET messages = $1 WHERE sender_id = $2 AND reciever_id = $3",
       

The problem with this, is that it removes the previous value stored at messages and updates it with the new one. This is a problem, I don't want to lose the previous values/messages/objects.

I found a way to solve this using concatenation

UPDATE chats SET messages = messages || $1 WHERE sender_id = $2 AND reciever_id = $3

The problem with this is that it only concatenate if there is at least one json object in the messages column, if it's empty, I won't get any errors, but the value doesn't get added.

Is there a way I can fix this, so that I can add to an empty column, but also not lose the objects when a new sessions is started.

I know I can probably glue something using if statements in my code, but I feel like that's overcomplicating it.

CodePudding user response：

try this :

UPDATE chats SET messages = COALESCE(messages || $1, $1) WHERE sender_id = $2 AND reciever_id = $3