40. JSON 函数（json1）

先在开头点明 SQLite 的”身份”：它是 serverless、零配置的嵌入式数据库，整个库就是一个文件，没有独立的服务进程在后台跑。这种轻量定位，意味着它希望”能就地解决的事就就地解决”——JSON 处理正是如此。从 SQLite 3.38.0（2022-02-22）起，json1 扩展（JSON 函数集）就已经默认编译进标准发行版；我们本教程的基线版本 SQLite 3.53.4 自然也默认包含，你不需要任何额外的 PRAGMA 或加载扩展动作，开箱即用。

一、构造 JSON

json_array(...) 把多个值拼成一个 JSON 数组，json_object(键1,值1,...) 拼成 JSON 对象。注意一个容易踩的点：普通文本参数会被当成”字符串值”加引号；如果参数本身是另一个 JSON 函数的返回值，才会被当作真正的 JSON 结构插入（支持嵌套）。

SELECT json_array(1, 2, 'three');
-- [1,2,"three"]
SELECT json_object('name', 'alice', 'age', 30);
-- {"name":"alice","age":30}

json(X) 用来校验并压缩（去掉多余空格）一段 JSON 文本，若不是合法 JSON 会报错；它也能把 JSON5 扩展语法转成标准 JSON。日常把外部字符串规范化入库前，先过一遍 json()，既校验合法性又压掉多余空白，一举两得。

二、提取 JSON

json_extract(json, 路径, ...) 是最常用的提取函数。路径以 $ 开头：$.字段 取对象属性，$[0] 取数组第 0 个元素，$.a[2].f 可层层深入。$[#-1] 表示数组最后一个元素。

SELECT json_extract('{"a":2,"c":[4,5,{"f":7}]}', '$.c[2].f');
-- 7

还有两个运算符（3.38.0+）：-> 返回 JSON 文本形式的子组件，->> 返回 SQL 原生值。日常查询里 ->> 最顺手：

SELECT '{"a":2,"c":[4,5]}' -> '$.c'   AS j;   -- '[4,5]'（仍是 JSON 文本）
SELECT '{"a":2,"c":[4,5]}' ->> '$.a'  AS v;   -- 2（SQL 数值）

想看 JSON 里某个节点的”类型”，用 json_type(X) 或 json_type(X, 路径)，返回 ‘object’/‘array’/‘integer’/‘text’ 等。想判断一段文本是不是合法 JSON，用 json_valid(X)（默认要求严格 RFC-8259；想放宽到 JSON5 用 json_valid(X, 2)）。这个函数在”导入外部数据”时极实用：先 SELECT json_valid(raw) FROM staging WHERE json_valid(raw) = 0 就能一次性揪出所有非法行，把脏数据拦在入库之前，而不是等 json_extract 报错才发现问题。

三、修改 JSON

三个”改”函数最容易混淆，记住这张表就够了：

函数	已存在则覆盖？	不存在则新建？
json_insert()	否	是
json_replace()	是	否
json_set()	是	是

实际用得最多的是 json_set()，它既能改又能加：

SELECT json_set('{"a":2,"c":4}', '$.a', 99);   -- {"a":99,"c":4}
SELECT json_set('{"a":2,"c":4}', '$.e', 99);   -- {"a":2,"c":4,"e":99}

注意：直接传文本值（如 '[97,96]'）会被当成字符串加引号；要插入真正的 JSON 结构，需包一层 json() 或 json_array()：

SELECT json_set('{"a":2}', '$.c', json_array(97,96));
-- {"a":2,"c":[97,96]}

json_remove(json, 路径, ...) 删除指定节点；json_array_length(json [,路径]) 取数组长度；json_pretty(json)（3.46.0+）把 JSON 排版成易读的多行格式。

四、拆解 JSON 成多行

json_each(json) 和 json_tree(json) 是”表值函数”：它们把 JSON 拆成多行返回，每行的 key、value、type 等列可像普通表一样 SELECT 和 JOIN。json_each 只拆第一层，json_tree 递归拆到底。

SELECT key, value
FROM json_each('["a","b","c"]');

配合全局示例表，假设 users 的 email 之外有个 tags TEXT 存 JSON 数组，想查所有带 “vip” 标签的用户：

SELECT u.name
FROM users u, json_each(u.tags)
WHERE json_each.value = 'vip';

聚合方面，json_group_array(值) 与 json_group_object(键,值) 能把多行聚成一个 JSON 数组/对象，做”行转 JSON”非常方便。

五、实战示例：给 users 表加 JSON 配置

假设我们给全局示例表 users(id INTEGER PRIMARY KEY, name TEXT, age INTEGER, email TEXT) 增加一列 profile TEXT 来存半结构化信息（城市、标签、是否会员）。插入时直接用 json_object 构造：

ALTER TABLE users ADD COLUMN profile TEXT;

INSERT INTO users(id, name, age, email, profile)
VALUES (1, 'alice', 30, 'a@x.com',
        json_object('city', '上海', 'vip', 1, 'tags', json_array('a','b')));

查询时像取普通列一样取 JSON 里的字段：

SELECT name,
       json_extract(profile, '$.city')        AS city,
       json_extract(profile, '$.vip')         AS is_vip
FROM users;

想给 alice 加一个标签，用 json_set 配合数组追加（$[#] 表示”末尾之后”）：

UPDATE users
SET profile = json_set(profile, '$.tags[#]', 'c')
WHERE id = 1;

想找所有带 “vip” 标签的用户，用 json_each 拆数组后过滤：

SELECT u.name
FROM users u, json_each(u.profile, '$.tags')
WHERE json_each.value = 'vip';

这种写法让你不必为了”多几个可选字段”频繁 ALTER TABLE，字段可随意增减，非常适合配置项、扩展属性这类”结构不稳定”的数据。

六、何时用 JSON、何时该拆表

JSON 好用，但不是万能。给你一个判断清单：

• 适合用 JSON：字段结构不稳定、经常增减；只在少数查询里用到；嵌套层级浅（一两层）；不需要对 JSON 内部字段建索引做高频筛选。典型如用户配置、标签数组、第三方返回的原文存证。
• 应该拆成普通列/子表：字段固定且几乎每条查询都要用；需要对它做聚合、连接、排序；需要约束唯一性或外键；数据量大且要建索引加速。典型如订单金额、用户姓名。

折中方案是”表达式索引”：对常用的 JSON 路径建索引，例如 CREATE INDEX idx_city ON users(json_extract(profile, '$.city'))，这样按城市查也能走索引，兼顾灵活与性能。

七、类比小结

把 json1 想成”文本车间里的 JSON 流水线”：原料是一段普通文本（存成 TEXT），车间里的 json_object/json_array 负责打包，json_extract/->> 负责拆包取料，json_set/json_remove 负责改料，json_each/json_tree 负责把整包拆成散件摆上流水线供其他工序使用。整条线都在 SQLite 内部跑完，你不用把原料搬回 Python/Java 再解析一遍。

一句话适用场景：当你需要在 SQL 层直接读写结构化或半结构化字段（配置、标签、嵌套属性），而懒得为了加几个字段去改表结构时，json1 就是那个”刚刚好”的工具。