如何在SQLite中查询JSON嵌套数组匹配子串的电影行？

SQLite其实支持JSON数组的迭代处理，直接用内置的JSON函数就能实现需求。以下是具体的解决方案：

基础SQL查询语句 #

假设你的表名为films，直接写SQL的话可以这样写：

SELECT *
FROM films
WHERE EXISTS (
    SELECT 1
    FROM json_each(films.movie, '$.cast') AS cast_member
    WHERE json_extract(cast_member.value, '$.name') LIKE '%关键词%'
);

语句说明： #

• json_each(films.movie, '$.cast')：将movie列里的cast数组拆分成单独的行记录，每个演员对象对应一行
• json_extract(cast_member.value, '$.name')：从拆分出的演员对象中提取name字段值
• LIKE '%关键词%'：替换关键词为你要匹配的子串，比如'%Joe%'就能匹配包含Joe的演员姓名

ORM中使用whereRaw的写法（以Sequelize为例） #

如果是用Sequelize这类ORM框架，whereRaw可以这么写：

Model.findAll({
  whereRaw: `EXISTS (
    SELECT 1
    FROM json_each(movie, '$.cast') AS cast_member
    WHERE json_extract(cast_member.value, '$.name') LIKE ?
  )`,
  replacements: ['%关键词%']
});

兼容旧版本SQLite的替代方案 #

如果你的SQLite版本过低（不支持json_each），可以用字符串模糊匹配兜底，但这种方法可能存在误匹配风险（比如其他字段有类似字符串时）：

SELECT *
FROM films
WHERE movie LIKE '%name":"%关键词%"%';

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