Highlighting

​

Overview

Highlighting refers to the practice of visually emphasizing the portions of a document that match a user’s search query. Highlighted snippets of text are by default wrapped in <b></b> tags. This can be modified with the prefix and postfix arguments.

​

Basic Usage

The snippet function takes a query and returns a table with three columns: the key_field of each result, its BM25 score, and a highlighted snippet.

SELECT * FROM <index_name>.snippet(
  '<query>',
  highlight_field => '<highlight_field>'
);

SELECT * FROM search_idx.snippet(
  'description:keyboard',
  highlight_field => 'description',
  max_num_chars => 100
);

​

Joining Snippets

Because snippet generates new score_bm25 and snippet columns, the function does not return all the columns of the original table. To map the columns of the original table the output of snippet, perform a JOIN on the key_field column.

-- Here, the key_field is "id"
WITH snippets AS (
    SELECT * FROM search_idx.snippet(
      'description:keyboard',
      highlight_field => 'description'
    )
)
SELECT snippets.id, description, snippet, score_bm25
FROM snippets
LEFT JOIN mock_items ON snippets.id = mock_items.id;

 id |       description        |             snippet             | score_bm25
----+--------------------------+---------------------------------+------------
  2 | Plastic Keyboard         | Plastic <b>Keyboard</b>         |  3.2668595
  1 | Ergonomic metal keyboard | Ergonomic metal <b>keyboard</b> |  2.8213787
(2 rows)