Full-Text Search and JSON

SQLite ships with two powerful built-in extensions that extend its capabilities well beyond basic relational queries: the FTS5 full-text search engine and the JSON1 functions for storing and querying JSON documents. Both are available in standard SQLite builds without any additional installation.

Full-Text Search with FTS5

FTS5 (Full-Text Search version 5) is a virtual table module that creates an inverted index over text columns, enabling fast keyword searches across large collections of documents.

Creating an FTS5 Table

CREATE VIRTUAL TABLE articles_fts USING fts5(
  title,
  body,
  content='articles',   -- optional: link to a real table
  content_rowid='id'
);

-- Populate from the content table
INSERT INTO articles_fts (rowid, title, body)
SELECT id, title, body FROM articles;

Searching with MATCH

-- Simple keyword search
SELECT title FROM articles_fts WHERE articles_fts MATCH 'sqlite';

-- Phrase search
SELECT title FROM articles_fts WHERE articles_fts MATCH '"full text search"';

-- Boolean operators
SELECT title FROM articles_fts WHERE articles_fts MATCH 'sqlite AND performance';
SELECT title FROM articles_fts WHERE articles_fts MATCH 'index OR query';

-- Prefix search
SELECT title FROM articles_fts WHERE articles_fts MATCH 'trans*';

Ranking Results with bm25

FTS5 provides a bm25() function that scores results by relevance:

SELECT title, bm25(articles_fts) AS score
FROM articles_fts
WHERE articles_fts MATCH 'database engine'
ORDER BY score;

Lower (more negative) bm25 scores indicate higher relevance.

JSON Functions

The JSON1 extension provides functions to create, query, and modify JSON stored as TEXT in a SQLite column.

Storing and Extracting JSON

CREATE TABLE events (
  id   INTEGER PRIMARY KEY,
  data TEXT NOT NULL   -- JSON stored as TEXT
);

INSERT INTO events (data) VALUES
  ('{"type": "click", "user_id": 42, "page": "/home"}'),
  ('{"type": "purchase", "user_id": 7, "amount": 49.99}');

-- Extract a top-level field
SELECT json_extract(data, '$.type')    AS event_type FROM events;
SELECT json_extract(data, '$.user_id') AS user_id    FROM events;

-- Filter by a JSON field
SELECT * FROM events
WHERE json_extract(data, '$.type') = 'purchase';

Modifying JSON

-- Add or replace a key
UPDATE events
SET data = json_set(data, '$.processed', 1)
WHERE json_extract(data, '$.type') = 'purchase';

-- Remove a key
UPDATE events
SET data = json_remove(data, '$.page')
WHERE json_extract(data, '$.type') = 'purchase';

JSON Arrays

SELECT json_each.value AS tag
FROM events, json_each(json_extract(events.data, '$.tags'))
WHERE events.id = 1;

Generated Columns for JSON Indexing