How can I remove a specific element from a jsonb array of primitive strings in postgres-CodePudding

Assuming I have an unordered array (i.e. the order of elements in this array may be different for each row in the table) of simple string like ["Foo", "Bar", "Baz"], how can I simply delete the (e.g.) "Bar" element? All my research assumes that jsonb arrays will contain objects with identifiers, but nothing for filtering for primitive strings.

One thing to note is that the actual column is not of type jsonb, but of text. In a query I simply cast it to jsonb so that I could easily insert an element:

SELECT jsonb_insert(element::jsonb, '{0}', '"Bar"')::TEXT FROM table

The original text value in the column is ["Foo", "Bar", "Baz"]

But not I need a way to revers this query.

CodePudding user response：

You can use the - operator:

select '["Foo", "Bar", "Baz"]'::jsonb - 'Bar'

returns ["Foo", "Baz"]

If your column isn't defined as jsonb (why?) then cast it: the_column::jsonb - 'Bar'

e.g. in an update statement:

update the_table
   set the_column = (the_column::jsonb - 'Bar')::text
where ...

CodePudding user response：

This seemed to do the trick:

create or replace function remove_key(p_input jsonb, p_to_remove jsonb)
returns jsonb
as
$$
select jsonb_agg(e)
from jsonb_array_elements(p_input) as t(e)
where t.e <> p_to_remove;
$$
language sql
immutable;

update table set "flags"=coalesce(remove_key(row::jsonb, '"value"'::jsonb), '[]'::jsonb)::text

where '"value"' will be whatever you are filtering, i.e. '"Bar"'