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?

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)
  • Thank you. Interesting approach to drag out the matched segments. Shame I cannot get the matched keywords. – Rob Shepherd Jul 21 '17 at 15:32
  • @RobShepherd I was working on it, submitted to quick. Check the update – Evan Carroll Jul 21 '17 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? – Evan Carroll Jul 21 '17 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. – Rob Shepherd Jul 21 '17 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. – Evan Carroll Jul 21 '17 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.

  • 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. – Evan Carroll Jul 21 '17 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 '17 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. – Rob Shepherd Jul 22 '17 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. – Evan Carroll Jul 22 '17 at 21:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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