1

I'd like to determine if a text input contains any keywords from a known list, but also show which ones are found.

E.g with Keywords: ['fox', 'jump', 'dog', 'foo', 'bar']

'the quick brown foxes jumped over the lazy dogs'

yields

['fox','jump','dog']

I appreciate that I can do boolean matching, but I can't work out if I can return the matched keywords, as opposed to a highlighted input snippet.

I'd like to use FTS so i can benefit from finding some synonyms etc.

Is this possible in Postgres?

2 Answers 2

1

You have two components of this problem

  1. getting your array to a ts_query,
  2. getting the keywords highlighted
  3. getting the keywords out of the returned string

Now first and foremost, this is FTS. All words in the search string get tokenized as a ts_query. From that point on the original search string is lost. You no longer have words you have lexemes -- tokens which have been stemmed and normalized.

Getting your array to a ts_query,

Essentially, a ts_query looks like, 'fox' & 'jump' & 'dog' & 'foo' & 'bar' You should review plainto_tsquery which does slightly more voodoo to get text to a tsquery, and to_tsquery which assumes the string is already properly formatted.

To get the array to a ts_query, you can do something like this

SELECT to_tsquery(array_to_string(myarray, ' & '))
FROM ( VALUES (ARRAY['fox','jump','dog','foo','bar']) )
  AS t(myarray);

Highlighting

To get highlighting working you need something like this,

SELECT ts_headline(
  'quick brown foxes jumped over the lazy dogs',
  to_tsquery(array_to_string(myarray, ' & '))
)
FROM ( VALUES (ARRAY['fox','jump','dog','foo','bar']) )
  AS t(myarray);

That returns

                           ts_headline                            
------------------------------------------------------------------
 quick brown <b>foxes</b> <b>jumped</b> over the lazy <b>dogs</b>
(1 row)

There are quite a few options on ts_headline. All of the docs for this are found in Controlling Text Search

Getting out the words

The only other thing you can do here is to pull out the words which triggered those tokens

SELECT array_agg(rm[1]) AS WORDS
FROM ( 
  SELECT ts_headline(
    'quick brown foxes jumped over the lazy dogs',
    to_tsquery(array_to_string(myarray, ' & '))
  ) 
  FROM ( VALUES (ARRAY['fox','jump','dog','foo','bar']) ) 
    AS t(myarray)
)
  AS t(s)
CROSS JOIN LATERAL regexp_matches(s,'<b>(.*?)</b>', 'g') AS rm(matches);

        words        
---------------------
 {foxes,jumped,dogs}
(1 row)
8
  • Thank you. Interesting approach to drag out the matched segments. Shame I cannot get the matched keywords. Jul 21, 2017 at 15:32
  • @RobShepherd I was working on it, submitted to quick. Check the update Jul 21, 2017 at 15:32
  • It makes sense though, right? If you searched for ARRAY['jump', 'jumped', 'jumping'] what would you expect it to to return? All three forms of jump? Jul 21, 2017 at 15:35
  • Well if the desired keywords are extensions of each other like this example then yes I suppose that would be required, but for me they are distinct preset keywords. Jul 21, 2017 at 15:38
  • Right, but the functionality to determine that would have to keep a mapping table of words to tokens in the vector, and then printing out the result would have to crawl that mapping. The idea of FTS is that it's all vector intersections at the end of the day. Jul 21, 2017 at 15:40
0

Smallish change on Evan Carroll's solution:

SELECT 
    array_agg(rm[1]) AS words
FROM 
    (SELECT 
        ts_headline(
            'Quick brown foxes jumped over the lazy dogs. Cats jumping',
            to_tsquery('simple' /* <--- */, array_to_string(myarray, ' & '))
        ) 
    FROM 
        (VALUES (ARRAY['fox','jump','jumping', 'jumped', 'dog','foo','bar'])) AS t(myarray)
    ) AS t(s)
    CROSS JOIN LATERAL regexp_matches(s,'<b>(.*?)</b>', 'g') AS rm(matches);
| words                       |
| :-------------------------- |
| {foxes,jumped,dogs,jumping} |

dbfiddle here

Per Evan Carroll's comments: 'simple' dictionnary doesn't stem (convert words to lexemes), only lowercases words, and (if specified) takes out stop words.

4
  • A few friendly notes: (1) simple doesn't actually take out plurals. (2) It actually presents a huge disadvantage in that stemming decreases selectivity. For instance, If you search for "houses" in "This is my house" with the simple dictionary it won't match, with the default it will. (3) it doesn't even take out stop words unless you create a dictionary from the template that does. Jul 21, 2017 at 21:23
  • @EvanCarroll: (2) I know of (at least someof ) the disadvantages (maning I've never put it to use). But for this specific use case where the original poster wants jump, jumped, jumping to be distinct preset keywords, it seems to fit. (1) As per the plurals, you're right: dbfiddle here I'll update answer.
    – joanolo
    Jul 21, 2017 at 21:30
  • 1
    Actually I don't want jump, jumped, jumping to be distinct keywords - this was Evan's pedagogical example. I actually want jump and other way more distinct terms to be keywords, but to match jump, jumped, jumping. my non-specific requirement is to output just jump as the keyword that was found, even if it was a derivative/synonym/plural etc. I am trying to retrospectively 'tag' documents with some very specific criteria. Jul 22, 2017 at 19:12
  • 1
    @RobShepherd yea, it's just not possible. Nor do I see it happening anytime. It would be massively memory intensive. Now if your question is how do you make it happen. That's a much more complex question. It would involve building your own system with NLP. Jul 22, 2017 at 21:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.