SonnetDB Hybrid Search：把全文 BM25 与向量相似度融合起来

只用全文搜索，可能会错过同义表达；只用向量搜索，又可能把关键字完全不匹配的文档排上来。SonnetDB 的 hybrid_search(...) 用一个 SQL 表值函数把两者合在一起：全文 BM25 提供关键词相关性，JSON embedding 提供语义相似度，最后按融合分数排序。

先准备文档集合：

using SonnetDB.Engine;
using SonnetDB.Sql;
using SonnetDB.Sql.Execution;

using var db = Tsdb.Open(new TsdbOptions
{
    RootDirectory = "data/hybrid-demo"
});

SqlExecutor.Execute(db, "CREATE DOCUMENT COLLECTION logs");

SqlExecutor.Execute(db, """
    INSERT INTO logs (id, document)
    VALUES
      ('log-1', '{"message":"Pump alarm overheating","site":"north","embedding":[1,0,0]}'),
      ('log-2', '{"message":"Pump alarm pressure","site":"south","embedding":[0.7,0.7,0]}'),
      ('log-3', '{"message":"Pump maintenance normal","site":"north","embedding":[0.95,0.05,0]}'),
      ('log-4', '{"message":"Fan alarm cleared","site":"south","embedding":[0,1,0]}')
    """);

SqlExecutor.Execute(db, """
    CREATE FULLTEXT INDEX ft_logs_message
    ON logs ('$.message')
    USING unicode
    """);

再执行融合查询：

var result = (SelectExecutionResult)SqlExecutor.Execute(db, """
    SELECT id,
           bm25_score() AS text_score,
           vector_distance() AS distance,
           hybrid_score() AS score
    FROM hybrid_search(
        source => logs,
        text_index => ft_logs_message,
        text_field => '$.message',
        text => 'pump alarm',
        vector_field => '$.embedding',
        vector => [1, 0, 0],
        k => 3,
        text_weight => 0.6,
        vector_weight => 0.4)
    ORDER BY score DESC
    """);

foreach (var row in result.Rows)
{
    Console.WriteLine($"id={row[0]}, bm25={row[1]}, dist={row[2]}, score={row[3]}");
}

hybrid_score() 的默认思路是：

hybrid_score = text_weight * normalized_bm25 + vector_weight * vector_score

你可以把 text_weight 调高，让关键词更强；也可以把 vector_weight 调高，让语义相似度更强。

加业务过滤

Hybrid Search 的结果还能继续过滤。例如只看 site = 'south'：

var south = (SelectExecutionResult)SqlExecutor.Execute(db, """
    SELECT id, site, hybrid_score() AS score
    FROM hybrid_search(
        source => logs,
        text => 'pump alarm',
        vector => [1, 0, 0],
        k => 10)
    WHERE site = 'south'
    ORDER BY score DESC
    """);

site 是 JSON 文档里的顶层字段。更复杂的 path 可以用 json_value(document, '$.path')。

为什么这对工业知识库实用

工业知识库经常同时包含设备告警、检修记录、操作手册和历史工单。用户输入“泵站过热报警”，系统既要命中“pump alarm overheating”，又要找到语义相近的“bearing temperature abnormal”。Hybrid Search 让这两个世界不必二选一。

---

官网地址：https://sonnetdb.com

技术文章站：https://iotpaper.net

开源仓库：https://github.com/IoTSharp/SonnetDB