在 PostgreSQL 9.2、9.3 和 9.4 中添加(推送)和从 JSON 数组中删除?

问题描述

我使用这个在 PostgreSQL 中创建了一个表:

I have created a table in PostgreSQL using this:

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);

现在我想要的是

• add 类似 append_to_json_array 的东西接收实际的 jsondata，它是一个 json-array 和 newString，我必须将它添加到那个 jsondata 数组和这个函数中应该返回更新的 json-array.

• add Something like append_to_json_array takes 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;

从json数据数组中删除一个值，一个删除值的函数.

remove a value from the json data array, one function for removing the value.

我试图搜索 postgreSQL 的文档，但一无所获.

I tried to search documentation of postgreSQL but found nothing there.

推荐答案

Radek 的想法可以用来定义这些方便的函数:

Radek's idea can be used to define these handy functions:

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", "beta", "gamma", "delta"]

select jsonb_array_remove('["alfa", "beta", "gamma"]', 'beta');
 jsonb_array_remove
-------------------
 ["alfa", "gamma"]

select jsonb_array_replace('["alfa", "beta", "gamma"]', 'alfa', 'delta');
     jsonb_array_replace
----------------------------
 ["delta", "beta", "gamma"]

如果它们证明对您有用，请感谢 Radek 的回答.但是，我必须补充一点，我完全同意 a_horse 的评论.

If they prove useful for you, please appreciate Radek's answer. However, I have to add I fully agree with a_horse's comment.

这篇关于在 PostgreSQL 9.2、9.3 和 9.4 中添加(推送)和从 JSON 数组中删除?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！