如何在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的替代方法。

附言：我正在播放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]'。

但你可能会看到，这不会扩展，即使有一个内置函数为你做这件事，它也不会扩展。字符串操作很慢。它可能在一次性使用或不经常使用时效果良好。

从长远来看，我建议正确规范数据库结构，而不是存储"；非斑点"；JSON Blob中的数据。处理规范化数据比处理JSON容易得多，更不用说可能快几个数量级了。