CODEWITHSUNDEEP
postgresql
Red October
Red October

Reputation: 689

Remove element from JSONB array in PostgeSQL

I have a JSOB field in the table with simple data like this:

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

How do I can remove a single element from this array? I tried something like this, but with no result:

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

Upvotes: 0

Views: 434

Answers (1)

user330315
user330315

Reputation:

There is no built-in function for that. You can only remove array elements by position. The fact that you need something like this, indicates that your choice of de-normalizing your data model might have been a wrong decision. This would be a trivial DELETE operation on a properly normalized model.

However, you can easily write a function to remove an array element by value:

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;

The expression a.element #>> '{}' is necessary to convert the scalar jsonb value to a text value without having to deal with quotes in case the JSON array contains something different than plain integers. The function will fail with an error if you don't pass a JSON array.

Then you can use it like this:

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

Upvotes: 1

Related Questions