SQLite中基于JSON字段的多标签匹配查询最优方案咨询

一、查找包含至少一个指定标签的文档 #

方法1：使用json_overlap()（推荐，SQLite 3.38.0+） #

json_overlap()可直接判断两个JSON数组是否存在交集，语法简洁高效：

SELECT path, frontmatter
FROM file
WHERE json_overlap(json_extract(frontmatter, '$.tags'), '[?, ?]');

将[?, ?]替换为目标标签数组（如['a','c']），或通过参数绑定动态传入。

方法2：使用json_each()结合EXISTS子查询 #

兼容较低版本SQLite，通过遍历标签数组判断是否存在匹配项：

SELECT path, frontmatter
FROM file
WHERE EXISTS (
    SELECT 1
    FROM json_each(json_extract(frontmatter, '$.tags'))
    WHERE value IN ('a', 'c') -- 替换为需要匹配的标签
);

二、查找包含全部指定标签的文档 #

方法1：使用json_intersection()+json_array_length()（SQLite 3.40.0+） #

通过比较交集长度与目标标签数组长度，判断是否包含所有标签：

SELECT path, frontmatter
FROM file
WHERE json_array_length(
    json_intersection(json_extract(frontmatter, '$.tags'), '[?, ?]')
) = json_array_length('[?, ?]');

注意目标标签数组需前后一致，或通过参数绑定统一传入。

方法2：使用json_each()统计匹配数量 #

兼容低版本SQLite，统计匹配标签数是否等于目标标签总数：

SELECT path, frontmatter
FROM file
WHERE (
    SELECT COUNT(DISTINCT value)
    FROM json_each(json_extract(frontmatter, '$.tags'))
    WHERE value IN ('a', 'b') -- 替换为目标标签
) = 2; -- 此处数字为目标标签的总数量

若标签无重复，DISTINCT可省略；若存在重复标签，需根据实际场景调整逻辑。

补充说明 #

• 所有方案均无需将标签物化到单独表，直接操作JSON字段即可实现需求。
• 若使用的SQLite版本较低，优先选择基于json_each()的方案；版本满足要求时，json_overlap()和json_intersection()的性能更优。

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