For versions greater than 9.5 see this question
我使用这个在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,"true",(5,"false",(6,'["string",(7,"watch"]')
) AS t(id,jsondata);
现在我想要的是
> add像append_to_json_array这样的东西接受实际的jsondata这是一个json数组和newString,我必须添加到jsondata数组,这个函数应该返回更新的json数组.
UPDATE jsontesting SET jsondata=append_to_json_array(jsondata,'newString') WHERE id = 7;
拉德克的想法可以用来定义这些方便的功能：
create function jsonb_array_append(j jsonb,e text) returns jsonb language sql immutable as $$ select array_to_json(array_append(array(select * from jsonb_array_elements_text(j)),e))::jsonb $$; create function jsonb_array_remove(j jsonb,e text) returns jsonb language sql immutable as $$ select array_to_json(array_remove(array(select * from jsonb_array_elements_text(j)),e))::jsonb $$; create function jsonb_array_replace(j jsonb,e1 text,e2 text) returns jsonb language sql immutable as $$ select array_to_json(array_replace(array(select * from jsonb_array_elements_text(j)),e1,e2))::jsonb $$;
行动中的功能：
select jsonb_array_append('["alfa","beta","gamma"]','delta');
jsonb_array_append
------------------------------------
["alfa","gamma","delta"]
select jsonb_array_remove('["alfa",'beta');
jsonb_array_remove
-------------------
["alfa","gamma"]
select jsonb_array_replace('["alfa",'alfa','delta');
jsonb_array_replace
----------------------------
["delta","gamma"]
如果他们证明对你有用,请欣赏拉德克的回答.但是,我必须补充一点,我完全同意a_horse的评论.
