Postgresql – TSQuery To Search Number and UoM in Text

full-text-searchpostgresql

I'm beginner with full text search so I'm learning this feature using psql following a tutorial i found here. I modify the data sample to meet my own need:

INSERT INTO documents (document_text) VALUES  
('Pack my box with five inches liquor jugs.'),
('Jackdaws love my big sphinx of quartz.'),
('The 5" wizards jump quickly.'),
('How vexingly quick daft zebras jump!'),
('Bright vixens jump; dozy fowl quack.'),
('Sphinx of black quartz, judge my vow.');

And tried to search using several combination of keyword hoping to get document #1 and #3, such as:

SELECT document_id, document_text FROM documents  
WHERE document_tokens @@ to_tsquery('5"');

output: document #3 only

SELECT document_id, document_text FROM documents  
WHERE document_tokens @@ to_tsquery('5&inch');

output: null

SELECT document_id, document_text FROM documents  
WHERE document_tokens @@ to_tsquery('5&in');

output: document #3 only

SELECT document_id, document_text FROM documents  
WHERE document_tokens @@ to_tsquery('five&in');

output: document #1 only

SELECT document_id, document_text FROM documents  
WHERE document_tokens @@ to_tsquery('inch&five');

output: document #1 only

While naturally we can assume that 5" / 5 in / five inches all are same, why the fts give different result (can't output both document #1 and #3)? If I use the fts in wrong way, please show me the right implementation of it or if I need to use other search engine to solve my problem, which search engine should I use?

Best Answer

FTS is very clever in some ways, but not very clever in other ways. It is not clever (by default) in the way you want it to be.

You can set up a thesaurus to map words to synonyms, but I don't know if there is any way to get your hands on the " symbol in order to map it. And of course it is unlikely to make sense to convert every ", or for that matter every 'in', into 'inches'.

Related Solutions

MongoDB text index and text search

So, no need to test as it turns out, I just remembered the key deciding limitations. You can only create one text index on a collection (reference here), so you have no real choice. Additionally, MongoDB can only use one index at a time to satisfy a query (until index intersection is introduced in 2.6).

Hence, the only workable option is to create the compound index as you outlined, on both fields as a single index:

db.articles.ensureIndex(
                     {
                       title: "text",
                       abstract: "text",
                     },
                     {
                       weights: {
                                  title: 1,
                                  abstract:1,
                                },
                       name: "TextIndex"
                     }
                   )

The same remains true for 3 or 4 fields, you would first have to drop the existing index, then create the new index and include 3/4/other.

Postgresql – finding keywords in text using full text search in PostgreSQL

You have two components of this problem

getting your array to a ts_query,
getting the keywords highlighted
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)