如何向Postgresql的JSON数组中插入数据？

Question

表格的定义如下：

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

我想插入这样的一条记录：

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

出现错误：

ERROR: 不良的数组字面量："{{"sender":"pablo","body":"they are on to us"}}" LINE 1: insert into chats (messages) values ('{{"sender":"pablo","bo...

我也尝试了这种方法：

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

请注意，更新行并使用array_append插入的操作是有效的。

- arisalexis

Answer 1

我认为这是JSON符号以 { 开头和Postgres中的简写数组符号之间的冲突，其字符串表示形式也由 { 表示。下面的代码可行：

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

通过使用明确的数组构造函数，避免了{{的歧义。

要将数组转换为 JSON 数组，您需要将字符串强制转换为json, 或将结果数组强制转换为json []（请参见上面的示例）。如果一行中有多个 JSON 文档，则将整个数组转换会更容易：

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

或者：

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

- user330315

---

可以简单地将以下内容插入到聊天记录中：insert into chats (messages) values '[{"sender":"pablo","body":"they are on to us"}, {"sender":"arthur"}]'::json - Naufal Khalid

@NaufalKhalid：不，因为该列（在问题中）被定义为json[]而不是json - 但我确实同意json[]可能从来没有意义。 - user330315

是的，没错。当您始终可以使用JSON具有相同的功能时。 - Naufal Khalid

这些SQL语句中缺少引号。应该是' {"sender":"arthur"}']::json[]或'{"sender":"arthur"}'::json。 - randomness2077

Answer 2

转义特殊字符相当复杂。我会使用从选择中插入来使它更容易。

1. 创建JSON数组。
2. 将其转换为一组JSON变量。
3. 将该集合转换为SQL数组。

下面是选择部分。

WITH bu AS (SELECT json_array_elements('[{"name":"obj1"},{"name":"obj2"},{"name":"obj3"},{"name":"obj4"}]'::json) AS bu)
SELECT array_agg(bu) FROM bu;

- Michas