Conditionally delete item inside an Array Field PostgreSQL

Question

I'm building a kind of dictionary app and I have a table for storing words like below:

id | surface_form | examples
-----------------------------------------------------------------------
 1 | sounds       | {"It sounds as though you really do believe that",
   |              |  "A different bell begins to sound midnight"}

Where surface_form is of type CHARACTER VARYING and examples is an array field of CHARACTER VARYING

Since the examples are generated automatically from another API, it might not contain the exact "surface_form". Now I want to keep in examples only sentences that contain the exact surface_form. For instance, in the given example, only the first sentence is kept as it contain sounds, the second should be omitted as it only contain sound.

The problem is I got stuck in how to write a query and/or plSQL stored procedure to update the examples column so that it only has the desired sentences.

Answer 1

This query skips unwanted array elements:

select id, array_agg(example) new_examples
from a_table, unnest(examples) example
where surface_form = any(string_to_array(example, ' '))
group by id;

 id |                    new_examples                    
----+----------------------------------------------------
  1 | {"It sounds as though you really do believe that"}
(1 row) 

Use it in update:

with corrected as (
    select id, array_agg(example) new_examples
    from a_table, unnest(examples) example
    where surface_form = any(string_to_array(example, ' '))
    group by id
)
update a_table
set examples = new_examples
from corrected
where examples <> new_examples
and a_table.id = corrected.id; 

Test it in rextester.

Answer 2

Maybe you have to change the table design. This is what PostgreSQL's documentation says about the use of arrays:

Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.

Documentation: https://www.postgresql.org/docs/current/static/arrays.html