CrateDB's full-text search is built on top of Apache Lucene, a renowned library for text search and indexing. It allows users to search for specific text across one or more columns. In order to use full-text search, a full-text index with an analyzer must be defined while creating the column. This index breaks down the text into searchable tokens, a task performed by the analyzer. Once set up, you can run full-text search queries based on the defined index.
create table documents_a ( title text, body text INDEX using fulltext with (analyzer = 'english') );
-- Find the 10 titles with the highest relevance for the term 'love' SELECT title, _score FROM documents_a WHERE MATCH(body, 'love') ORDER BY _score DESC LIMIT 10;
CrateDB Workshop 2023
Modeling Data in CrateDB