在 PostgreSQL 9.5+ 中添加（推送）和从 JSON 数组中删除
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42233542/
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
Appending (pushing) and removing from a JSON array in PostgreSQL 9.5+
提问by Evan Carroll
For versions less than 9.5 see this question
对于低于 9.5 的版本，请参阅此问题
I have created a table in PostgreSQL using this:
我使用这个在 PostgreSQL 中创建了一个表：
CREATE TEMP TABLE jsontesting
AS
SELECT id, jsondata::jsonb FROM ( VALUES
(1, '["abra","value","mango", "apple", "sample"]'),
(2, '["japan","china","india", "russia", "australia"]'),
(3, '["must", "match"]'),
(4, '["abra","value","true", "apple", "sample"]'),
(5, '["abra","false","mango", "apple", "sample"]'),
(6, '["string","value","mango", "apple", "sample"]'),
(7, '["must", "watch"]')
) AS t(id,jsondata);
Now what I wanted was to
现在我想要的是
addSomething like append_to_json_arraytakes in the actual jsondata which is a json-array and the newString which I have to add to that jsondata array and this function should return the updated json-array.
UPDATE jsontesting SET jsondata=append_to_json_array(jsondata, 'newString') WHERE id = 7;removea value from the json data array, one function for removing the value.
add类似append_to_json_array 的东西接收实际的 jsondata，它是一个 json-array 和 newString，我必须将它添加到那个 jsondata 数组中，这个函数应该返回更新的 json-array。
UPDATE jsontesting SET jsondata=append_to_json_array(jsondata, 'newString') WHERE id = 7;从 json 数据数组中删除一个值，一个用于删除该值的函数。
I tried to search documentation of PostgreSQL but found nothing there.
我试图搜索 PostgreSQL 的文档，但一无所获。
回答by Evan Carroll
To add the value use the JSON array append opperator (||)
要添加值，请使用 JSON 数组附加操作符 ( ||)
UPDATE jsontesting
SET jsondata = jsondata || '["newString"]'::jsonb
WHERE id = 7;
Removing the value looks like this
删除值看起来像这样
UPDATE jsontesting
SET jsondata = jsondata - "newString"
WHERE id = 7;
Concatenating to a nested field looks like this
连接到嵌套字段看起来像这样
UPDATE jsontesting
SET jsondata = jsonb_set(
jsondata::jsonb,
array['nestedfield'],
(jsondata->'nestedfield')::jsonb || '["newString"]'::jsonb)
WHERE id = 7;
回答by winduptoy
To add to Evan Carroll's answer, you may want to do the following to set the column to an empty array if it is NULL. The append operator (||) does nothing if the column is currently NULL.
要添加到 Evan Carroll 的答案中，您可能需要执行以下操作以将列设置为空数组（如果是NULL. ||如果列当前为 ，则追加运算符 ( ) 不执行任何操作NULL。
UPDATE jsontesting SET jsondata = (
CASE
WHEN jsondata IS NULL THEN '[]'::JSONB
ELSE jsondata
END
) || '["newString"]'::JSONB WHERE id = 7;
