Postgres найти и вернуть ключевые слова из списка в Select

Question

У меня есть простая таблица postgres, которая содержит столбец комментариев (текст). В представлении мне нужно найти в этом поле комментариев список слов, а затем вернуть разделенный запятыми список слов, найденных в виде столбца (а также группы обычных столбцов).

Список определенных ключевых слов содержит около 20 слов. Т.е. яблоки, бананы, груша, персик, слива.

Идеальным результатом будет что-то вроде:

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

Я думаю, что мне нужно сделать подзапрос и array_agg? Или, возможно, "где в". Но я не могу понять, как соединить это вместе.

Большое спасибо, Стив

Answer 1

Вы можете использовать средства полнотекстового поиска для достижения результатов:

1. Установите новый словарь ispell с вашим списком слов.
2. Создайте конфигурацию полнотекстового поиска, которая будет основана на вашем словаре. Не забудьте удалить все остальные словари из конфигурации, потому что в вашем случае все остальные слова на самом деле являются стоп-словами.

После этого при выполнении

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

вы получите только ваши ключевые слова: 'apples' & 'bananas' или даже 'apple' & 'banana' если вы правильно настроили словарь.

По умолчанию английская конфигурация использует словари снежного кома, которые сокращают окончания слов, поэтому, если вы запустите

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

ты получишь

'like' & 'appl' & 'banana'

что не совсем подходит для вашего случая.

Answer 2

Еще один более простой способ (но медленнее):

создать таблицу dict:

create table keywords (nm text);

insert into keywords (nm)
values ('apples'), ('bananas');

Выполните следующий скрипт для вашего текста, чтобы извлечь ключевые слова

select string_agg(regexp_replace(foo, '[^a-zA-Z\-]*', '', 'ig'), ',') s
  from regexp_split_to_table('I like apples and bananas!', E'\\s+') foo 
 where regexp_replace(foo, '[^a-zA-Z\-]*', '', 'ig') in (select nm from keywords)

Это решение хуже с точки зрения семантики, поэтому бананы и бананы будут разными ключевыми словами.