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的评论.