SQLite 的 JSON 扩展如何使用？

SQLite 的 JSON 扩展提供了处理 JSON 数据的能力：

1. JSON 扩展概述

   • SQLite 3.38.0+ 内置 JSON1 扩展
   • 提供了一组函数用于创建、查询和操作 JSON 数据
   • JSON 数据以 TEXT 类型存储在数据库中

2. JSON 函数分类

   • 创建函数：json_array()、json_object()
   • 提取函数：json_extract()、json_each()
   • 修改函数：json_set()、json_remove()
   • 查询函数：json_array_length()、json_type()
   • 路径函数：json_patch()、json_tree()

3. 创建 JSON 数据

   sql
   -- 创建 JSON 对象
   SELECT json_object('name', 'Alice', 'age', 30);
   -- 输出: {"name":"Alice","age":30}
   
   -- 创建 JSON 数组
   SELECT json_array(1, 2, 3, 'four');
   -- 输出: [1,2,3,"four"]

4. 提取 JSON 数据

   sql
   -- 提取 JSON 字段
   SELECT json_extract('{"name":"Alice","age":30}', '$.name');
   -- 输出: "Alice"
   
   -- 提取嵌套字段
   SELECT json_extract('{"user":{"name":"Alice"}}', '$.user.name');
   -- 输出: "Alice"

5. 修改 JSON 数据

   sql
   -- 设置或更新 JSON 字段
   SELECT json_set('{"name":"Alice"}', '$.age', 30);
   -- 输出: {"name":"Alice","age":30}
   
   -- 删除 JSON 字段
   SELECT json_remove('{"name":"Alice","age":30}', '$.age');
   -- 输出: {"name":"Alice"}

6. 查询 JSON 数据

   sql
   -- 获取 JSON 数组长度
   SELECT json_array_length('[1,2,3,4]');
   -- 输出: 4
   
   -- 获取 JSON 字段类型
   SELECT json_type('{"name":"Alice","age":30}', '$.age');
   -- 输出: integer

7. 在表中使用 JSON

   sql
   -- 创建包含 JSON 字段的表
   CREATE TABLE users (
       id INTEGER PRIMARY KEY,
       data TEXT
   );
   
   -- 插入 JSON 数据
   INSERT INTO users (data) VALUES (json_object('name', 'Alice', 'tags', json_array('admin', 'user')));
   
   -- 查询 JSON 数据
   SELECT json_extract(data, '$.name') as name FROM users;
   
   -- 基于 JSON 字段过滤
   SELECT * FROM users WHERE json_extract(data, '$.name') = 'Alice';

8. JSON 路径表达式

   • $：根对象
   • .key：对象属性
   • [index]：数组索引
   • *：通配符
   • ..：递归下降

9. 性能考虑

   • JSON 查询比直接查询列慢
   • 可以使用生成列和索引优化 JSON 查询

   sql
   CREATE TABLE users (
       id INTEGER PRIMARY KEY,
       data TEXT,
       name TEXT GENERATED ALWAYS AS (json_extract(data, '$.name')) STORED
   );
   CREATE INDEX idx_name ON users(name);

10. 使用场景

    • 存储灵活的数据结构
    • 存储配置信息
    • 存储日志和元数据
    • 实现 NoSQL 风格的数据存储

JSON 扩展使 SQLite 能够处理半结构化数据，增强了数据库的灵活性。