SQLite因其轻量、易用近年重获关注，支持JSON操作是其亮点之一。文章作者分享使用SQLite的经验，将推出系列博文介绍其强大功能，首篇聚焦JSON虚拟列和索引特性。

SQLite的JSON超能力：虚拟列与索引技术解析

在DB Pro团队，我们对SQLite有着深厚的热爱。这款轻量级数据库近年来正迎来复兴——从衍生出libSQL和Turso等项目，到成为PocketBase等流行后端框架的核心引擎。

经过三个月的深度使用，我们发现了SQLite许多鲜为人知的特性。本文将重点介绍其JSON处理能力的惊艳之处：

1. 原生JSON存储 只需创建包含JSON类型的简单表，即可直接存储原始JSON文档，无需预先定义复杂模式。

2. 虚拟生成列 通过json_extract函数创建虚拟列，这些列不实际存储数据，而是在查询时动态计算。例如： sql ALTER TABLE products ADD COLUMN price REAL GENERATED ALWAYS AS (json_extract(data, '$.price')) VIRTUAL;

3. 高性能索引 为虚拟列添加普通索引后，JSON字段即可获得与传统关系型列相同的查询性能： sql CREATE INDEX idx_products_price ON products(price);

4. 灵活扩展 当需要新增查询字段时，只需追加虚拟列并创建索引，无需数据迁移或模式重构。这种设计完美融合了NoSQL的灵活性与关系型数据库的性能优势。

这项技术彻底改变了我们在SQLite中使用JSON的方式。它允许开发者： - 初期无需确定所有索引策略 - 后期可零成本扩展查询字段 - 保持完整的索引查询速度 - 避免ETL流程的复杂性

未来我们还将分享更多SQLite的隐藏特性。这个优雅的解决方案证明，经过精心调优的SQLite完全能够胜任生产环境需求。

（注：原文中的GIF动图链接及部分口语化表达已根据技术文章特性进行精简处理）

以下是评论内容的总结：

主要观点

1. JSON索引方法的讨论

   • 有评论建议使用"Index On Expression"来索引JSON字段，但指出语法变化可能导致索引失效（评论2）。
   • 另一观点认为生成列（generated columns）是常见做法，尤其在Postgres中用于JSON性能优化（评论6）。

2. 技术细节与疑问

   • 有用户希望看到查询计划的变化以确认索引效果（评论3）。
   • 对创建表时是否一次性定义虚拟列提出疑问（评论11）。

3. 与其他数据库的比较

   • 提到Vertica和Snowflake有类似功能（评论4、8）。
   • 指出MSSQL在2025版本前对JSON支持有限（评论15）。

4. 数据规范化的争议

   • 有开发者呼吁规范化数据而非滥用JSON字段，以方便索引和约束（评论10）。
   • 但也有认为对小数据集（数十万行）使用JSON加生成列是合理选择（评论7）。

5. 使用体验与建议

   • 报告移动端SQL编辑器存在显示和编辑问题（评论5）。
   • 希望支持Postgres的GIN索引以加速未预期键的查询（评论13）。

关键引用

• 关于JSON索引替代方案

  "interesting, but can't you use 'Index On Expression'?"（评论2）
  "I thought this was common practice, generated columns for JSON performance."（评论6）

• 对规范化的呼吁

  "I wish devs would normalize their data rather than shove everything into a JSON(B) column"（评论10）

• 技术疑问

  "Why?"（评论11）
  "I would have loved to see how the query plan changes"（评论3）

• 幽默评价

  "MongoDB is dead, long live MongoDB"（评论14）

认可度

虽然所有评论均未显示评分，但正面评价占多数，如称赞技巧实用（评论12）、感谢性能缓冲方案（评论16）等。