如何通过Azure Search复杂类型实现关联表聚合值索引并保留内置变更跟踪？

Absolutely! You can ditch that database view and use Azure Search's features to get the aggregated data you need while keeping built-in change data capture (CDC) for incremental updates. The problem with your current view setup is that SQL Server CDC doesn't work with views—it only tracks changes on base tables. By structuring your search index and indexer to work directly with your person and linkedtable tables, you’ll get both the link count and automatic incremental updates.

Step 1: Enable CDC on your base tables #

First, make sure CDC is enabled on both your person and linkedtable tables in SQL Server. This is non-negotiable because you want Azure Search to pick up changes in either table—like when a new link is added to linkedtable, which should update the corresponding person's number_of_links value.

Step 2: Define your Azure Search index #

You’ve got two solid options here, depending on whether you ever need more data from linkedtable beyond just a count:

Option 1: Calculated field for link count (simplest for your current need) #

Create an index with core person fields, a hidden collection field to store linked record IDs, and a calculated field that counts the length of that collection (your number_of_links):

{
  "name": "person-details-index",
  "fields": [
    { "name": "id", "type": "Edm.String", "key": true, "filterable": true },
    { "name": "full_name", "type": "Edm.String", "searchable": true },
    { "name": "first_name", "type": "Edm.String", "searchable": true },
    { "name": "middle_name", "type": "Edm.String", "searchable": true },
    { "name": "last_name", "type": "Edm.String", "searchable": true },
    { "name": "linked_record_ids", "type": "Collection(Edm.String)", "retrievable": false }, // Hidden from search results
    { "name": "number_of_links", "type": "Edm.Int32", "calculatedFields": "length(linked_record_ids)", "filterable": true }
  ]
}

Option 2: Complex type for full linked table details #

If you might need to include extra data from linkedtable later (not just a count), define a complex collection type instead:

{
  "name": "person-details-index",
  "fields": [
    // Core person fields same as above
    { "name": "links", "type": "Collection(Edm.ComplexType)", "fields": [
        { "name": "link_id", "type": "Edm.String" },
        // Add any other fields from linkedtable you might need
      ]
    },
    { "name": "number_of_links", "type": "Edm.Int32", "calculatedFields": "length(links)", "filterable": true }
  ]
}

Step 3: Configure your Azure Search Indexer #

Set up an indexer that connects to your SQL database, links the two tables, and enables CDC for incremental updates:

1. Create a data source pointing to your SQL Server database (use your DB's connection string).
2. Build the indexer with these key settings:
   • Link it to your data source and target index.
   • Use field mappings to pull in person data and map linked records to your collection field.
   • Enable the SqlIntegratedChangeTrackingPolicy to leverage SQL Server's CDC. This will automatically detect changes to either table and only update the affected search documents.

Here’s a sample indexer definition snippet:

{
  "name": "person-details-indexer",
  "dataSourceName": "your-sql-datasource",
  "targetIndexName": "person-details-index",
  "changeDetectionPolicy": {
    "@odata.type": "#Microsoft.Azure.Search.SqlIntegratedChangeTrackingPolicy"
  },
  "fieldMappings": [
    { "sourceFieldName": "id", "targetFieldName": "id" },
    { "sourceFieldName": "full_name", "targetFieldName": "full_name" },
    // Map other person fields here
    { "sourceFieldName": "linkedtable.id", "targetFieldName": "linked_record_ids" }
  ]
}

Why this beats using a view #

• CDC works natively: Since you’re working directly with base tables, Azure Search can track incremental changes. When a person’s data updates or a linked record is added/removed, only the relevant search document gets updated.
• Flexibility: If you need to add more linked table data later, you can expand the complex type without modifying a database view.
• Less database load: The aggregation (counting links) happens on the search side, reducing overhead on your SQL Server.

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