Postgres Find and Return Keywords From List Within Select

Question

I have a simple postgres table that contains a comments (text) column. Within a view, I need to search that comments field for a list of words and then return a comma separated list of the words found as a column (as well as a bunch of normal columns).

The list of defined keywords contains about 20 words. I.e. apples, bananas, pear, peach, plum.

Ideal result would be something like:

id | comments                    | keywords
-----------------------------------------------------
1  | I like bananas!             | bananas
2  | I like apples.              | apples
3  | I don't like fruit          | 
4  | I like apples and bananas!  | apples,bananas

I'm thinking I need to do a sub query and array_agg? Or possibly 'where in'. But I can't figure out how to bolt it together.

Many thanks, Steve

Answer 1

You can use full-text search facilities to achieve results:

1. Setup new ispell dictionary with your list of words.
2. Create full-text search configuration which will be based on your dictionary. Don't forget to remove all other dictionaries from configuration, because in your case all other words actually are stopwords.

After that when you execute

select plainto_tsquery('<your config name>', 'I like apples and bananas!')

you will get only your keywords: 'apples' & 'bananas' or even 'apple' & 'banana' if you setup dictionary properly.

By default, english configuration uses snowball dictionaries which reduce word endings, so if you run

select plainto_tsquery('english', 'I like apples and bananas!')

you will get

'like' & 'appl' & 'banana'

which is not exact suitable for your case.