使用SQLite JSON1扩展更新单个JSON属性值的技术问询

嘿，其实SQLite的JSON1扩展里的json_set()函数刚好能完美解决你的需求——它可以同时支持单个JSON属性的插入和更新：如果目标属性在JSON对象里不存在，就会新增这个属性；如果已经存在，就会覆盖它的值。

结合你的keywords表结构，我给你几个实用的例子：

基础用法：更新或插入单个属性 #

比如你想给lang=1、kwd='sqlite'的记录，更新或新增locs里的ny位置计数（第一次插入时默认从1开始，之后每次累加）：

UPDATE keywords
SET locs = json_set(locs, '$.ny', COALESCE(json_extract(locs, '$.ny'), 0) + 1)
WHERE lang = 1 AND kwd = 'sqlite';

这里的COALESCE是为了处理属性不存在的情况：如果$.ny还没在locs里，json_extract会返回NULL，COALESCE就会用0来替代，再加1后就是1，实现第一次插入的效果。

直接插入新属性 #

如果只是想给指定记录的locs新增一个固定值的属性，比如给id=1的记录添加la位置，值设为5：

UPDATE keywords
SET locs = json_set(locs, '$.la', 5)
WHERE id = 1;

区分插入/更新的细分场景 #

如果你需要更精准的控制（比如只插入不更新，或者只更新不插入），SQLite 3.38.0及以上版本还提供了两个针对性的函数：

• json_insert()：仅插入不存在的属性，如果属性已经存在，不会做任何修改

  -- 只有当`tx`属性不存在时才插入，存在则保持原值
  UPDATE keywords
  SET locs = json_insert(locs, '$.tx', 1)
  WHERE kwd = 'android';
  

• json_replace()：仅更新已存在的属性，如果属性不存在，不会新增

同时操作多个属性 #

另外json_set()也支持一次操作多个属性，比如同时更新ny和新增chicago：

UPDATE keywords
SET locs = json_set(locs, '$.ny', 3, '$.chicago', 2)
WHERE lang = 2 AND kwd = 'json';

需要注意的是，你的locs字段是TEXT类型存储的JSON字符串，JSON1扩展会自动识别并处理这类字段，不需要额外的类型转换~

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