Extracting Multiple Snippets From Single FTS5 Column in SQLite

Understanding FTS5 Snippet Function Limitations for Multi-Highlight Extraction

The challenge of retrieving multiple highlighted snippets from a single FTS5 virtual table column arises from the inherent design of SQLite’s Full-Text Search (FTS5) engine. When a user queries an FTS5 table using the snippet auxiliary function, the system returns a single contiguous excerpt from the specified column that best matches the search terms. This limitation persists even when the column contains multiple non-overlapping regions that satisfy the query. The problem stems from the interaction between FTS5’s tokenization process, match ranking algorithms, and the snippet generation logic. While the default behavior serves most use cases by prioritizing the most relevant match, applications requiring visual representation of all query matches in long text fields face functional gaps. This scenario becomes critical in document search systems, legal text analysis, or academic research tools where comprehensive match visualization is essential.

FTS5 Snippet Generation Mechanics and Match Prioritization Constraints

The inability to produce multiple snippets from a single FTS5 column originates from three architectural layers in SQLite’s implementation. First, the token boundary alignment system in FTS5 operates on a first-match-wins basis during snippet generation. When the snippet function processes a column, it identifies the span of tokens that yields the highest relevance score using the BM25 ranking algorithm. This score calculation incorporates factors like term frequency and inverse document frequency but doesn’t maintain a list of all qualified match positions.

Second, the snippet windowing algorithm uses greedy selection to maximize the highlighted term density within the configured snippet length. Once the optimal window is chosen, the system discards information about secondary match regions that fall outside this window. This occurs during the internal call to the fts5SnippetScore C-language function, which evaluates potential snippet windows but doesn’t retain a sorted list of candidate windows.

Third, the API limitations in SQLite’s FTS5 extension framework restrict custom snippet generators from accessing the full list of match positions. While the FTS5 API provides hooks for custom tokenizers and auxiliary functions, the current interface doesn’t expose the complete array of match offsets needed to implement multi-snippet extraction. This creates a dependency loop where enhanced snippet generation would require modifications to both the match position tracking system and the snippet scoring logic.

Implementing Multi-Snippet Extraction Through FTS5 Customization

Custom Snippet Function via FTS5 API Extension

Developers can bypass the single-snippet limitation by implementing a custom auxiliary function that extends the native snippet functionality. This requires writing a C extension that interacts with SQLite’s FTS5 API to access match position data. The implementation involves four key phases:

1. Match Position Enumeration: Override the xTokenize method to capture all token offsets where query terms appear in the column. Store these positions in a structure accessible during snippet generation.

typedef struct MatchPositions {
  int iStart;     // Starting byte offset of match
  int iEnd;       // Ending byte offset of match
  double score;   // Relevance score for this match
} MatchPositions;

static int fts5CustomTokenize(
  Fts5Tokenizer *pTokenizer,
  void *pCtx,
  int flags,
  const char *pText, int nText,
  int (*xToken)(void*, int, const char*, int, int, int)
){
  // Tokenization logic that records positions
  // into MatchPositions array
}

2. Window Selection Algorithm: Implement a modified version of fts5SnippetScore that selects multiple non-overlapping windows with the highest aggregate scores. Use a sliding window approach with configurable gap sizes between snippets.

3. Snippet Assembly: After selecting optimal windows, generate concatenated snippets with ellipsis markers between them. Ensure proper handling of UTF-8 boundaries and HTML/XML entity escaping if required by the application.

4. SQL Function Registration: Expose the custom snippet generator as a new SQL function using sqlite3_create_function_v2, enabling queries like:

SELECT multi_snippet(column, 3, '[', ']', '...', 64) FROM fts_table WHERE ...;

Query-Time Snippet Multiplication via JOIN Patterns

For environments where native code extensions aren’t feasible, a pure-SQL workaround leverages multiple correlated subqueries to simulate multi-snippet extraction. This method exploits FTS5’s offsets auxiliary function to manually identify match positions:

WITH matches AS (
  SELECT rowid, offsets(fts_table) AS off
  FROM fts_table
  WHERE fts_table MATCH 'search term'
)
SELECT 
  (SELECT snippet(fts_table, 1, '[', ']', '...', 8) 
   FROM fts_table 
   WHERE rowid = m.rowid 
   AND substr(content, 
     json_extract(m.off, '$[2]')+1, 
     json_extract(m.off, '$[3]')) LIKE '%term%'
   LIMIT 1
  ) AS snippet1,
  (SELECT snippet(fts_table, 1, '[', ']', '...', 8) 
   FROM fts_table 
   WHERE rowid = m.rowid 
   AND substr(content, 
     json_extract(m.off, '$[6]')+1, 
     json_extract(m.off, '$[7]')) LIKE '%term%'
   LIMIT 1
  ) AS snippet2
FROM matches m;

This approach parses the offsets function output (which returns match positions as a JSON array) and uses substring operations to force the snippet function to target different regions. While fragile and performance-intensive, it provides a temporary solution for small datasets.

FTS5 Contentless Table Optimization for Position Tracking

When using FTS5’s contentless tables (content=""), modify the external content storage to include precomputed text segmentation markers. By inserting delimiter tokens at strategic positions (e.g., paragraph boundaries), you can trick the snippet generator into treating each segment as a separate pseudo-column:

1. Preprocess text to insert Unicode Unit Separator (U+001F) between paragraphs:

processed_text = text.replace('\n\n', '\x1F')

2. Create FTS5 table with custom tokenizer that treats the unit separator as a token boundary:

CREATE VIRTUAL TABLE fts_contentless USING fts5(
  content,
  content='',
  tokenize='unicode61 separators "\x1F"'
);

3. Query using snippet with increased token count to capture multiple segments:

SELECT snippet(fts_contentless, 0, '', '', ' ... ', 64) 
FROM fts_contentless 
WHERE fts_contentless MATCH 'term' 
AND LENGTH(COALESCE(snippet(...), '')) > 0;

This structure allows the snippet function to wrap around the artificial segment boundaries, effectively producing multiple highlighted regions when matches span adjacent segments. Combine with GROUP_CONCAT and json_each to aggregate results:

SELECT 
  (SELECT GROUP_CONCAT(snippet_part, ' ... ') 
   FROM (
     SELECT snippet(fts_contentless, 0, '', '', '', 32) AS snippet_part 
     FROM fts_contentless 
     WHERE rowid = m.rowid 
     AND INSTR(content, char(0x1F)) BETWEEN start AND end 
     LIMIT 3
   )
  ) AS combined_snippets
FROM matches m;

Each method carries distinct performance and accuracy trade-offs. Native C extensions provide optimal speed and flexibility but require SQLite recompilation. SQL-level workarounds avoid native code but suffer from exponential complexity growth with document size. Contentless table optimizations offer a middle ground but demand careful preprocessing pipeline management. For mission-critical deployments, a hybrid approach combining precomputed match positions with runtime snippet assembly delivers the best balance between responsiveness and completeness.