SQLite JSON 扩展怎么用，什么时候该拆列或建索引？

SQLite JSON 扩展适合处理“结构有变化，但还没必要拆成一堆表”的数据，比如配置、埋点属性、第三方回调、用户偏好。它让 SQLite 能直接创建、校验、提取和修改 JSON，但不要因此把 SQLite 当成完整的文档数据库。更稳的设计是：原始 JSON 保留一份，稳定且高频查询的字段提成列或生成列，真正的关系和约束仍然交给普通表结构。这样既能接住变化，也不会让每次查询都在一大段文本里反复解析。

sql
CREATE TABLE webhooks (
  id INTEGER PRIMARY KEY,
  payload TEXT NOT NULL CHECK (json_valid(payload)),
  event_type TEXT GENERATED ALWAYS AS (json_extract(payload, '$.type')) STORED
);
CREATE INDEX idx_webhooks_event_type ON webhooks(event_type);

实际落地时，可以把 JSON 字段分成三类：原样留存的审计数据、偶尔展示的扩展属性、必须参与查询的业务字段。第一类只要校验合法性，第二类用函数读取即可，第三类最好拆列或用生成列加索引。这个分层能避免两个极端：一边是过早把所有字段拆得很碎，另一边是所有查询都压在 json_extract 上。

还有一个边界是兼容性，线上环境、移动端系统库和开发机的 SQLite 版本可能不一致，JSON 操作符和函数支持范围要提前确认。

这点很容易漏掉。

追问

SQLite 里的 JSON 到底是什么类型？

大多数情况下，JSON 在 SQLite 里仍然以 TEXT 保存，JSON 函数负责校验、解析和提取。它不像某些服务端数据库的 JSONB 那样天然带有专门存储格式和复杂索引能力，所以不能期待任意深层路径查询都很快。取舍在数据规模和访问方式：少量配置、低频展示没问题；百万级事件表按 JSON 字段筛选，就要提前设计索引。踩坑是没有加 CHECK (json_valid(payload))，脏数据进入表后，某个后台任务执行到 json_extract 才突然失败。边界也很清楚：需要外键、唯一约束、范围查询的核心字段，不要长期只放在 JSON 里。

json_extract、-> 和 ->> 应该怎么选？

json_extract() 最通用，路径写法明确，兼容大量旧代码和示例。->、->> 写起来更短，风格接近其他数据库，->> 更适合拿标量文本值来比较或展示。取舍主要看 SQLite 版本和团队习惯：如果要兼容旧环境，用函数更稳；如果版本可控，操作符能让简单查询更清爽。踩坑是返回值类型容易被忽略，JSON 字符串、数字和 JSON 文本在比较时并不总是按你想的方式转换。建议在关键过滤条件里显式保持类型一致，必要时用 CAST 或生成列固定下来。

sql
SELECT
  json_extract(payload, '$.user.id') AS user_id,
  payload ->> '$.type' AS event_type
FROM webhooks
WHERE payload ->> '$.type' = 'payment.succeeded';

JSON 数组怎么展开查询？

数组通常用 json_each() 或 json_tree() 展开，它们会把 JSON 内容临时变成可查询的行。json_each() 适合展开一层数组或对象，json_tree() 会递归遍历更深结构，但成本也更高。取舍是查询表达力和性能：临时分析可以直接展开，线上接口最好先用普通条件缩小范围再展开。踩坑是对大表无条件展开数组，一行十个标签就会把结果放大十倍，排序和分页都可能变慢。边界是高频标签、分类、权限这类字段，如果经常被筛选，拆成关联表通常比每次展开 JSON 更可靠。

sql
SELECT w.id, tag.value AS tag
FROM webhooks AS w, json_each(w.payload, '$.tags') AS tag
WHERE w.event_type = 'article.updated'
  AND tag.value = 'sqlite';

JSON 字段过滤慢应该怎么优化？

先确认这个字段是否稳定、是否真的高频查询。如果稳定，可以用生成列提取并建索引；如果只是内部优化，也可以建表达式索引。生成列的好处是字段含义清楚，多处查询能复用；表达式索引少一个可见列，但要求查询表达式和索引表达式保持一致。踩坑是路径、类型转换或函数写法稍有不同，查询计划可能不用索引，所以优化后一定要看 EXPLAIN QUERY PLAN。边界是 JSON 深层结构频繁变化时，过早建很多索引会拖慢写入，还会让迁移非常麻烦。

sql
CREATE INDEX idx_webhooks_user_id
ON webhooks(json_extract(payload, '$.user.id'));

EXPLAIN QUERY PLAN
SELECT * FROM webhooks
WHERE json_extract(payload, '$.user.id') = 1001;

什么时候不该把数据塞进 JSON？

强关系、强约束、高频 JOIN 的数据不适合长期放在 JSON 里，比如订单金额、库存数量、用户主键关系。JSON 很适合接住变化快的边缘字段，但不适合替代表设计，否则后面会在校验、索引、统计和迁移上付出更多成本。取舍可以按问题判断：这个字段要不要被约束、排序、聚合、关联？如果答案经常是“要”，它就应该是列或关联表。踩坑是早期为了省事全塞 JSON，后期报表和风控要查时，只能在一堆文本函数里补性能。把 JSON 当缓冲区，而不是当所有数据的最终归宿，通常更稳。