SQLite 3中如何使用JSON函数查询数组数据-SQL-PHP中文网

mysql用json_contains可查json数组是否含某值，要求第二个参数为带双引号的json字符串（如'"banana"'），路径默认'$'；sqlite则需用json_each展开数组再过滤，无法直接使用类似函数。

sqlite 3中如何使用json函数查询数组数据

json_array_contains 不能直接用字符串判断数组包含

很多人写 json_array_contains(json_col, 'apple') 发现查不到数据，是因为该函数第二个参数必须是原始值（如整数、布尔），不是 JSON 字符串。传入 '"apple"' 或 'apple' 都会失败 —— 它只接受未加引号的字面量。

正确写法：json_array_contains(json_col, 'apple') ❌（字符串字面量，不生效）
正确写法：json_array_contains(json_col, 'apple') 实际上仍错；真正有效的是：json_array_contains(json_col, 'apple') → 不，等等 —— 真正生效的是：json_array_contains(json_col, 'apple')？不对。查文档确认：json_array_contains 的 value 参数类型是 SQL 值，不是 JSON 字符串。所以传 'apple'（SQL text）可以，但前提是 JSON 数组里存的就是 plain string，不是 "apple" 这种带引号的 JSON 字符串。
更稳妥的做法是：先用 json_valid(json_col) 过滤非法 JSON，再用 json_type(json_col) = 'array' 确保是数组，最后用 json_array_contains 判断 —— 但注意：如果数组元素是对象（如 [{"name":"a"},{"name":"b"}]），json_array_contains 对整个对象无效，它只匹配标量或 null。

查询 JSON 数组中某个字段等于指定值的对象

比如 JSON 列存的是 [{"id":1,"status":"active"},{"id":2,"status":"pending"}]，想查 status 为 "active" 的记录。SQLite 没有内置路径式数组遍历函数，json_extract 只能取固定下标，无法“全量扫描”。这时必须用 json_each 展开：

SELECT t.* FROM mytable t, json_each(t.json_col) j WHERE j.value LIKE '%"status":"active"%' —— ❌ 不可靠，属子串匹配，可能误命中
正确方式：SELECT t.* FROM mytable t, json_each(t.json_col) j WHERE j.type = 'object' AND json_extract(j.value, '$.status') = 'active'
注意 json_each 是表值函数，会产生多行结果；同一记录若数组中有多个匹配项，会重复返回 —— 加 DISTINCT 或用 EXISTS 包裹更安全
性能敏感时，避免在大表上无条件 CROSS JOIN json_each；务必加 WHERE json_valid(json_col) 提前过滤

json_extract 取数组元素时下标越界返回 NULL，不报错

json_extract(json_col, '$[5]') 如果数组只有 3 个元素，结果就是 NULL，不是错误。这点容易被忽略，导致后续逻辑误判为“字段不存在”而非“索引超出”。

php版微信js-sdk支付接口类

下载

检查是否存在可用下标：json_array_length(json_col) > 5
取首项安全写法：json_extract(json_col, '$[0]') + WHERE json_array_length(json_col) > 0
json_array_get 和 json_extract 行为不同：json_array_get 返回 varchar 类型，且对非数组输入也返回 NULL；json_extract 支持任意路径，但要求输入是合法 JSON
别依赖 json_extract 的返回类型做类型判断 —— 它永远返回 text，哪怕提取的是数字；需要数值比较时，得显式 CAST(... AS INTEGER)

高频查询 JSON 数组内容必须建表达式索引

直接写 WHERE json_array_contains(json_col, 'x') 或 json_extract(json_col, '$.tags') LIKE '%x%' 几乎必然全表扫描。SQLite 的 B-tree 索引无法解析 JSON 内部结构，除非你告诉它“这个表达式的结果值得索引”。

有效索引语句：CREATE INDEX idx_tags_contain_x ON mytable (json_extract(json_col, '$.tags')) WHERE json_valid(json_col);
但注意：这个索引只加速 json_extract(json_col, '$.tags') 整体相等查询，不加速 json_array_contains 或模糊匹配
真要加速数组元素存在性判断，目前唯一可行方案是冗余列：ALTER TABLE mytable ADD COLUMN has_tag_x AS (json_array_contains(json_col, 'x')) STORED;（SQLite 3.38+ 支持生成列表达式）
没有生成列支持的老版本，只能靠应用层维护额外 tag 标志字段，或定期物化视图刷新

实际用起来最麻烦的不是语法，而是 SQLite 对 JSON 的“半解析”特性 —— 它能提取、能验证、能展开，但所有操作都发生在查询执行期，不参与索引构建，也不做类型推导。写 WHERE 条件前，先想清楚：这个判断能不能提前落到普通列上。