如何从Room数据库EntriesTable的data列表中按url删除指定DataTable？

问题分析 #

你的核心需求是通过DataTable的url字段，删除EntriesTable中data列表（已被TypeConverter转为JSON数组存储）对应的条目。原SQL语句存在字段名错误、JSON函数用法不当的问题，导致执行失败。

正确的SQL查询语句 #

在Room中，需要通过json_each遍历JSON数组找到目标元素的索引，再用json_remove删除对应位置的元素。正确的@Query注解代码如下：

@Query("""
    UPDATE entries_table 
    SET data = json_remove(data, '$[' || (
        SELECT idx 
        FROM entries_table, json_each(entries_table.data) 
        WHERE entries_table.name = :name AND json_extract(json_each.value, '$.url') = :url
    ) || ']') 
    WHERE name = :name
""")
fun removeDataEntryByNameAndUrl(name: String, url: String): Int

代码解释 #

• 字段修正：将原语句中的links改为实体类对应的data字段，确保更新的是正确列。
• 索引查找：通过json_each(entries_table.data)遍历data列的JSON数组，结合json_extract(json_each.value, '$.url') = :url筛选出url匹配的元素，获取其索引idx。
• JSON数组删除：用字符串拼接构造$[索引]的JSON路径，传递给json_remove函数，实现删除指定索引的数组元素。
• 返回值：函数返回Int类型，代表被修改的行数（如果没有匹配条目则返回0）。

注意事项 #

• SQLite版本支持：确保Room依赖的SQLite版本不低于3.14.0（Room 2.2.0+通常满足此要求），因为json_each和json_remove是SQLite 3.14.0引入的函数。
• TypeConverter正确性：确认EntriesTypeConverter将List<DataTable>正确序列化为JSON数组格式（例如[{"name":"xxx","url":"xxx","addon":"xxx"},...]），否则JSON函数无法正常解析。
• 多匹配处理：如果data数组中存在多个url相同的条目，上述语句只会删除第一个匹配的条目。若需要删除所有匹配条目，需循环执行或使用更复杂的JSON操作。

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