How to remove a value from a postgres text column json string array?

Question

I have a postgresql table with a column that is a text datatype. This column value is a JSON string array. These values could be in any order such as

["a", "b", "c"]

or

["c", "a", "b", "d"]

or

["a"]

Now one of these values "b" is no longer used and needs to be removed from each record in the table.

so ["a", "b", "c"] should be converted to ["a", "c"]

I have looked into using one of the postgres functions array_remove() but haven't been able to get it to work.

Is it possible to convert to an array type and convert back to a string after using remove?

Use the json or jsonb data type for JSON values, not text. There are many functions for working with it. — Bergi
– Bergi, Commented May 11, 2021 at 23:56

Bergi · Accepted Answer · 2021-05-12 00:07:08Z

3

Use a jsonb value (cast your text if necessary) and the - operator:

UPDATE table SET col = col::jsonb - 'a';

answered May 12, 2021 at 0:07

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1