如何插入到 Postgresql JSON 数组中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35081748/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I insert into a Postgresql JSON array
提问by arisalexis
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...
错误：格式错误的数组文字："{{"sender":"pablo","body":"they are on to us"}}" 第 1 行：插入聊天（消息）值（'{{"sender":"巴勃罗”、“博...
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.
请注意，更新行并使用 array_append 插入工作正常。
回答by a_horse_with_no_name
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 alsodenoted by an {.
我认为这是以 JSON 开头的{表示法与 Postgres 中的简写数组表示法之间的冲突，其中数组的字符串表示也由{.
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 jsonor 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:
要使数组成为 json 数组，您需要将字符串转换为 ajson或将结果数组转换为 a json[]（参见上面的示例）。如果该行中有多个 JSON 文档，则转换整个数组会更容易：
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]);
回答by Michas
It is quite complicate to escape special characters. I would use insert from select to make it easier.
转义特殊字符非常复杂。我会使用 insert from select 来使它更容易。
- Create JSON array.
- Convert it to set of JSON variables.
- Convert the set to SQL array.
- 创建 JSON 数组。
- 将其转换为一组 JSON 变量。
- 将集合转换为 SQL 数组。
The select part below.
下面选择部分。
WITH bu AS (SELECT json_array_elements('[{"name":"obj1"},{"name":"obj2"},{"name":"obj3"},{"name":"obj4"}]'::json) AS bu)
SELECT array_agg(bu) FROM bu;
