Azure认知搜索：如何将SQL子查找表作为筛选器且避免行重复

核心思路 #

利用SQL Server的持久化计算列将子表关联数据聚合为字符串集合，再通过认知搜索的字段映射转换为Collection(Edm.String)类型，同时兼容SQL变更检测实现增量更新，无需依赖关联视图或全量同步。

1. 数据库端配置：添加持久化计算列 #

在主表中新增一个持久化计算列，自动聚合关联子表的筛选值（如国家名称）为逗号分隔的字符串：

-- 假设主表名为MainTable，子表名为CountryLookup，关联字段为MainTable.Id = CountryLookup.MainTableId
ALTER TABLE MainTable 
ADD CountryCollection AS (
    SELECT STRING_AGG(CountryLookup.CountryName, ',') 
    FROM CountryLookup 
    WHERE CountryLookup.MainTableId = MainTable.Id
) PERSISTED;

• PERSISTED关键字确保计算列的值被物理存储，且当子表关联数据变化时，SQL Server会自动更新该列并标记主表行的变更，为后续增量更新提供支持。
• 若子表数据变更未触发主表计算列更新，可在子表上创建触发器，更新主表的一个时间戳列（如LastUpdated），确保变更检测能捕获到关联数据的变化：

  CREATE TRIGGER Trigger_UpdateMainTableOnCountryChange
  ON CountryLookup
  AFTER INSERT, UPDATE, DELETE
  AS
  BEGIN
      UPDATE MainTable
      SET LastUpdated = GETUTCDATE()
      FROM MainTable
      JOIN (
          SELECT DISTINCT MainTableId FROM inserted
          UNION
          SELECT DISTINCT MainTableId FROM deleted
      ) AS ChangedIds ON MainTable.Id = ChangedIds.MainTableId;
  END
  

2. 认知搜索索引与映射配置 #

• 索引字段创建：在Azure认知搜索索引中定义一个Collection(Edm.String)类型的字段，例如countries，用于存储子表的筛选值集合。
• 字段映射配置：在索引器的字段映射中，将主表的CountryCollection列通过split函数转换为字符串集合：

  {
    "sourceFieldName": "CountryCollection",
    "targetFieldName": "countries",
    "mappingFunction": {
      "name": "split",
      "parameters": { "delimiter": "," }
    }
  }
  

3. 启用增量更新（变更检测） #

• 开启主表的变更检测：

  ALTER TABLE MainTable ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
  

• 在认知搜索索引器配置中，选择“变更检测”作为增量更新模式，指定主表的主键和变更检测列（可使用SQL自动生成的变更追踪列，或自定义的LastUpdated列）。
• 此时，无论是主表本身的数据变更，还是子表关联数据触发的主表计算列/时间戳列变更，索引器都会只同步变化的行，无需全量扫描。

4. 筛选查询使用 #

配置完成后，即可在搜索查询中使用集合筛选语法，例如：

$filter=countries/any(c: c eq '中国')

来筛选关联了指定国家的主表数据。

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