How can I full text search with sentences through large documents?

user_19464206 0
Viewed 30

I am building a website that will allow users to search for specific words/phrases inside large documents. I'm using PostgreSQL and each document stores a title and the document's actual content (there will be less than 10,000 documents each under 1MB of text). I want to use full text or fuzzy searching but the only solutions I've found so far allow for matching only one or two words.

Documents table:

CREATE TABLE documents (
  id serial,
  title text,
  content text
);

Example:

For example purposes, I'll add a few rows to the table.

INSERT INTO documents (title, content)
VALUES ('The Brown Fox', 'The quick brown fox jumps over the lazy dog')

INSERT INTO documents (title, content)
VALUES ('Boxing Wizards', 'The five boxing wizards jump quickly.')

INSERT INTO documents (title, content)
VALUES ('Crazy Fredrick', 'Crazy Fredrick bought many very exquisite opal jewels.')

I want to be able to full text or fuzzy search through both the title and content fields with words (ie. searching the quixk bronw fox would still return row 1 ['The Brown Fox'])

postgresql sql
1 Answers

Here's my solution after a bit more research:

  1. First install the pg_trgm postgres extension.
CREATE EXTENSION pg_trgm;
  1. After creating the tables create add a column for the tokenized (tsvector) version of the content and add a gin index.
ALTER TABLE documents ADD "document_vectors" tsvector;
CREATE INDEX idx_doc_vec ON documents USING gin(document_vectors);
  1. Update the table to add the tokenized version of the title and content fields to the new document_vectors column.
UPDATE documents
SET document_vectors = (to_tsvector(title) || to_tsvector(content));
  1. Finally, you can now search using the plainto_tsquery() function and rank the search results by how similar they are to your query using the ts_rank_cd() function.
SELECT *
FROM documents
ORDER BY ts_rank_cd(
  document_vectors,
  plainto_tsquery('english', 'some words here')
) Desc
LIMIT 5 OFFSET 0;

This last query will find the 5 most similar records and rank them by similariy.

user_194642060
Related