SQLite中JSON_EXTRACT、JSON_SET等JSON函数怎么用？常见用法与避坑详解

不少项目会把灵活多变的配置信息或第三方接口返回的数据直接以JSON字符串的形式存进SQLite字段里，这样确实省去了频繁改表结构的麻烦。但存进去只是一半，怎么在SQL层面把这些JSON数据取出、修改、更新才是关键。SQLite从3.9版本起内置了JSON扩展函数，其中JSON_EXTRACT负责提取、JSON_SET负责更新，配合JSON_INSERT、JSON_REPLACE、JSON_PATCH等函数，基本可以覆盖日常的JSON读写需求。这篇文章就把这些函数的用法逐一拆开讲清楚。

先搞懂路径表达式，这是所有JSON函数的基础

无论是提取还是更新，SQLite的JSON函数都依赖一个统一的路径语法。路径必须以美元符号$开头，表示JSON文档的根节点，后面通过点号和方括号逐层深入。访问对象的键用.键名，例如$.user.name表示根对象下user对象里的name字段；访问数组用方括号加下标，下标从0开始，例如$.items[0]取数组第一个元素，$.items[1].price取第二个元素的price键。

有几个容易忽略的细节值得单独说。第一，如果键名本身包含点号、空格或特殊字符，可以用双引号包裹，写成$."user name"的形式。第二，路径末尾写#代表数组长度，$.items[#]即数组末尾位置，这个写法在JSON_ARRAY_INSERT这类函数里往尾部追加元素时特别有用。第三，从SQLite 3.31开始支持#-n的负数下标，表示倒数第n个元素，例如$.items[#-1]取最后一个元素。

路径写错通常不会直接报错，而是返回NULL，这是很多人排查半天找不到原因的坑。所以在调试阶段，建议先用JSON_EXTRACT验证路径是否正确，确认路径没问题后再套用到UPDATE语句里，能省不少时间。

JSON_EXTRACT与JSON查询：提取嵌套字段并不复杂

JSON_EXTRACT是最常用的函数，接收一个JSON字符串和一个或多个路径，返回对应的值。如果结果是字符串，默认会带上双引号返回，想拿到不带引号的纯文本，可以用->>双箭头操作符，它是JSON_EXTRACT的语法糖，区别就在返回值是否去掉引号和类型转换上。

下面用一个完整的例子演示。先建表插入一条带嵌套JSON的记录，再做几种典型查询：

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    info TEXT   -- 存放JSON字符串
);

INSERT INTO orders (info) VALUES (
    '{"user": {"name": "张三", "vip": true},
      "items": [
          {"sku": "A100", "price": 59.9},
          {"sku": "B200", "price": 128.0}
      ],
      "remark": "尽快发货"}'
);

-- 提取嵌套对象的字段
SELECT JSON_EXTRACT(info, '$.user.name');      -- 返回 "张三"（带引号）

-- 使用双箭头操作符拿到纯文本
SELECT info ->> '$.user.name';                -- 返回 张三（不带引号）

-- 提取数组内指定下标的元素
SELECT JSON_EXTRACT(info, '$.items[0].price'); -- 返回 59.9

-- 一次提取多个值，会拼成数组返回
SELECT JSON_EXTRACT(info, '$.user.name', '$.items[1].sku');
-- 返回 ["张三","B200"]

-- 提取整个数组
SELECT JSON_EXTRACT(info, '$.items');

JSON_EXTRACT返回的值可以直接参与WHERE过滤和聚合运算，这让JSON字段的查询能力大幅提升。比如统计每个订单最贵商品的价格：

-- 查询包含价格超过100元商品的订单
SELECT id FROM orders
WHERE JSON_EXTRACT(info, '$.items[1].price') > 100;

-- 结合通配符匹配数组内所有元素（SQLite 3.32+ 支持 LIKE 增强写法时可配合）
-- 数组内任意sku等于A100的订单
SELECT id FROM orders
WHERE EXISTS (
    SELECT 1 FROM JSON_EACH(info, '$.items')
    WHERE JSON_EXTRACT(value, '$.sku') = 'A100'
);

这里用到的JSON_EACH是个表值函数，可以把JSON数组或对象拆成一行行记录，是遍历JSON数据的主力工具，处理不确定长度的数组时比写死下标靠谱得多。

JSON_SET、JSON_INSERT、JSON_REPLACE：三个更新函数的区别

这三个函数长得很像，行为差异却很关键，混用会出隐蔽的bug。核心区别一句话概括：JSON_INSERT只在键不存在时插入，JSON_REPLACE只在键存在时替换，JSON_SET则不管存不存在都会写入。

举例说明，假设还是上面那条订单数据，分别做三种操作：

-- JSON_INSERT：status不存在，插入成功
SELECT JSON_INSERT(info, '$.status', 'paid') FROM orders WHERE id = 1;
-- 结果里多了 "status":"paid"

-- JSON_REPLACE：remark存在，替换成功
SELECT JSON_REPLACE(info, '$.remark', '加急处理') FROM orders WHERE id = 1;

-- JSON_SET：存在则更新，不存在则插入，最省心的选择
SELECT JSON_SET(info, '$.remark', '已联系买家') FROM orders WHERE id = 1;

-- 一次修改多个字段
SELECT JSON_SET(info,
    '$.user.name', '李四',
    '$.items[0].price', 49.9
) FROM orders WHERE id = 1;

注意这些函数都不会改动原数据，它们返回修改后的新JSON字符串。要真正落库必须配合UPDATE语句：

UPDATE orders
SET info = JSON_SET(info, '$.status', 'paid')
WHERE id = 1;

-- 往数组末尾追加一个元素，# 表示数组长度即末尾下标
UPDATE orders
SET info = JSON_INSERT(info, '$.items[#]', JSON('{"sku":"C300","price":35.5}'))
WHERE id = 1;

-- 删除指定路径
UPDATE orders
SET info = JSON_REMOVE(info, '$.remark')
WHERE id = 1;

追加数组元素这一步有个细节：如果直接传一个字符串'{"sku":"C300"}'而不是用JSON()函数包裹，插入进去的是一个字符串而不是JSON对象，后面再提取$.items[2].sku就会得到NULL。JSON()函数的作用是把文本显式标记为合法JSON，避免这种类型歧义。

进阶用法与常见坑位总结

再补充几个实用函数。JSON_PATCH实现RFC 7396定义的merge patch，适合把一个JSON对象的变更批量合并到另一个对象上，比如接口只返回了部分字段的修改值，用JSON_PATCH一次合并即可。JSON_TYPE返回指定路径的值类型，JSON_VALID用来校验字符串是不是合法JSON，JSON_QUOTE把SQL值转成JSON字符串，JSON_GROUP_ARRAY和JSON_GROUP_OBJECT是聚合函数，可以把多行数据聚合成一个JSON数组或对象，做反向转换很方便。

-- 用JSON_PATCH合并补丁
SELECT JSON_PATCH(info, '{"remark":"换货处理","extra":{"gift":true}}')
FROM orders WHERE id = 1;

-- 校验JSON合法性
SELECT JSON_VALID(info) FROM orders;  -- 合法返回1，非法返回0

-- 把查询结果聚合成JSON数组
SELECT JSON_GROUP_OBJECT(sku, price)
FROM JSON_EACH(
    (SELECT info FROM orders WHERE id = 1),
    '$.items'
);
-- 返回类似 {"A100":59.9,"B200":128.0}

坑位方面，有四点需要留意。其一，路径写错返回NULL而非报错，排查时优先怀疑路径。其二，对非法JSON执行JSON_EXTRACT会抛错，写入前最好用JSON_VALID做一次校验，或者在应用层保证只写入合法JSON。其三，这些函数每次调用都要重新解析整个JSON文本，数据量大、JSON文档很长时性能会明显下降，频繁按某个JSON字段查询的话，建议把该字段提取出来存成普通列并建索引，JSON函数适合灵活查询，不适合做高频过滤条件。其四，版本兼容问题，JSON函数在3.9才引入，->>操作符要求3.38以上，老版本SQLite上运行会直接报函数不存在的错误，发布前务必确认目标环境的SQLite版本。

把这些函数的分工记牢——EXTRACT管查询，SET管写入，INSERT管新增，REPLACE管替换，REMOVE管删除，EACH管遍历——在SQLite里处理JSON数据基本就够用了。如果你的应用场景里JSON结构相对固定，也可以考虑SQLite 3.45之后增强的JSONB支持，解析速度会有进一步提升。

SQLiteJSON_EXTRACTJSON_SET修改时间：2026-09-03 18:55:06