You are viewing a free preview of this lesson.
Subscribe to unlock all 10 lessons in this course and every other course on LearningBro.
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.
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.
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;
-- 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*';
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.
The JSON1 extension provides functions to create, query, and modify JSON stored as TEXT in a SQLite column.
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';
-- 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';
SELECT json_each.value AS tag
FROM events, json_each(json_extract(events.data, '$.tags'))
WHERE events.id = 1;
Subscribe to continue reading
Get full access to this lesson and all 10 lessons in this course.