时间戳

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

postgresql - 如何使用 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

标签： postgresqljsonb

解决方案

这两个问题都需要取消嵌套并聚合回（修改后的）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;

操作员将||覆盖现有密钥，因此这有效地将旧名称替换为新名称。

你可以像这样使用它：

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

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

如果您不想创建函数，请将函数调用替换为函数中select的。

推荐阅读