SQLite中移除JSON列指定嵌套对象并更新数据的方法

问题背景 #

需要更新SQLite表的ArrayColumn列，移除其中pair数组里满足field1="0"且field2="1"的对象。部分行包含该目标对象，需精准移除，保留其他数据结构不变。

可行解决方案 #

方法1：重新构建过滤后的数组（推荐，通用可靠）

无需定位元素索引，直接遍历数组并过滤目标对象，重新生成pair数组替换原内容：

UPDATE your_table
SET ArrayColumn = json_set(
  ArrayColumn,
  '$.pair',
  (
    SELECT json_group_array(obj.value)
    FROM json_each(your_table.ArrayColumn, '$.pair') AS obj
    WHERE NOT (
      json_extract(obj.value, '$.field1') = '0'
      AND json_extract(obj.value, '$.field2') = '1'
    )
  )
);

原理说明：

1. json_each(your_table.ArrayColumn, '$.pair')：遍历pair数组的每个元素
2. WHERE子句：排除掉符合field1="0"且field2="1"的对象
3. json_group_array(obj.value)：将剩余元素重新组合成JSON数组
4. json_set：把新生成的数组替换回原JSON的pair字段

方法2：通过索引定位移除（适合单目标对象场景）

先找到目标元素在数组中的索引，再用json_remove删除：

1. 先查询验证目标元素的索引

SELECT 
  Id,
  SUBSTR(json_tree.path, 3) AS element_index
FROM 
  your_table,
  json_tree(your_table.ArrayColumn, '$.pair')
WHERE 
  json_extract(json_tree.value, '$.field1') = '0'
  AND json_extract(json_tree.value, '$.field2') = '1';

2. 执行更新

WITH target_indices AS (
  SELECT 
    Id,
    SUBSTR(json_tree.path, 3) AS element_index
  FROM 
    your_table,
    json_tree(your_table.ArrayColumn, '$.pair')
  WHERE 
    json_extract(json_tree.value, '$.field1') = '0'
    AND json_extract(json_tree.value, '$.field2') = '1'
)
UPDATE your_table
SET ArrayColumn = json_remove(ArrayColumn, '$.pair[' || target_indices.element_index || ']')
FROM target_indices
WHERE your_table.Id = target_indices.Id;

注意事项 #

• 确保你的SQLite版本≥3.38.0，json_tree、json_each等JSON函数在该版本及以上才完全支持。
• 方法1可处理同一行pair数组中存在多个目标对象的情况，方法2默认只移除第一个匹配的对象（若有多个需递归处理）。

验证结果 #

执行更新后，可通过以下语句确认结果：

SELECT Id, ArrayColumn FROM your_table;

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