时间戳

首页 > 解决方案 > 从 PostgeSQL 中的 JSONB 数组中删除元素

postgresql - 从 PostgeSQL 中的 JSONB 数组中删除元素

问题描述

我在表中有一个 JSOB 字段，其中包含如下简单数据：

[375680, 549410, 545754, 549071, 546086, 548511, 547351, 560005, 522775, 534784, 609045, 601870]

如何从此数组中删除单个元素？我尝试了这样的事情，但没有结果：

UPDATE my_table SET my_json_field=my_json_field-'["549410"]'

标签： postgresql

解决方案

没有内置功能。您只能按位置删除数组元素。您需要这样的事实表明您选择对数据模型进行非规范化可能是一个错误的决定。这将是对适当规范化模型的微不足道的 DELETE 操作。

但是，您可以轻松编写一个函数来按值删除数组元素：

create function remove_element(p_array jsonb, p_to_remove int)
 returns jsonb
as
$$
  select jsonb_agg(a.element order by a.idx)
  from jsonb_array_elements(p_array) with ordinality as a(element, idx)
  where (a.element #>> '{}') <> p_to_remove::text;
$$
language sql
immutable;

如果 JSON 数组包含与普通整数不同的内容，则表达式a.element #>> '{}'对于将标量 jsonb 值转换为text值而无需处理引号是必需的。如果您不传递 JSON 数组，该函数将失败并出现错误。

然后你可以像这样使用它：

UPDATE my_table 
  SET my_json_field = remove_elmeent(my_json_field, 549410);

推荐阅读