SQLite 中的 JSON 支持：从 JSON 存储到查询与操作的完整指南

1. SQLite与JSON的奇妙邂逅

当传统的表格结构遇上灵活的JSON数据，SQLite展现了独特的能力。自从3.9.0版本引入JSON1扩展以来，这个轻量级数据库就具备了处理JSON数据的完整能力。想象一个移动应用需要同时存储结构化数据和可变配置项的场景：用户的基本信息可以放在常规字段，而个性化设置、动态扩展属性等非结构化数据使用JSON字段存储，正是SQLite JSON支持最典型的用武之地。

2. 搭建你的JSON实验环境

（技术栈：SQLite 3.37.2 + JSON1扩展）

-- 创建测试用表（包含JSON字段）
CREATE TABLE user_data (
    id INTEGER PRIMARY KEY,
    profile JSON,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 启用JSON1扩展（现代SQLite版本默认已启用）
SELECT json_extract('{"test":true}', '$.test');

这里我们创建一个包含JSON类型的字段表。虽然在SQLite中JSON实质是TEXT类型，但通过特定的函数操作可以识别其结构。值得注意的是，JSON字段类型声明（如上述JSON类型）在SQLite中实际上只是语义标记，真正发挥作用的是JSON处理函数。

3. 全面掌握JSON基础操作

3.1 JSON数据写入的多种姿势

-- 直接插入JSON字符串
INSERT INTO user_data (id, profile) VALUES 
(1, '{"name":"张三","age":30,"prefs":{"theme":"dark","notify":true}}');

-- 使用json函数构造复杂结构
INSERT INTO user_data (id, profile)
VALUES (2, json_object(
    'name', '李四',
    'age', json(25),
    'tags', json_array('vip', 'early'),
    'metadata', json('{"source":"mobile","version":2.3}')
));

json_object和json_array函数可以帮助构建规范的JSON结构，自动处理特殊字符的转义，比手动拼接字符串更安全可靠。

3.2 JSON字段的深度查询

-- 基础路径查询
SELECT 
    json_extract(profile, '$.name') as username,
    json_extract(profile, '$.prefs.theme') as theme
FROM user_data;

-- 简写语法（->运算符）
SELECT 
    profile->'name' as user_name,
    profile->'prefs'->'notify' as notify_setting
FROM user_data;

-- 数组元素提取
SELECT json_extract('["苹果","香蕉","橙子"]', '$[1]');  -- 返回"香蕉"

注意->返回的是JSON格式值，而->>运算符可以提取标量值：

SELECT profile->>'name' as user_name_str FROM user_data;

4. 高级JSON操作技巧

4.1 动态修改JSON内容

-- 修改用户主题设置
UPDATE user_data
SET profile = json_set(profile, '$.prefs.theme', 'light')
WHERE id = 1;

-- 添加新的配置项
UPDATE user_data
SET profile = json_set(profile, '$.prefs.volume', 70)
WHERE json_extract(profile, '$.name') = '李四';

-- 删除数组元素
UPDATE user_data
SET profile = json_remove(profile, '$.tags[0]')
WHERE id = 2;

json_set函数在目标路径不存在时会自动创建嵌套结构，这对动态扩展数据字段非常有用。

4.2 进阶查询与性能优化

-- 条件查询（注意使用json_quote处理字符串）
SELECT *
FROM user_data
WHERE json_extract(profile, '$.age') > 25 
  AND json_extract(profile, '$.prefs.notify') = 1;

-- 创建虚拟列提高查询性能
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    info JSON,
    price GENERATED ALWAYS AS (json_extract(info, '$.price'))
);

-- 建立表达式索引
CREATE INDEX idx_age ON user_data(json_extract(profile, '$.age'));

生成列和表达式索引可以有效提升JSON字段的查询效率，特别是在频繁查询某个特定路径时效果显著。

5. 关联技术的深度整合

5.1 结合全文搜索

-- 创建虚拟表（需要FTS5扩展）
CREATE VIRTUAL TABLE product_search USING fts5(name, json_data);

-- 插入示例数据
INSERT INTO product_search VALUES 
('手机', '{"specs":{"cpu":"骁龙888","ram":"8GB"}}');

-- 联合JSON查询与全文搜索
SELECT * 
FROM product_search 
WHERE json_extract(json_data, '$.specs.cpu') = '骁龙888'
  AND product_search MATCH '手机';

这种组合实现了结构化查询和非结构化搜索的完美结合，特别适合需要同时处理多种数据形态的应用场景。

5.2 高效聚合分析

-- 使用json_each展开数组
SELECT 
    json_extract(value, '$.product') as product,
    sum(json_extract(value, '$.qty')) as total_qty
FROM orders, json_each(orders.items)
GROUP BY product;

-- 统计JSON数组长度
SELECT 
    id,
    json_array_length(profile->'tags') as tag_count 
FROM user_data;

这些技巧在处理包含数组结构的JSON数据时非常有用，能够实现类似NoSQL数据库的聚合分析功能。

6. 关键应用场景解析

1. 移动应用数据存储：处理用户动态配置、应用状态持久化
2. IoT设备记录：存储传感器采集的异构数据格式
3. 动态表单系统：保存结构不固定的表单数据
4. 日志分析系统：存储含上下文信息的日志条目
5. 内容管理系统：管理复杂的内容属性集合

7. 技术优缺点全景分析

优势亮点：

• 零配置使用，无需额外服务
• 结构化与非结构化数据统一存储
• JSON操作支持原生索引加速
• 兼容传统SQL查询语法

潜在挑战：

• 深层嵌套查询性能衰减
• 不支持完整JSON Schema验证
• 数据修改需要全量更新
• 最大嵌套深度限制（默认2000层）

8. 开发实践的黄金法则

1. 路径索引预计算：对高频查询路径建立表达式索引
2. 数据版本控制：添加元数据版本号字段
3. 验证约束设计：

-- 添加JSON有效性检查约束
ALTER TABLE user_data ADD CHECK (json_valid(profile));

4. 业务分层处理：尽量在应用层处理复杂JSON逻辑
5. 定期优化策略：适时执行VACUUM命令整理碎片

9. 未来发展与生态展望

随着SQLite 3.45.0引入的JSONB支持，二进制JSON格式显著提升了存储效率。现代框架如SQLAlchemy已增加对SQLite JSON功能的原生支持，TypeORM等工具也提供了便捷的JSON字段装饰器。预计未来版本的改进将集中在性能优化和更智能的查询计划生成上。