How can I insert into a Postgresql JSON array
I think this is a clash between the JSON notation that starts with {
and the short hand array notation in Postgres where the string representation of an array is also denoted by an {
.
The following works:
insert into chats
(messages)
values
(array['{"sender":"pablo","body":"they are on to us"}']::json[]);
This avoids the ambiguity of the {{
by using an explicit array constructor.
To make the array a json array you need to either cast the string to a json
or the resulting array to a json[]
(see the example above). Casting the whole array makes it easier if you have more than one JSON document in that row:
insert into chats
(messages)
values
(array['{"sender":"pablo","body":"they are on to us"}',
'{"sender":"arthur"}']::json[]);
alternatively:
insert into chats
(messages)
values
(array['{"sender":"pablo","body":"they are on to us"}'::json,
'{"sender":"arthur"}'::json]);