Extract Keywords From Text Using Postgres

Question

I have a table of keywords. I want to query the keywords table given a string of text and return the keywords found. I was able to get this working in Elasticsearch using this solution. Is this something that is possible in Postgres using the available text search functions? How would the text search query look?

Example:

Keywords table:

id    keyword
--------------
1     thousand eyes
2     facebook
3     superdoc
4     quora
5     your story
6     Surgery
7     lending club
8     ad roll
9     the honest company
10    Draft kings


Given the following text: "I saw the news of lending club on facebook, your story and quora"

Output:

id    keyword
--------------
3     facebook
4     quora
5     your story
7     lending club

select regexp_matches('1 2 3 4 5 666', '\m(1|3 4|6)\M', 'g'); — Abelisto
– Abelisto, Commented May 11, 2021 at 3:58
Could you explain how that does the keyword matching from the keyword column? — Eric Larson
– Eric Larson, Commented May 11, 2021 at 4:46
It was just an example. Instead of constant value '\m(1|3 4|6)\M' you should to build it dynamically using your Keywords table: select '\m(' || string_agg(keyword, '|') || ')\M' from Keywords; The complete query could be: select regexp_matches('I saw the news of lending club on facebook, your story and quora', (select '\m(' || string_agg(keyword, '|') || ')\M' from Keywords)); Then join it with Keywords to get IDs. — Abelisto
– Abelisto, Commented May 11, 2021 at 6:22
Do you know if I can use the text search functions to do the same thing? — Eric Larson
– Eric Larson, Commented May 11, 2021 at 7:23

jjanes · Accepted Answer · 2021-05-11 14:15:23Z

1

Getting your desired answer is pretty simple:

SELECT * FROM keywords WHERE 
    'I saw the news of lending club on facebook, your story and quora' LIKE 
        '%'||keyword||'%' ;

Now I don't know what you want to do with case, or word boundaries, or what you expect for performance. But your example didn't address any of those.

answered May 11, 2021 at 14:15

jjanes

45k5 gold badges39 silver badges48 bronze badges

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

2 Comments

Eric Larson Over a year ago

This should work for now. In the future, what would be some performance enhacements to this query?

Belayer Over a year ago

Sounds like a case of premature optimization. If you have performance issues in the future, ask another question then.

Collectives™ on Stack Overflow

Extract Keywords From Text Using Postgres

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related