SQLite为何缺失jsonb_each函数？附类型判断与高效访问疑问

1. j.value的类型是JSON文本（json类型） #

当你用json_each处理jsonb类型的my_data列时，返回的j.value是JSON文本格式（即标准json类型），而非jsonb。这是因为json_each函数的设计目标是解析JSON文本，即便输入是二进制编码的jsonb，SQLite内部会先将其转换为JSON文本再进行解析，最终输出的value字段自然是JSON文本类型。

2. 为何SQLite没有提供jsonb_each #

主要有两个原因：

• 转换开销可忽略：jsonb本质是JSON的二进制压缩编码，当传入json_each时，SQLite会自动完成jsonb到JSON文本的转换，这个过程的性能开销极小，单独提供jsonb_each函数无法带来显著的性能提升。
• 设计逻辑统一：JSON1扩展的表值函数（如json_each、json_tree）均基于JSON文本解析逻辑构建，为了保持API简洁，官方并未针对jsonb单独实现一套表值函数——现有函数已经能满足jsonb的处理需求。

3. 访问jsonb数组中key1值的最高效方式 #

根据使用场景，有几种优化方案：

方案一：优化现有查询写法 #

保持json_each的用法，但可以使用更简洁的操作符提升可读性（性能与json_extract相当）：

SELECT j.value ->> '$.key1' AS key1
FROM table1, json_each(table1.my_data) AS j;

注：->>操作符等价于json_extract(value, '$.key1')，且支持直接提取文本值。

方案二：使用生成列加速查询 #

如果需要频繁查询该数组的key1值，可以创建虚拟生成列，将数组中的key1集合预提取出来，再配合json_each拆分：

-- 添加虚拟生成列，提取所有key1组成的JSON数组
ALTER TABLE table1 ADD COLUMN key1_array TEXT GENERATED ALWAYS AS (json_extract(my_data, '$[*].key1')) VIRTUAL;

-- 查询时直接基于生成列拆分
SELECT j.value AS key1
FROM table1, json_each(table1.key1_array) AS j;

虚拟生成列不会占用额外存储空间，但能减少每次查询时的JSON解析次数，提升重复查询的效率。

方案三：重构为关系型存储（最优性能） #

如果你的业务频繁需要查询这类数组元素，最彻底的优化是将JSON数组拆分为独立的关系型表：

1. 创建子表table1_key1，包含table1_id（关联主表主键）和key1字段；
2. 批量将主表my_data中的key1值插入子表；
3. 后续查询直接关联子表：

SELECT tk.key1
FROM table1 t
JOIN table1_key1 tk ON t.id = tk.table1_id;

这种方式能利用SQLite的索引优化，查询性能远高于JSON解析，适合数据更新频率低、查询频率高的场景。

内容的提问来源于stack exchange，提问作者HelloThere