SQLite JSON 函数：json_extract、json_set 与 json_each

SQLite 没有 JSON 类型，这没关系

SQLite 没有专门的 JSON 列类型。JSON 存放在普通的 TEXT 列里，由一组内置函数（统称为 JSON1 扩展）负责解析、查询和修改。所有现代 SQLite 版本都自带 JSON1，所以什么都不用安装。

可以这样理解：把文档存成文本，用函数去查看里面的内容。

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    data TEXT
);

INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com", "tags": ["admin", "beta"]}'),
    ('{"name": "Boris", "email": "boris@example.com", "tags": ["beta"]}');

SELECT id, data FROM users;

两行数据，每行都在一个普通文本列里存放一个 JSON 文档。现在我们需要办法深入这些文档内部。

用 json_extract 和 ->> 提取字段

json_extract(column, path) 从 JSON 文档中取出一个值。路径以 $（根）开头，用 .field 表示对象的键，用 [i] 表示数组下标。

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com", "tags": ["admin", "beta"]}'),
    ('{"name": "Boris", "email": "boris@example.com", "tags": ["beta"]}');

SELECT
    json_extract(data, '$.name')     AS name,
    json_extract(data, '$.tags[0]')  AS first_tag
FROM users;

到处写 json_extract(data, '$.name') 很快就会让人厌烦，所以 SQLite 提供了两个运算符：

• -> 返回 JSON 编码的值（字符串会带着引号）。
• ->> 返回 SQL 值（文本或数字，不带引号）。

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "tags": ["admin", "beta"]}');

SELECT
    data -> '$.name'      AS name_json,
    data ->> '$.name'     AS name_text,
    data ->> '$.tags[0]'  AS first_tag
FROM users;

name_json 返回的是 "Ada"（仍是 JSON），name_text 返回的是 Ada。要拿值做比较或显示时用 ->>。要把结果交给另一个 JSON 函数时用 ->。

按 JSON 字段过滤

能提取，就能过滤。表达式和其他条件一样写在 WHERE 子句中：

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com", "active": 1}'),
    ('{"name": "Boris", "email": "boris@example.com", "active": 0}'),
    ('{"name": "Cleo", "email": "cleo@example.com", "active": 1}');

SELECT data ->> '$.name' AS name
FROM users
WHERE data ->> '$.active' = 1;

这样可以运行，但只要表有点规模就会很慢：为了判断条件，每一行都得解析一遍。稍后我们会用索引解决这个问题。

构建 JSON：json_object 和 json_array

反过来，你也可以在查询中构造 JSON：

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);
INSERT INTO users (name, email) VALUES
    ('Ada', 'ada@example.com'),
    ('Boris', 'boris@example.com');

SELECT json_object(
    'id', id,
    'name', name,
    'email', email
) AS user_json
FROM users;

json_object('k1', v1, 'k2', v2, ...) 构建一个对象。json_array(v1, v2, ...) 构建一个数组。直接在 SQL 中拼装 API 响应时它们很方便，而且可以随意嵌套：

SELECT json_object(
    'user', json_object('name', 'Ada', 'id', 1),
    'roles', json_array('admin', 'beta')
) AS payload;

更新 JSON：json_set、json_insert、json_replace

三个密切相关的函数可以修改 JSON 文档并返回新版本：

• json_set(doc, path, value)：设置路径的值，路径不存在就创建，存在就覆盖。
• json_insert(doc, path, value)：只在路径不存在时插入。
• json_replace(doc, path, value)：只在路径已存在时更新。

这些函数不会就地修改，而是返回一个新文档，通常你要用 UPDATE 把它写回去：

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com"}');

UPDATE users
SET data = json_set(data, '$.email', 'ada@new.com', '$.verified', 1)
WHERE id = 1;

SELECT data FROM users;

注意，json_set 一次调用就可以接收多组路径和值。要删除一个键，用 json_remove(doc, path)。

用 json_each 展开数组

json_each 是一个表值函数：它接收一个 JSON 数组（或对象），为每个元素返回一行。这样一来，“找出带有 admin 标签的用户”这种用普通 SQL 很别扭的需求，就变成了一个普通的连接：

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "tags": ["admin", "beta"]}'),
    ('{"name": "Boris", "tags": ["beta"]}'),
    ('{"name": "Cleo", "tags": ["admin"]}');

SELECT u.data ->> '$.name' AS name, t.value AS tag
FROM users u, json_each(u.data, '$.tags') t
WHERE t.value = 'admin';

users 中的每一行都会和它的 tags 数组的元素做连接。json_each 提供了一些有用的列，包括 key、value、type 和 fullkey。它的兄弟函数 json_tree 会递归遍历整个文档，包括每一个嵌套节点，适合在结构未知的文档中搜索。

给 JSON 字段建索引

上面的 WHERE data ->> '$.active' = 1 查询可以运行，但 SQLite 必须解析每一行才能判断条件。对于经常查询的字段，可以建一个表达式索引：

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);

CREATE INDEX idx_users_email
    ON users(json_extract(data, '$.email'));

INSERT INTO users (data) VALUES
    ('{"email": "ada@example.com"}'),
    ('{"email": "boris@example.com"}');

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE json_extract(data, '$.email') = 'ada@example.com';

索引必须使用和查询完全相同的表达式。如果索引里用的是 json_extract(data, '$.email')，查询里用的却是 data ->> '$.email'，两者就对不上，索引会闲置不用：选定一种写法并坚持下去。

对于需要频繁查询的字段，用生成列会更易读：

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    data TEXT,
    email TEXT GENERATED ALWAYS AS (data ->> '$.email') VIRTUAL
);

CREATE INDEX idx_users_email ON users(email);

INSERT INTO users (data) VALUES ('{"email": "ada@example.com"}');

SELECT email FROM users WHERE email = 'ada@example.com';

对写查询的人来说，email 就像一个普通列，但它会自动和 JSON 保持同步。

校验 JSON

json_valid(text) 在文本能被解析为 JSON 时返回 1，否则返回 0。配合 CHECK 约束，就能在写入时拒绝错误的数据：

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    payload TEXT CHECK (json_valid(payload))
);

INSERT INTO events (payload) VALUES ('{"type": "click"}');
INSERT INTO events (payload) VALUES ('not json');

第一条插入成功；第二条会因约束错误而失败。如果没有这个检查，格式错误的 JSON 会安静地躺在表里，直到几个月后某次 json_extract 调用突然报错。

JSON 与 JSONB

从 SQLite 3.45 开始，有了一种叫 JSONB 的二进制表示：数据相同，但预先解析成紧凑的二进制形式，这样函数就不用每次调用都重新解析。jsonb_* 系列函数（jsonb_extract、jsonb_set、jsonb_object 等）返回的是 JSONB 而不是文本，JSONB 列也可以用同样的运算符查询。

CREATE TABLE docs (id INTEGER PRIMARY KEY, body BLOB);

INSERT INTO docs (body) VALUES
    (jsonb('{"name": "Ada", "score": 99}'));

SELECT body ->> '$.name' AS name FROM docs;

如果你希望文档在转储中便于阅读、方便检查，就用普通 JSON（文本）。当表很大、查询频繁，并且解析开销确实在性能分析中显现出来时，再考虑 JSONB。不要默认就切换：调试时，普通 JSON 的可读性价值很大。

什么时候适合用 JSON

JSON 列在这些情况下很出色：

• 每行数据的结构各不相同（比如事件负载、审计日志、集成 webhook）。
• 你在缓存外部 API 的响应，想完整保留它。
• 某个字段很少被查询，几乎从不用来过滤。

在这些情况下就不太合适：

• 你用 JSON 来逃避设计表结构。如果每一行都有相同的字段，那它们就应该是列。
• 你需要经常按某个值过滤或连接。带索引的真实列每次都会比 JSON 路径查找更快。
• 你本来会用到外键。JSON 没有关系完整性。

最理想的做法是两者结合：用标量列存放驱动查询和约束的字段，再加一个 JSON 列存放长尾的可变数据。

下一步：全文搜索

JSON 让你在存储方面更加灵活。下一页讲的是 FTS5，即 SQLite 的全文搜索引擎，它提供带排序和高亮的真正文本搜索，远远超出 LIKE 的能力。