sqlite数据库JSON1扩展概述：

JSON1扩展特点：
├── 内置扩展：sqlite 3.38+默认启用
├── 完整支持：JSON所有操作
├── 高效处理：优化的JSON解析
└── SQL集成：JSON函数与SQL结合

JSON1核心功能：
├── JSON解析与验证
├── JSON路径查询
├── JSON数据提取
├── JSON数据修改
├── JSON数组操作
├── JSON对象操作
└── JSON聚合函数

JSON数据类型支持：
├── null：空值
├── boolean：布尔值
├── number：数字
├── string：字符串
├── array：数组
└── object：对象

版本要求：
├── sqlite 3.38+：默认启用
├── sqlite 3.7.17+：可加载扩展
└── 编译选项：-DSQLITE_ENABLE_JSON1

sqlite数据库JSON函数分类：

1. 提取函数
├── json_extract()：提取JSON值
├── json_value()：提取标量值
└── ->、->>操作符：简化提取

2. 创建函数
├── json_array()：创建JSON数组
├── json_object()：创建JSON对象
└── json_quote()：转义JSON字符串

3. 修改函数
├── json_set()：设置JSON值
├── json_insert()：插入JSON值
├── json_replace()：替换JSON值
├── json_remove()：删除JSON值
└── json_patch()：合并JSON

4. 数组函数
├── json_array_length()：数组长度
├── json_each()：遍历数组/对象
└── json_group_array()：聚合为数组

5. 对象函数
├── json_type()：获取类型
├── json_keys()：获取键列表
└── json_group_object()：聚合为对象

6. 验证函数
├── json_valid()：验证JSON
└── json_error_position()：错误位置

常用函数示例：
— 提取值
SELECT json_extract(‘{“name”:”风哥”,”age”:35}’, ‘$.name’);
— 结果：风哥

— 创建JSON
SELECT json_object(‘name’, ‘风哥’, ‘age’, 35);
— 结果：{“name”:”风哥”,”age”:35}

— 修改JSON
SELECT json_set(‘{“name”:”风哥”}’, ‘$.age’, 35);
— 结果：{“name”:”风哥”,”age”:35}

sqlite数据库JSON路径表达式：

路径语法：
├── $：根元素
├── .key：对象键访问
├── [n]：数组索引访问
├── [*]：数组所有元素
├── ..key：递归查找
└── ?()：过滤表达式

路径示例：
{
“name”: “风哥”,
“age”: 35,
“skills”: [“sqlite”, “mysql”, “oracle”],
“contact”: {
“email”: “fengge@fgedu.net.cn”,
“phone”: “13800138000”
}
}

路径表达式：
├── $：整个JSON
├── $.name：风哥
├── $.age：35
├── $.skills[0]：sqlite
├── $.skills[*]：所有技能
├── $.contact.email：fengge@fgedu.net.cn
└── $.contact.*：所有联系方式

操作符简写：
├── ->：返回JSON类型
├── ->>：返回文本类型
├── data->’$.name’：等价于json_extract(data, ‘$.name’)
└── data->>’$.name’：返回文本

sqlite数据库JSON应用规划：

1. 适用场景
├── 配置数据存储
├── 日志数据存储
├── API响应缓存
├── 动态属性存储
└── 半结构化数据

2. 不适用场景
├── 频繁查询的字段
├── 需要排序的字段
├── 需要关联的字段
├── 大量数据统计
└── 高并发写入

3. 设计原则
├── 常用字段单独存储
├── JSON存储扩展属性
├── 建立必要索引
└── 控制JSON大小

4. 性能考虑
├── JSON解析开销
├── 索引策略
├── 查询优化
└── 存储空间

JSON vs 关系型设计：
┌─────────────────┬────────────────┬────────────────┐
│ 特性 │ JSON存储 │ 关系型存储 │
├─────────────────┼────────────────┼────────────────┤
│ 灵活性 │ 高 │ 低 │
│ 查询性能 │ 较低 │ 高 │
│ 存储效率 │ 较低 │ 高 │
│ 索引支持 │ 有限 │ 完整 │
│ 适用场景 │ 动态数据 │ 固定结构 │
└─────────────────┴────────────────┴────────────────┘

JSON表创建示例：
— 创建用户表（带JSON扩展属性）
CREATE TABLE fgedu_users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
email TEXT NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
profile JSON,
settings JSON
);

— 创建商品表（带JSON规格）
CREATE TABLE fgedu_products (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
price REAL NOT NULL,
category TEXT,
specs JSON,
attributes JSON
);

— 创建日志表（JSON日志）
CREATE TABLE fgedu_logs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
log_time DATETIME DEFAULT CURRENT_TIMESTAMP,
level TEXT,
source TEXT,
data JSON
);

— 插入JSON数据
INSERT INTO fgedu_users (username, email, profile, settings) VALUES
(‘fgedu01’, ‘fgedu01@fgedu.net.cn’,
json_object(‘nickname’, ‘风哥’, ‘age’, 35, ‘city’, ‘北京’),
json_object(‘theme’, ‘dark’, ‘language’, ‘zh-CN’, ‘notifications’, json_object(’email’, 1, ‘sms’, 0)));

INSERT INTO fgedu_products (name, price, category, specs, attributes) VALUES
(‘sqlite教程’, 99.00, ‘教程’,
json_array(
json_object(‘name’, ‘格式’, ‘value’, ‘PDF’),
json_object(‘name’, ‘页数’, ‘value’, 500)
),
json_object(‘author’, ‘风哥’, ‘publisher’, ‘ITPUX’));

— 查询验证
sqlite> SELECT * FROM fgedu_users;
1|fgedu01|fgedu01@fgedu.net.cn|2024-04-08 12:00:00|{“nickname”:”风哥”,”age”:35,”city”:”北京”}|{“theme”:”dark”,”language”:”zh-CN”,”notifications”:{“email”:1,”sms”:0}}

JSON查询示例：
— 提取JSON字段
sqlite> SELECT
…> username,
…> json_extract(profile, ‘$.nickname’) as nickname,
…> json_extract(profile, ‘$.age’) as age,
…> profile->>’$.city’ as city
…> FROM fgedu_users;
fgedu01|风哥|35|北京

— 条件查询
sqlite> SELECT username, email
…> FROM fgedu_users
…> WHERE json_extract(profile, ‘$.age’) > 30;
fgedu01|fgedu01@fgedu.net.cn

— 嵌套JSON查询
sqlite> SELECT username,
…> settings->>’$.theme’ as theme,
…> settings->>’$.notifications.email’ as email_notify
…> FROM fgedu_users;
fgedu01|dark|1

— JSON数组查询
sqlite> SELECT name, price,
…> json_extract(specs, ‘$[0].value’) as format
…> FROM fgedu_products;
sqlite教程|99.0|PDF

— JSON聚合
sqlite> SELECT
…> json_group_array(username) as users,
…> json_group_object(username, email) as user_emails
…> FROM fgedu_users;
[“fgedu01”]|{“fgedu01″:”fgedu01@fgedu.net.cn”}

— JSON遍历
sqlite> SELECT
…> p.name,
…> j.key as spec_name,
…> j.value as spec_value
…> FROM fgedu_products p, json_each(specs) j;
sqlite教程|name|格式
sqlite教程|value|PDF
sqlite教程|name|页数
sqlite教程|value|500

JSON修改示例：
— json_set：设置值（存在则更新，不存在则添加）
sqlite> UPDATE fgedu_users
…> SET profile = json_set(profile, ‘$.age’, 36, ‘$.country’, ‘中国’)
…> WHERE username = ‘fgedu01’;

sqlite> SELECT profile FROM fgedu_users WHERE username = ‘fgedu01’;
{“nickname”:”风哥”,”age”:36,”city”:”北京”,”country”:”中国”}

— json_insert：仅插入（存在则不更新）
sqlite> UPDATE fgedu_users
…> SET profile = json_insert(profile, ‘$.age’, 35, ‘$.gender’, ‘男’)
…> WHERE username = ‘fgedu01’;

sqlite> SELECT profile FROM fgedu_users WHERE username = ‘fgedu01’;
{“nickname”:”风哥”,”age”:36,”city”:”北京”,”country”:”中国”,”gender”:”男”}

— json_replace：仅替换（不存在则不添加）
sqlite> UPDATE fgedu_users
…> SET profile = json_replace(profile, ‘$.age’, 37, ‘$.phone’, ‘13800138000’)
…> WHERE username = ‘fgedu01’;

sqlite> SELECT profile FROM fgedu_users WHERE username = ‘fgedu01’;
{“nickname”:”风哥”,”age”:37,”city”:”北京”,”country”:”中国”,”gender”:”男”}

— json_remove：删除字段
sqlite> UPDATE fgedu_users
…> SET profile = json_remove(profile, ‘$.country’)
…> WHERE username = ‘fgedu01’;

sqlite> SELECT profile FROM fgedu_users WHERE username = ‘fgedu01’;
{“nickname”:”风哥”,”age”:37,”city”:”北京”,”gender”:”男”}

— json_patch：合并JSON
sqlite> UPDATE fgedu_users
…> SET settings = json_patch(settings,
…> json_object(‘theme’, ‘light’, ‘timezone’, ‘Asia/Shanghai’))
…> WHERE username = ‘fgedu01’;

sqlite> SELECT settings FROM fgedu_users WHERE username = ‘fgedu01’;
{“theme”:”light”,”language”:”zh-CN”,”notifications”:{“email”:1,”sms”:0},”timezone”:”Asia/Shanghai”}

配置管理系统：
— 创建配置表
CREATE TABLE fgedu_config (
id INTEGER PRIMARY KEY AUTOINCREMENT,
module TEXT NOT NULL,
key TEXT NOT NULL,
value JSON,
description TEXT,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
UNIQUE(module, key)
);

— 插入配置
INSERT INTO fgedu_config (module, key, value, description) VALUES
(‘database’, ‘connection’,
json_object(‘host’, ‘192.168.1.100’, ‘port’, 3306, ‘name’, ‘fgedudb’),
‘数据库连接配置’),
(‘cache’, ‘redis’,
json_object(‘host’, ‘192.168.1.101’, ‘port’, 6379, ‘db’, 0),
‘Redis缓存配置’),
(‘app’, ‘features’,
json_array(‘feature1’, ‘feature2’, ‘feature3’),
‘启用的功能列表’);

— 查询配置
sqlite> SELECT module, key, json_pretty(value) as value FROM fgedu_config;
database|connection|{
“host”: “192.168.1.100”,
“port”: 3306,
“name”: “fgedudb”
}
cache|redis|{
“host”: “192.168.1.101”,
“port”: 6379,
“db”: 0
}

— 更新配置
UPDATE fgedu_config
SET value = json_set(value, ‘$.port’, 3307),
updated_at = CURRENT_TIMESTAMP
WHERE module = ‘database’ AND key = ‘connection’;

— 验证更新
sqlite> SELECT value->>’$.port’ FROM fgedu_config WHERE module = ‘database’;
3307

sqlite数据库JSON最佳实践：

1. 设计原则
├── 常用字段单独存储
├── JSON存储动态属性
├── 控制JSON大小
└── 合理使用索引

2. 查询优化
├── 使用生成列建索引
├── 避免全JSON扫描
├── 缓存常用查询
└── 使用json_valid验证

3. 性能考虑
├── JSON解析开销
├── 存储空间占用
├── 更新效率
└── 索引策略

4. 使用建议
├── 配置数据适合JSON
├── 日志数据适合JSON
├── 频繁查询不适合
└── 大数据量不适合