SQLite JSON1 扩展函数完整指南

要做什么？
│
├─ 创建 JSON ──────────────── json_object / json_array / json_quote
│
├─ 读取值 ─────────────────── json_extract / -> / ->>
│
├─ 检查类型 ───────────────── json_type
│
├─ 数组长度 ───────────────── json_array_length
│
├─ 遍历展开 ───────────────── json_each（一层）/ json_tree（递归）
│
├─ 更新 ──────┬─ 存在覆盖，不存在新建 → json_set
│             ├─ 仅替换已有字段 → json_replace
│             ├─ 仅插入新字段 → json_insert
│             └─ 部分合并 → json_patch
│
├─ 删除路径 ───────────────── json_remove
│
├─ 验证 ──────┬─ 有效性检查 → json_valid
│             └─ 错误定位 → json_error_position
│
├─ 美化/格式化 ────────────── json_pretty / json / jsonb
│
├─ 聚合 ──────┬─ 数组 → json_group_array
│             └─ 对象 → json_group_object
│
└─ 高性能 ─────────────────── jsonb_* 系列

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  info TEXT
);

INSERT INTO users VALUES
(1, '{"name":"Alice","age":30,"skills":["Python","SQL"],"address":{"city":"Beijing","zip":"100000"},"active":true}'),
(2, '{"name":"Bob","age":25,"skills":["Java","C++"],"address":{"city":"Shanghai","zip":"200000"},"active":false}'),
(3, '{"name":"Charlie","age":35,"skills":["Go","Rust"],"address":{"city":"Shenzhen","zip":"518000"},"active":true}');

-- INSERT 场景：插入新用户
INSERT INTO users (id, info) VALUES (
  11,
  json_object(
    'name', 'Henry',
    'age', 29,
    'skills', json_array('Python', 'Java'),
    'address', json_object('city', 'Wuhan', 'zip', '430000'),
    'active', json('true')
  )
);

-- UPDATE 场景：用 json_object 构造新值替换整个 info
UPDATE users SET info = json_object(
  'name', 'Henry Updated',
  'age', 30,
  'skills', json_array('Go', 'Rust'),
  'active', json('true')
) WHERE id = 11;

-- SELECT 场景：动态构建临时对象展示
SELECT json_object(
  'user', info ->> '$.name',
  'location', json_extract(info, '$.address.city'),
  'is_active', json_extract(info, '$.active')
) AS summary FROM users WHERE id = 1;

-- DELETE 场景：重置整行 JSON 为空白对象
UPDATE users SET info = json_object('reset', json('true'), 'timestamp', datetime('now'))
WHERE id = 101;

-- INSERT 场景：插入带有技能数组的新用户
INSERT INTO users (id, info) VALUES (
  12,
  json_object(
    'name', 'Ivy',
    'skills', json_array('Python', 'SQL', 'Docker')
  )
);

-- UPDATE 场景：替换整个技能数组
UPDATE users SET info = json_set(
  info,
  '$.skills', json_array('Kubernetes', 'Terraform', 'Ansible')
) WHERE info ->> '$.name' = 'Alice';

-- SELECT 场景：构建临时数组用于展示
SELECT json_array(
  info ->> '$.name',
  json_extract(info, '$.address.city'),
  json_array_length(info, '$.skills')
) AS user_info_array FROM users WHERE id = 1;

-- DELETE 场景：清空数组
UPDATE users SET info = json_set(info, '$.skills', json_array()) WHERE id = 123;

-- INSERT 场景：确保插入的值是合法的 JSON 字符串
INSERT INTO users (id, info) VALUES (
  13,
  json_object(
    'note', json_quote('This is a "quoted" note with special chars'),
    'count', json_quote(42)
  )
);

-- SELECT 场景：将普通列值转为 JSON 格式
SELECT json_quote(info ->> '$.name') AS quoted_name FROM users WHERE id = 1;
-- "Alice"  （带双引号的 JSON 字符串）

-- UPDATE 场景：安全地插入包含特殊字符的文本
UPDATE users SET info = json_set(
  info,
  '$.description', json_quote('Line1\nLine2\tTabbed')
) WHERE id = 141;

-- DELETE 场景：日志记录清理前的值
SELECT json_quote(info) AS deleted_data FROM users WHERE id = 151;

-- SELECT 场景：提取单字段
SELECT json_extract(info, '$.name') FROM users WHERE id = 1;

-- SELECT 场景：提取多字段
SELECT json_extract(info, '$.name', '$.age', '$.address.city') FROM users WHERE id = 1;

-- SELECT 场景：提取数组元素
SELECT json_extract(info, '$.skills[0]') AS first_skill FROM users WHERE id = 1;

-- WHERE 条件中使用
SELECT * FROM users WHERE json_extract(info, '$.active') = 'true';

-- INSERT 场景：从已有记录复制字段
INSERT INTO users (id, info) VALUES (
  161,
  json_object(
    'name', 'Jack',
    'template', (SELECT json_extract(info, '$.address') FROM users WHERE id = 1)
  )
);

-- UPDATE 场景：跨行赋值
UPDATE users SET info = json_set(
  info,
  '$.mentor',
  (SELECT json_extract(info, '$.name') FROM users WHERE id = 2)
) WHERE id = 171;

-- DELETE 场景：删除前备份特定字段
SELECT json_extract(info, '$.name') AS deleted_user FROM users WHERE id = 181;

-- SELECT 场景：获取 JSON 原始格式
SELECT info -> '$.name' FROM users WHERE id = 1;
-- "Alice"  （带引号的 JSON 字符串）

SELECT info -> '$.age' FROM users WHERE id = 1;
-- 30

SELECT info -> '$.skills' FROM users WHERE id = 1;
-- ["Python","SQL"]

-- WHERE 条件（需注意 JSON 格式比较）
SELECT * FROM users WHERE info -> '$.name' = '"Alice"';

-- UPDATE 场景：拼接路径表达式
UPDATE users SET info = json_set(
  info,
  '$.skills[' || (json_array_length(info, '$.skills') - 1) || ']',
  info -> '$.skills[0]'
) WHERE id = 191;

-- SELECT 场景：获取纯文本
SELECT info ->> '$.name' FROM users WHERE id = 1;
-- Alice  （无引号）

SELECT info ->> '$.age' FROM users WHERE id = 1;
-- 30     （数字转为文本字符串）

SELECT info ->> '$.address.city' FROM users WHERE id = 1;
-- Beijing

-- WHERE 条件（推荐用于文本比较）
SELECT * FROM users WHERE info ->> '$.name' = 'Alice';

-- ORDER BY 排序
SELECT * FROM users ORDER BY info ->> '$.name';

-- GROUP BY 分组
SELECT info ->> '$.address.city' AS city, COUNT(*) AS cnt
FROM users
GROUP BY info ->> '$.address.city';

-- UPDATE 场景：条件更新
UPDATE users SET info = json_set(info, '$.level', 'VIP')
WHERE info ->> '$.name' LIKE 'A%';

-- SELECT 场景：检查字段类型
SELECT
  info ->> '$.name' AS name,
  json_type(info, '$.name') AS name_type,
  json_type(info, '$.age') AS age_type,
  json_type(info, '$.skills') AS skills_type,
  json_type(info, '$.address') AS address_type,
  json_type(info, '$.active') AS active_type
FROM users WHERE id = 1;
-- text | integer | array | object | true

-- SELECT 场景：筛选特定类型的记录
SELECT * FROM users WHERE json_type(info, '$.extra') IS NULL;

-- UPDATE 场景：根据类型决定更新策略
UPDATE users SET info = json_set(
  info,
  '$.processed', CASE
    WHEN json_type(info, '$.metadata') = 'object'
    THEN json('true')
    ELSE json('false')
  END
);

-- INSERT 场景：插入时校验类型
INSERT INTO users (id, info) SELECT
  201,
  json_object(
    'data', CASE
      WHEN json_type(json_extract(old_data, '$.payload')) = 'array'
      THEN old_data
      ELSE json_object('error', 'Invalid payload type')
    END
  )
FROM temp_table WHERE id = 211;

-- SELECT 场景：统计技能数量
SELECT
  info ->> '$.name' AS name,
  json_array_length(info, '$.skills') AS skill_count
FROM users;

-- WHERE 条件：筛选多技能用户
SELECT * FROM users
WHERE json_array_length(info, '$.skills') >= 3;

-- ORDER BY 排序：按技能数排序
SELECT * FROM users
ORDER BY json_array_length(info, '$.skills') DESC;

-- UPDATE 场景：动态定位数组末尾追加元素
UPDATE users SET info = json_set(
  info,
  '$.skills[' || json_array_length(info, '$.skills') || ']', 'NewSkill'
) WHERE id = 221;

-- INSERT 场景：插入时包含数组长度信息
INSERT INTO audit_log (event) VALUES (
  json_object(
    'action', 'check',
    'user_id', 1,
    'skill_count', json_array_length(
      (SELECT json_extract(info, '$.skills') FROM users WHERE id = 231)
    )
  )
);

-- SELECT 场景：展开技能数组
SELECT
  u.info ->> '$.name' AS user_name,
  e.key AS skill_index,
  e.value AS skill_name
FROM users u, json_each(u.info, '$.skills') e
WHERE u.id = 1;

-- SELECT 场景：展开对象键值对
SELECT key, value, type
FROM json_each('{"a":1,"b":"hello","c":[1,2]}');

-- INSERT 场景：从展开结果构建新记录
INSERT INTO user_skills (user_id, skill_name)
SELECT u.id, e.value
FROM users u, json_each(u.info, '$.skills') e
WHERE u.id = 241;

-- UPDATE 场景：批量处理数组元素
UPDATE users SET info = json_set(
  info,
  '$.skills[' || e.key || ']',
  UPPER(e.value)
)
FROM json_each((SELECT info FROM users WHERE id = 251), '$.skills') e
WHERE id = 251;

-- DELETE 场景：删除特定索引的元素
DELETE FROM users_skills_temp WHERE skill_name IN (
  SELECT value FROM json_each(
    (SELECT json_extract(info, '$.skills') FROM users WHERE id = 261)
  )
);

-- SELECT 场景：展开完整 JSON 结构
SELECT key, value, type, fullkey, path
FROM users, json_tree(info)
WHERE id = 1;

-- SELECT 场景：查找所有叶子节点
SELECT fullkey, value, type
FROM users, json_tree(info)
WHERE id = 1 AND type NOT IN ('array', 'object');

-- SELECT 场景：按层级深度过滤
SELECT fullkey, value, type
FROM users, json_tree(info)
WHERE id = 1 AND length(path) - length(replace(path, '.', '')) <= 1;

-- INSERT 场景：从树状结构导入数据
INSERT INTO flat_keys (full_path, value)
SELECT fullkey, value
FROM users, json_tree(info)
WHERE id = 271 AND type = 'text';

-- UPDATE 场景：递归更新所有匹配的值
UPDATE users SET info = json_set(info, '$.audited', json('true'))
WHERE id IN (
  SELECT DISTINCT id FROM users, json_tree(info)
  WHERE value LIKE '%sensitive%'
);

-- UPDATE 场景：更新单个字段
UPDATE users SET info = json_set(info, '$.age', 31) WHERE id = 281;

-- UPDATE 场景：更新多个字段
UPDATE users SET info = json_set(
  info,
  '$.name', 'Alice New',
  '$.age', 32,
  '$.country', 'China'  -- 新建字段
) WHERE id = 291;

-- UPDATE 场景：更新嵌套对象
UPDATE users SET info = json_set(
  info,
  '$.address.city', 'Nanjing',
  '$.address.zip', '210000'
) WHERE id = 301;

-- UPDATE 场景：更新数组元素
UPDATE users SET info = json_set(info, '$.skills[0]', 'Advanced Python') WHERE id = 311;

-- INSERT 场景：插入时使用 set 风格构造
INSERT INTO users (id, info) VALUES (
  321,
  json_set(
    json_object('base', 'template'),
    '$.name', 'Kevin',
    '$.age', 33
  )
);

-- UPDATE 场景：安全替换已知字段
UPDATE users SET info = json_replace(info, '$.age', 333) WHERE id = 331;

-- UPDATE 场景：尝试替换不存在字段（无效果）
UPDATE users SET info = json_replace(info, '$.nonexistent', 'x') WHERE id = 341;
-- 没有任何变化

-- UPDATE 场景：批量替换多个字段
UPDATE users SET info = json_replace(
  info,
  '$.name', UPPER(info ->> '$.name'),
  '$.active', json('false')
) WHERE id = 351;

-- SELECT 场景：预览替换效果但不提交
SELECT json_replace(info, '$.age', 999) AS preview FROM users WHERE id = 361;

-- INSERT 场景：基于模板插入
INSERT INTO users (id, info) VALUES (
  371,
  json_replace(
    (SELECT info FROM users WHERE id = 381),
    '$.id', 391,
    '$.name', 'Template Copy'
  )
);

-- UPDATE 场景：添加新字段
UPDATE users SET info = json_insert(
  info,
  '$.email', 'alice@example.com',
  '$.phone', '13800138000'
) WHERE id = 401;

-- UPDATE 场景：重复插入相同路径（不覆盖）
UPDATE users SET info = json_insert(info, '$.email', 'new@example.com') WHERE id = 411;
-- email 保持不变

-- UPDATE 场景：插入到数组末尾（需配合长度）
UPDATE users SET info = json_insert(
  info,
  '$.skills[' || json_array_length(info, '$.skills') || ']', 'Docker'
) WHERE id = 421;

-- SELECT 场景：预览插入效果
SELECT json_insert(info, '$.temp_field', 'temporary') FROM users WHERE id = 431;

-- INSERT 场景：创建新记录时避免覆盖默认值
INSERT INTO users (id, info) VALUES (
  441,
  json_insert(
    json_object('default_role', 'user'),
    '$.name', 'Leo',
    '$.role', 'admin'
  )
);

-- UPDATE 场景：部分更新（只改指定字段，其余保留）
UPDATE users SET info = json_patch(
  info,
  json_object(
    'age', 344,
    'address', json_object('city', 'Wuhan', 'zip', '430000'),
    'title', 'Senior Engineer'
  )
) WHERE id = 451;
-- age 更新，address 整体替换，title 新增，其他字段保留

-- UPDATE 场景：合并外部配置
UPDATE users SET info = json_patch(info, '{"theme":"dark","notifications":true}');

-- SELECT 场景：预览合并结果
SELECT json_patch(
  info,
  '{"age": 100, "test_only": true}'
) AS what_if FROM users WHERE id = 461;

-- INSERT 场景：合并多个来源
INSERT INTO users (id, info) VALUES (
  471,
  json_patch(
    json_object('source', 'import'),
    json_object('name', 'Mia', 'age', 266)
  )
);

-- DELETE 场景：通过 patch 清空某些字段（设为 null）
UPDATE users SET info = json_patch(info, '{"obsolete_field": null}') WHERE id = 481;

-- UPDATE 场景：删除单个字段
UPDATE users SET info = json_remove(info, '$.email') WHERE id = 491;

-- UPDATE 场景：删除多个字段
UPDATE users SET info = json_remove(info, '$.phone', '$.country', '$.title') WHERE id = 501;

-- UPDATE 场景：删除嵌套字段
UPDATE users SET info = json_remove(info, '$.address.zip') WHERE id = 511;

-- UPDATE 场景：删除数组元素
UPDATE users SET info = json_remove(info, '$.skills[1]') WHERE id = 521;
-- 数组自动压缩

-- UPDATE 场景：删除不存在的路径（无效果）
UPDATE users SET info = json_remove(info, '$.nonexistent') WHERE id = 531;

-- SELECT 场景：预览删除效果
SELECT json_remove(info, '$.age', '$.active') AS preview FROM users WHERE id = 541;

-- INSERT 场景：插入时排除某些字段
INSERT INTO users (id, info) VALUES (
  551,
  json_remove(
    (SELECT info FROM users WHERE id = 561),
    '$.id',
    '$.created_at'
  )
);

-- SELECT 场景：验证数据完整性
SELECT id, json_valid(info) AS is_valid FROM users;

-- SELECT 场景：筛选无效 JSON
SELECT * FROM users WHERE json_valid(info) = 0;

-- INSERT 场景：插入前验证
INSERT INTO users (id, info)
SELECT 571, '{"name":"Test"}'
WHERE json_valid('{"name":"Test"}') = 1;

-- UPDATE 场景：仅更新有效 JSON 的记录
UPDATE users SET info = json_set(info, '$.checked', json('true'))
WHERE json_valid(info) = 1;

-- DELETE 场景：清理无效数据
DELETE FROM users WHERE json_valid(info) = 0;

-- SELECT 场景：调试无效 JSON
SELECT json_error_position('{"bad": }');
-- 13

-- INSERT 场景：插入前检查错误位置
INSERT INTO error_log (input, error_pos)
VALUES ('{"malformed": true', json_error_position('{"malformed": true'));

-- UPDATE 场景：修复前定位问题
UPDATE users SET info = '{"fixed": true}'
WHERE json_error_position(info) > 0;

-- DELETE 场景：标记并删除严重错误
DELETE FROM users WHERE json_error_position(info) > 50;

-- SELECT 场景：人类可读展示
SELECT json_pretty(info) FROM users WHERE id = 581;

-- SELECT 场景：导出格式化数据
.mode list
.separator "\n---\n"
SELECT json_pretty(info) FROM users;

-- INSERT 场景：存储美化版本（不推荐，浪费空间）
INSERT INTO users_pretty SELECT id, json_pretty(info) FROM users;

-- UPDATE 场景：临时美化用于展示
UPDATE display_cache SET formatted = json_pretty(raw_json) WHERE table_name = 'users';

-- SELECT 场景：标准化 JSON 格式
SELECT json('{"a": 1, "b": [2, 3]}');
-- {"a":1,"b":[2,3]}  （紧凑格式）

-- INSERT 场景：确保插入合法 JSON
INSERT INTO users (id, info) VALUES (591, json('{"validated": true}'));

-- UPDATE 场景：标准化已有数据
UPDATE users SET info = json(info) WHERE json_valid(info) = 1;

-- SELECT 场景：查看二进制存储大小
SELECT id, length(info) AS text_size, length(jsonb(info)) AS binary_size
FROM users;

-- INSERT 场景：直接存储二进制 JSON
INSERT INTO users_binary SELECT id, jsonb(info) FROM users;

-- UPDATE 场景：转换已有数据为二进制
UPDATE users_binary SET info_bin = jsonb(info) WHERE id = 601;

-- jsonb_extract：二进制提取
SELECT jsonb_extract(info, '$.name') FROM users WHERE id = 611;

-- jsonb_set：二进制设置
UPDATE users SET info = jsonb_set(info, '$.age', '355') WHERE id = 621;

-- jsonb_insert：二进制插入
UPDATE users SET info = jsonb_insert(info, '$.email', 'bob@test.com') WHERE id = 631;

-- jsonb_replace：二进制替换
UPDATE users SET info = jsonb_replace(info, '$.age', '266') WHERE id = 641;

-- jsonb_remove：二进制删除
UPDATE users SET info = jsonb_remove(info, '$.temp') WHERE id = 651;

-- jsonb_array / jsonb_object：二进制构建
SELECT jsonb_array(1, 2, 3);
SELECT jsonb_object('key', 'value', 'num', 422);

-- SELECT 场景：收集所有用户名
SELECT json_group_array(info ->> '$.name') AS all_names FROM users;

-- SELECT 场景：按城市分组收集
SELECT
  json_extract(info, '$.address.city') AS city,
  json_group_array(info ->> '$.name') AS residents
FROM users
GROUP BY json_extract(info, '$.address.city');

-- INSERT 场景：将聚合结果存入新表
INSERT INTO city_summary (city, resident_names)
SELECT
  json_extract(info, '$.address.city'),
  json_group_array(info ->> '$.name')
FROM users
GROUP BY json_extract(info, '$.address.city');

-- SELECT 场景：高效聚合
SELECT jsonb_group_array(info ->> '$.name') FROM users;

-- INSERT 场景：存储二进制聚合结果
INSERT INTO cache (key, value_bin)
SELECT 'all_names', jsonb_group_array(info ->> '$.name') FROM users;

-- SELECT 场景：构建名称-年龄映射
SELECT json_group_object(
  info ->> '$.name',
  info ->> '$.age'
) AS name_age_map FROM users;

-- SELECT 场景：构建名称-城市映射
SELECT json_group_object(
  info ->> '$.name',
  json_extract(info, '$.address.city')
) AS name_city_map FROM users;

-- INSERT 场景：存储映射结果
INSERT INTO mappings (type, data)
VALUES ('name_city', (
  SELECT json_group_object(
    info ->> '$.name',
    json_extract(info, '$.address.city')
  ) FROM users
));

-- SELECT 场景：高性能映射构建
SELECT jsonb_group_object(
  info ->> '$.name',
  json_array_length(info, '$.skills')
) AS name_skill_count FROM users;