How can I insert into a Postgresql JSON array

Question

The table definition is:

 chat_id serial primary key, last_update timestamp, messages JSON[] 

and I want to insert a record like this:

insert into chats (messages) values ('{{"sender":"pablo","body":"they are on to us"}}');

with error:

ERROR: malformed array literal: "{{"sender":"pablo","body":"they are on to us"}}" LINE 1: insert into chats (messages) values ('{{"sender":"pablo","bo...

I have also tried this approach :

insert into chats (messages) values (ARRAY('{"sender":"pablo","body":"they are on to us"}'));

Note that updating the row and inserting with array_append works OK.

Answer 1

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]);