在Postgres jsonb字段中最多存储最近的N个元素

Question

我有一个Postgres表，它有一个JSONB列，我想在其中存储一个数组。该阵列一开始具有0个元素，但将增长到最多可容纳N个元素。

每次更新时，我都需要向该字段添加一个元素，直到元素数量从0增加到N。之后，每次添加新元素时，都应该删除最旧的元素，以便数组只包含最近的N个元素。

因此，如果表是myTable，JSONB数组列是myArray、传入元素是>myElement并且N是10，我会。。。

UPDATE myTable SET myArray=???

有人能提供这样附加元素的查询吗？

提前感谢！

Answer 1

您可以编写一个函数来实现对数组长度的检查：

create or replace function append_element(p_array jsonb, p_new_element anyelement, p_max_elements int)
returns jsonb
as
$$
begin
p_array := p_array || to_jsonb(p_new_element);

while jsonb_array_length(p_array) > p_max_elements loop 
p_array := p_array - 0;
end loop;
return p_array;
end;
$$
language plpgsql
immutable;

可以在UPDATE语句中使用：

update the_table
set the_column = append_element(the_column, 'a new one', 10)
where ....;

这假设the_column直接存储阵列，例如["one", "two"]，而不是像{"elements": ["one", "two"]}这样的子结构

可以通过检查传递的jsonb值是否确实是一个数组并抛出错误来改进该函数。