如何在SQLite中判断JSON列表与外部列表是否存在交集？

这个问题确实挺常见的——SQLite本身没有直接的数组交集运算符，尤其是当数组以JSON格式存储的时候。你之前用IN语句没效果，是因为IN是用来匹配整个值的，而不是JSON数组里的单个元素。下面给你详细的解决方案：

为什么原方法失效？ #

你原来的selectedDates IN (:customDates)写法，SQLite会把selectedDates整个JSON字符串和customDates列表里的每个元素（转成字符串后）做完全匹配，而不是检查数组内的元素是否有重叠，所以自然得不到想要的结果。

可行解决方案：利用SQLite JSON函数展开数组 #

SQLite 3.33.0及以上版本支持json_each()函数，它可以把JSON数组拆分成单行的记录。我们可以利用这个函数，把selected_dates和传入的自定义日期列表都展开，然后检查是否有匹配的元素。

适配Room的查询写法 #

因为Room无法直接把List<ScheduleType.Dates>转换成SQL可处理的JSON数组，所以我们可以先把列表转成JSON字符串，再传入查询。修改后的DAO方法如下：

@Query("""
    SELECT * FROM schedule_table 
    WHERE (type_daily IS 1) 
       OR (:monthOfYear IS 1) 
       OR EXISTS (
           SELECT 1 
           FROM json_each(schedule_table.selected_dates) AS sd
           JOIN json_each(:customDatesJson) AS cd
           ON sd.value = cd.value
       )
    ORDER BY scheduleID DESC
""")
LiveData<List<Schedule>> getSchedulesOfThisWeek(String monthOfYear, String customDatesJson);

调用方法示例 #

在调用这个DAO方法时，需要把List<ScheduleType.Dates>转成标准的JSON字符串。比如用Gson工具：

val gson = Gson()
val customDatesJson = gson.toJson(customDates)
scheduleDao.getSchedulesOfThisWeek(monthOfYear, customDatesJson)

查询逻辑说明 #

1. json_each(schedule_table.selected_dates)：把每条记录的selected_dates数组拆成一行行的JSON对象字符串
2. json_each(:customDatesJson)：把传入的自定义日期数组也拆成一行行的JSON对象字符串
3. JOIN ... ON sd.value = cd.value：检查两个展开后的集合是否有完全相等的JSON对象
4. 如果存在匹配的元素，EXISTS子查询返回true，这条记录就会被筛选出来

注意事项 #

• JSON格式一致性：确保selected_dates存储的JSON和你传入的customDatesJson格式完全一致（包括键名大小写、键的顺序，因为字符串匹配是严格的）。比如你的示例是{"dayOfMonth":1,"month":8}，传入的JSON也要保持同样的结构。
• SQLite版本要求：json_each()需要SQLite 3.33.0或更高版本，如果你用的是旧版Room，建议升级依赖以获得对应版本的SQLite。
• 性能优化建议：如果表数据量很大，这种展开JSON数组的查询可能会有性能瓶颈。如果频繁需要这类交集查询，建议考虑把日期数据拆分成单独的关联表（比如schedule_dates表，每条记录对应一个日程和一个日期），这样查询效率会更高。

内容的提问来源于stack exchange，提问作者Dont DownVote Please