如何在 SQLite 中将元素插入数组（json）的中间？

Question

我在SQLite文档的json部分找到了一个方法json_insert。但它似乎并没有按照我预期的方式工作。

例如 select json_insert('[3,2,1]', '$[3]', 4) as result;

result 列返回 '[3 ,2,1,4]'，这是正确的。

但是对于 select json_insert('[3,2,1]', '$[1]', 4) as result;

我期待类似 '[3,2,4, 1]' 返回，而不是 '[3,2,1]'。

我错过了什么吗？我没有看到 json_insert 的替代方法。

PS我正在https://sqlime.org/#demo.db（SQLite）上玩它版本是3.37.2。

Answer 1

文档指出 json_insert() 不会覆盖值 （“如果已存在则覆盖？ - 否”）。这意味着您不能在数组中间插入元素。

我的解释：该函数主要是为了将键插入到对象中，这种行为更有意义——不改变数组的长度是为了一致性的牺牲。

您可以通过将 JSON 数组转换为表、附加元素、对结果进行排序并将其全部转换回 JSON 数组来将其硬塞到 SQLite 中：

select json_group_array(x.value) from (
    select key, value from json_each('[3,2,1]')
    union
    select 1.5, 4  -- 1.5 = after 1, before 2
    order by 1
) x

这将生成 '[3,2,4,1]'。

但您可能会发现这无法扩展，即使有一个内置函数可以为您完成此操作，它也无法扩展。字符串操作很慢。对于一次性或不经常这样做的情况来说，它可能效果很好。

从长远来看，我建议正确规范化数据库结构，而不是将“非 blob”数据存储在 JSON blob 中。操作规范化数据比操作 JSON 容易得多，更不用说速度可能快几个数量级了。