When the user clicks on another user, a new item is created in the chats table, with a sender and receiver id but an empty chat message.

when the user sends a message, depending on who he sends it to, the item in the chat table will be updated with the sent message.

the message object looks like this [{message: "abc", sender: "person", 
reciever:"person2"}] // there will be multiple object inside the arr

at the moment this is how I my table look:

chats (sender_id, reciever_id, messages jsonb[])

and this is the query I'm sending

UPDATE chats SET 
messages = '[
  { message: 'hi', sender: 'test', reciever: null },
  { message: 'hello', sender: 'test', reciever: null }
]' WHERE 
sender_id = 47 AND 
reciever_id = 43

This is causing errors, and not updating the table. The error doesn't tell me much, but I think it's due to the fact that my object is not in json format(strings on the keys)

so what data type can I use, to allow me to update my messages column using MY format.

CodePudding user response：

You must use double quotes for json.

your data also doesn't need an jsonb array, so i removed it

CREATE TABLE chats (sender_id int, reciever_id int, messages jsonb)

CREATE TABLE

INSERT INTO chats VALUES (47,43, NULL)

INSERT 0 1

UPDATE chats SET 
messages = '[
  { "message": "hi", "sender": "test", "reciever": null },
  { "message": "hello", "sender": "test", "reciever": null }
]' WHERE 
sender_id = 47 AND 
reciever_id = 43

UPDATE 1

SELECT * FROM chats

SELECT 1

fiddle