如何使用where子句从存储在PostgreSQL中jsonb列类型的JSON数组中修改或删除特定的JSON对象？

在我的 Postgres 数据库中，我有一个具有 jsonb 数据类型的表列。在该列中，我存储 JSON 数组。现在，我想删除或修改数组内的特定 JSON 对象。

我的 JSON 数组看起来像

[
    {
        "ModuleId": 1,
        "ModuleName": "XYZ"
    },
    {
        "ModuleId": 2,
        "ModuleName": "ABC"
    }
]

现在，我想执行两个操作：

1. 如何从上面 ModuleId 为 1 的数组中删除 JSON 对象？
2. 如何修改 JSON 对象，即将 ModuleName 更改为 ModuleId 为 1 的“CBA”？

有没有一种方法可以直接对 JSON 数组执行查询？

注意：Postgres版本是12.0

这两个问题都需要取消嵌套并聚合回（修改后的）JSON 元素。对于这两个问题，我将创建一个函数以使其更易于使用。

create function remove_element(p_value jsonb, p_to_remove jsonb)
  returns jsonb
as
$$
  select jsonb_agg(t.element order by t.idx)  
  from jsonb_array_elements(p_value) with ordinality as t(element, idx)
  where not t.element @> p_to_remove;
$$
language sql
immutable;

该函数可以像这样使用，例如在 UPDATE 语句中：

update the_table
  set the_column = remove_element(the_column, '{"ModuleId": 1}')
where ...

对于第二个问题，类似的函数会派上用场。

create function change_value(p_value jsonb, p_what jsonb, p_new jsonb)
  returns jsonb
as
$$
  select jsonb_agg(
         case
           when t.element @> p_what then t.element||p_new
           else t.element
         end order by t.idx)  
  from jsonb_array_elements(p_value) with ordinality as t(element, idx);
$$
language sql
immutable;

The ||运算符将覆盖现有密钥，因此这实际上会用新名称替换旧名称。

你可以这样使用它：

update the_table
  set the_column = change_value(the_column, '{"ModuleId": 1}', '{"ModuleName": "CBA"}')
where ...;

我认为传递 JSON 值比硬编码键更灵活，这使得该函数的使用非常有限。第一个函数还可以用于通过比较多个键来删除数组元素。

如果您不想创建函数，请将函数调用替换为select从功能上看。