How to do a full text search, when content has HTML tags?

user_12455837 0
Viewed 413

I want to make a full-text search using text content in HTML format. E.g.:

... f<em>oo</em> ...

If the search term will be 'foo' the document containing "foo" will not be found

How to make this work?

I'm using PostgreSQL

postgresql sql
2 Answers

I'm afraid you won't find anything out of the box, but you could extract text from your html column, e.g. using regular expressions or xpath (designed for xml)

CREATE TABLE t (html text);
INSERT INTO t VALUES ('<html>
  <h1>
    <foo>test f<em>oo</em> bar</foo>
  </h1>
  <h1>
    <foo>test bar</foo>
  </h1>
 </html>');

SELECT * FROM t
WHERE to_tsvector(regexp_replace(html,'<.+?>','','g')) @@ plainto_tsquery('test foo');

                 html                 
--------------------------------------
 <html>                              +
   <h1>                              +
     <foo>test f<em>oo</em> bar</foo>+
   </h1>                             +
   <h1>                              +
     <foo>test bar</foo>             +
   </h1>                             +
  </html>

Keep in mind that creating the ts_vector in query time will make things quite slow. So, if you decided to go this way consider creating a new column for it and then create a gin index, e.g.

CREATE INDEX idx_html_tsvector ON t USING gin(the_new_column);

Demo: db<>fiddle

user_22753880

The PostgreSQL text parser will recognize tags but considers them as ending words, so it will parse that as 'f' and 'oo'. This cannot be changed without hacking the C code. You could implement your own parser, but again in C, and doing so is not easy. Your best bet is probably to pre-process your text with something else to remove the tag, making sure that closes up the gap to give 'foo', not 'f oo'.

user_17212390
Related