SQLite正确更新表中JSON数组内指定对象字段的SQL写法

之前两条语句未达到预期的核心原因：

• 第一条SELECT语句仅做读操作，只会返回符合条件的修改后结果，不会对磁盘上存储的表数据做任何改动。
• 第二条错误UPDATE语句存在两个逻辑问题：一是子查询通过json_each遍历数组后返回的是逐行拆分的单个对象，二是json_set直接操作根路径$.am_pm，没有定位到数组内的具体元素索引，最终赋值时只会取子查询返回的第一行结果，直接把整个数组替换成单个对象，破坏原有结构。

SQLite的JSON函数操作数组内元素时，必须通过$[索引]格式的路径定位到具体元素，json_each遍历数组时返回的key字段就是对应元素的索引值，可以直接用来拼接路径。

更新单个匹配元素 #

针对修改指定part_uid对应项的am_pm字段的需求，语句如下：

UPDATE orders
SET items = (
    SELECT json_set(
        orders.items,
        '$[' || e.key || '].am_pm',
        'Tequilla'
    )
    FROM json_each(orders.items, '$') e
    WHERE json_extract(e.value, '$.part_uid') = '35f81391-392b-4d5d-94b4-a5639bba8591'
    LIMIT 1
)
WHERE id = 2;

注：加LIMIT 1是为了避免数组内存在多个同part_uid的项时，子查询返回多结果导致赋值异常。

批量更新数组内多个符合条件的元素 #

如果需要一次性修改数组内所有满足条件的元素（比如把所有supplier为XXX的项的am_pm都改为Tequilla），可以用递归CTE遍历数组逐个修改，语句如下：

WITH RECURSIVE
target AS (
    SELECT items AS original_items, json_array_length(items) AS arr_len
    FROM orders WHERE id = 2
),
iterate(idx, current_json) AS (
    SELECT 0, original_items FROM target
    UNION ALL
    SELECT 
        idx + 1,
        CASE
            WHEN json_extract(current_json, '$[' || idx || '].supplier') = 'XXX'
            THEN json_set(current_json, '$[' || idx || '].am_pm', 'Tequilla')
            ELSE current_json
        END
    FROM iterate, target
    WHERE idx < arr_len
)
UPDATE orders
SET items = (SELECT current_json FROM iterate, target WHERE idx = arr_len)
WHERE id = 2;

操作提示：执行UPDATE前建议先运行SELECT语句校验修改后的结果，确认JSON结构正确、字段值符合预期后再执行更新，避免误改数据：

-- 校验单条更新结果
SELECT json_set(
    orders.items,
    '$[' || e.key || '].am_pm',
    'Tequilla'
) AS updated_items
FROM orders, json_each(orders.items, '$') e
WHERE orders.id=2 
AND json_extract(e.value, '$.part_uid') = '35f81391-392b-4d5d-94b4-a5639bba8591'
LIMIT 1;

内容的提问来源于stack exchange，提问作者Barry the Platipus