如何在SQLite的JSON列中兼容数组与字符串的包含查询？

可以通过json_type()函数先判断value.key的JSON类型，分情况处理数组和字符串的匹配逻辑，同时避免非数组/字符串类型导致的报错：

SELECT * 
FROM logs 
WHERE 
  -- 处理key为数组的情况：检查数组中是否包含'a'
  (json_type(logs.value, '$.key') = 'array' 
   AND EXISTS (
     SELECT * 
     FROM json_each(json_extract(logs.value, '$.key')) 
     WHERE json_each.value = 'a'
   ))
  OR 
  -- 处理key为字符串的情况：用LIKE判断是否包含'a'
  (json_type(logs.value, '$.key') = 'text' 
   AND json_extract(logs.value, '$.key') LIKE '%a%');

逻辑说明 #

• json_type(logs.value, '$.key')：获取value.key的JSON类型，返回值包括array、text、number、null等，通过类型判断实现精准分支处理
• 数组分支：仅当类型为数组时，才执行json_each遍历检查，避免字符串传入json_each引发的报错
• 字符串分支：当类型为字符串时，直接用LIKE模糊匹配包含'a'的内容
• 其他情况（如key不存在、key为数字/布尔值等）：两个条件均不满足，不会被选中，也不会触发报错

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