在 postgresql、选项数组或对象中插入 jsonb 数据，有效方式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/47131040/
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
insert jsonb data in postgresql, option array or objects, valid way
提问by DDave
I have this update, i've read postgresql documentation, but nothing clear about how to insert data, some tutorials options:
我有这个更新，我已经阅读了 postgresql 文档，但不清楚如何插入数据，一些教程选项：
1.with '{}'
2.with {}
3.with '[]' <-- array of objects
and most dont' use '::jsonb' like is indicated on:
并且大多数不'使用':: jsonb'就像在：
https://www.postgresql.org/docs/9.4/static/datatype-json.html
https://www.postgresql.org/docs/9.4/static/datatype-json.html
here my code:
这是我的代码：
UPDATE customer set phones ='{ {"type": "mobile", "phone": "001001"} ,
{"type": "fix", "phone": "002002"} }'::jsonb
where id ='4ca27243-6a55-4855-b0e6-d6e1d957f289';
I get this error:
我收到此错误：
ERROR: invalid input syntax for type json
LINE 1: UPDATE customer set phones ='{ {"type": "mobile", "phone": ...
^
DETAIL: Expected string or "}", but found "{".
CONTEXT: JSON data, line 1: { {...
SQL state: 22P02
Character: 29
I need just record a lit of phones, need to enclose in a big name object like? I mean for javascript , array of objets is not an object, but i dont know if that is accepted in jsonb of postresql
我只需要录制一盏电话，需要附上一个大名鼎鼎的对象吗？我的意思是对于 javascript，对象数组不是对象，但我不知道 postresql 的 jsonb 是否接受了
{ phones:[ {"type": "mobile", "phone": "001001"} , {"type": "fix", "phone": "002002"} ] }
{手机：[ {“type”：“mobile”，“phone”：“001001”}，{“type”：“fix”，“phone”：“002002”}]}
回答by Yuci
Example 1 (object):
示例 1（对象）：
CREATE TABLE customer {
contact JSONB
}
update customer
set contact = '{ "phones":[ {"type": "mobile", "phone": "001001"} , {"type": "fix", "phone": "002002"} ] }'
where id = '4ca27243-6a55-4855-b0e6-d6e1d957f289';
Example 2 (array):
示例 2（数组）：
CREATE TABLE customer {
phones JSONB
}
update customer
set phones = '[ {"type": "mobile", "phone": "001001"} , {"type": "fix", "phone": "002002"} ]'
where id = '4ca27243-6a55-4855-b0e6-d6e1d957f289';
Notes:
笔记：
- My PostgreSQL version
- 我的 PostgreSQL 版本
select version();
PostgreSQL 11.2 (Debian 11.2-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
- Be sure to enclose the keys and values with double quotes.
- 确保用双引号将键和值括起来。
回答by Vao Tsun
'{}'is array type in postgres. if you use jsonb, use regular '[]'for array:
'{}'是 postgres 中的数组类型。如果使用jsonb，请使用常规'[]'数组：
so=# select jsonb_pretty('{"phones":[ {"type": "mobile", "phone": "001001"} , {"type": "fix", "phone": "002002"} ] }');
jsonb_pretty
{
"phones": [
{
"type": "mobile",
"phone": "001001"
},
{
"type": "fix",
"phone": "002002"
}
]
}
(1 row)
Time: 0.486 ms
or:
或者：
so=# select jsonb_pretty('[ {"type": "mobile", "phone": "001001"} , {"type": "fix", "phone": "002002"} ]');
jsonb_pretty
[
{
"type": "mobile",
"phone": "001001"
},
{
"type": "fix",
"phone": "002002"
}
]
(1 row)
