postgresql - Postgres fulltext search extract matched term

Question

I am trying to implement a search term suggestion mechanism that works with PostgreSQL 11.1. Using GIN index for "search_vector_index" column.

My query so far for returning the matched rows:

SELECT "study_collection_collection"."id",
       "study_collection_collection"."name",
       "study_collection_collection"."description",
       "study_collection_collection"."search_vector_index",
       ((0 + SIMILARITY("study_collection_collection"."name", 'my coll')) + SIMILARITY("study_collection_collection"."description", 'my coll')) AS "similarity"
  FROM "study_collection_collection"
 WHERE ("study_collection_collection"."is_public" = true AND ((0 + SIMILARITY("study_collection_collection"."name", 'my coll')) + SIMILARITY("study_collection_collection"."description", 'my coll')) >= 0.1)
 GROUP BY "study_collection_collection"."id", ((0 + SIMILARITY("study_collection_collection"."name", 'my coll')) + SIMILARITY("study_collection_collection"."description", 'my coll'))
 ORDER BY "similarity" DESC
 LIMIT 5

What I actually want

Get matched terms all over the searched documents based on their frequency. Is this possible in Postgres? If so, how?

note: please don't refer to Lucene, Elasticsearch, Algolia, or other solutions that require a separate technology than Postgres.

question from:https://stackoverflow.com/questions/65949030/postgres-fulltext-search-extract-matched-term

Answer 1

Waitting for answers