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;
>从json数据数组中删除一个值,一个用于删除值的函数.
我试图搜索postgresql的文档,但没有发现任何内容.
@H_
404_16@
拉德克的想法可以用来定义这些方便的
功能：
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的评论.
