postgresql 在 postgres 中更新 json 数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41593815/
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
Update json array in postgres
提问by TLR
I have a data field that looks like this :
我有一个如下所示的数据字段：
{ "field1" : [{"name":'name1',"value1":true},
{"name":'name2',"value2":false}
],
"field2" : [{"name":'name1',"value1":true},
{"name":'name2',"value2":false}
]
}
Is it possible to update a specific field with an update ?
是否可以使用更新来更新特定字段？
create table t_json (
t_data json
);
insert into t_json values('{"field1":[{"name":"name1","value" : true},{"name":"name2","value" : false}],"field1":[{"name":"name1","value" : true},{"name":"name2","value" : false}]}');
select t_data->'field1'
from t_json;
I tried this :
我试过这个：
update t_json
set t_data->'a' = '[{"value1" : true, "value2" : false}]';
But I get an error : "syntax error at or near ->
但我收到一个错误：“在或附近出现语法错误 ->
What is missing ?
缺什么 ？
回答by Ryan Wheale
I wanted to post this here in case it helps anybody else. By all means use JSON over JSONB unless you actually need features that JSONB affords you. In general, if you need to perform queries on the JSON data itself, use JSONB. If you are just needing to store data, use JSON.
我想在这里张贴这个以防它对其他人有帮助。除非您确实需要 JSONB 为您提供的功能，否则一定要在 JSONB 上使用 JSON。一般来说，如果您需要对 JSON 数据本身执行查询，请使用 JSONB。如果您只需要存储数据，请使用 JSON。
Anyhow, here is how I am updating a JSON[]field:
无论如何，这是我更新JSON[]字段的方式：
UPDATE foo SET bar = ARRAY[$${"hello": "world"}$$, $${"baz": "bing"}$$]::JSON[]
The important things to notice are this:
需要注意的重要事项是：
- The array is wrapped like this:
ARRAY[ ... ]::JSON[] - Each item in the array is wrapped like this:
$${ "foo": "bar" }$$
- 数组是这样包装的：
ARRAY[ ... ]::JSON[] - 数组中的每一项都是这样包装的：
$${ "foo": "bar" }$$
It is worth noting that this same technique can be used for other array types. For example, if you have a text[]column, the query would look like this:
值得注意的是，同样的技术可用于其他数组类型。例如，如果您有一text[]列，查询将如下所示：
UPDATE foo SET bar = ARRAY[$$hello world$$, $$baz bing$$]::TEXT[]`
回答by Evan Carroll
Fixing your typos
修正你的错别字
Doubt it. This is not valid json. name1and name2must be double quoted. To ease working with json, ALWAYSuse double quotes. ALWAYSquery-quote with double-dollar.
怀疑。这不是有效的 json。name1并且name2必须双引号。为了便于使用 json，请始终使用双引号。总是用双美元查询报价。
{ "field1" : [{"name":'name1',"value1":true},
{"name":'name2',"value2":false}
],
"field2" : [{"name":'name1',"value1":true},
{"name":'name2',"value2":false}
]
}
And, what you INSERTEDis also funky.. ALWAYSpaste beautified valid JSON in your question.
而且，你INSERTED也很时髦......总是在你的问题中粘贴美化的有效 JSON。
{
"field1":[{"name":"name1","value" : true},{"name":"name2","value" : false}],
"field1":[{"name":"name1","value" : true},{"name":"name2","value" : false}]
}
Let's change that and fix it.
让我们改变它并修复它。
{
"field1":[{"name":"name1","value" : true},{"name":"name2","value" : false}],
"field2":[{"name":"name1","value" : true},{"name":"name2","value" : false}]
}
Now let's put it in a query..
现在让我们把它放在一个查询中..
TRUNCATE t_json;
INSERT INTO t_json (t_data) VALUES ($$
{
"field1":[{"name":"name1","value" : true},{"name":"name2","value" : false}],
"field2":[{"name":"name1","value" : true},{"name":"name2","value" : false}]
}
$$);
Making the update of the JSON
更新 JSON
Now it works.. Now you can update it as you want..
现在它可以工作了.. 现在您可以根据需要更新它..
UPDATE t_json
SET t_data = jsonb_set(
t_data::jsonb,
'{field1}',
$${"whatever":1}$$
);
Change from JSON to JSONB
从 JSON 更改为 JSONB
Notice we're having to cast to jsonb. As a general rule, NEVERuse JSON (not everyone agrees, see comments). There is no point. Instead use the newer JSONB.
请注意，我们必须强制转换为jsonb。作为一般规则，永远不要使用 JSON（并非所有人都同意，请参阅评论）。无关紧要。而是使用较新的 JSONB。
ALTER TABLE t_json ALTER COLUMN t_data TYPE jsonb ;
Now you can do
现在你可以做
UPDATE t_json
SET t_data = jsonb_set(
t_data,
'{field1}',
$${"whatever":1}$$
);
