如何在ROOM构建的SQLite数据库中查询JSON列筛选指定状态的用户？

这个需求我之前做类似功能时碰到过，其实借助SQLite自带的JSON函数，结合Room的查询注解就能轻松实现，给你两种实用的方案：

方法1：直接使用json_extract函数精准匹配 #

SQLite从3.31.0版本开始支持完整的JSON处理函数，Room默认依赖的SQLite版本现在基本都满足这个要求。你可以用json_extract函数从user_status列的JSON字符串中提取指定键对应的值，再进行条件筛选。

比如你要筛选user1状态为1的用户，Dao层的查询可以这么写：

@Dao
interface UserDao {
    @Query("SELECT * FROM users WHERE json_extract(user_status, '$.user1') = 1")
    suspend fun getUsersWithUser1Active(): List<User>
}

这里的'$.user1'是JSONPath表达式，$代表整个JSON对象，.user1就是要提取的键名。

如果需要动态指定要查询的用户名和状态值，可以把参数传入：

@Query("SELECT * FROM users WHERE json_extract(user_status, :jsonPath) = :targetStatus")
suspend fun getUsersBySpecificStatus(jsonPath: String, targetStatus: Int): List<User>

// 调用示例：查询user2状态为0的用户
userDao.getUsersBySpecificStatus("$.user2", 0)

方法2：使用JSON运算符简化写法（可选） #

SQLite还支持->运算符作为json_extract的简写，不过在Room的@Query注解里需要注意语法兼容性，写法如下：

@Query("SELECT * FROM users WHERE user_status->'$.user1' = 1")
suspend fun getUsersWithUser1Active(): List<User>

效果和json_extract完全一致，只是写法更简洁。

注意事项 #

• 性能提示：如果你的用户表数据量很大，频繁做JSON字段的查询可能会比单独列查询慢一些。如果某个状态字段是高频查询的，建议考虑单独拆分到数据库列中，兼顾灵活性和性能。
• JSON格式校验：要确保存入user_status的JSON字符串格式是合法的，否则json_extract会返回null，导致筛选结果不符合预期。

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