如何在Azure Search中合并一对多的Azure SQL数据源并生成嵌套索引？

场景说明 #

我有两个存在一对多（1..*）关系的表：

表 Product #

表 ProductVersion #

希望在Azure Search中合并这两个数据源，生成如下格式的文档索引：

{
    "Id": "1",
    "Name": "Product 1",
    "Versions": [
        {
            "Id": 10,
            "ProductId": 1,
            "Published": true
        },
        {
            "Id": 11,
            "ProductId": 1,
            "Published": false
        }        
    ]
}

我的尝试 #

创建索引 #

async Task CreateIndexAsync( string indexName, SearchIndexClient indexClient )
{
    FieldBuilder builder = new FieldBuilder();
    var definition = new SearchIndex( indexName, builder.Build( typeof( Product ) ) );

    await indexClient.CreateIndexAsync( definition );
}

创建并运行Product数据源的索引器 #

SearchIndexerDataSourceConnection dataSource =
    await indexerClient.GetDataSourceConnectionAsync( dataSourceName );

Console.WriteLine( "Creating SQL indexer..." );

SearchIndexer dbIndexer = new SearchIndexer(
    name: "lq-product-indexer",
    dataSourceName: dataSource.Name,
    targetIndexName: indexName )
{
    Schedule = new IndexingSchedule( TimeSpan.FromDays( 1 ) )
};

try
{
    await indexerClient.GetIndexerAsync( dbIndexer.Name );
    // 若索引器已存在则重置
    await indexerClient.ResetIndexerAsync( dbIndexer.Name );
}
catch ( RequestFailedException ex ) when ( ex.Status == 404 )
{
    // 指定索引器不存在时会抛出404
}

await indexerClient.CreateOrUpdateIndexerAsync( dbIndexer );

Console.WriteLine( "Running SQL indexer..." );

try
{
    await indexerClient.RunIndexerAsync( dbIndexer.Name );
}
catch ( RequestFailedException ex ) when ( ex.Status == 429 )
{
    Console.WriteLine( "Failed to run indexer: {0}", ex.Message );
}

ProductVersion索引器的字段映射 #

创建并运行ProductVersion索引器的流程与上述基本一致，需添加以下字段映射：

dbIndexer.FieldMappings.Add(
    new FieldMapping( "ProductId" )
    {
        TargetFieldName = "Id"
    } );

模型定义 #

public record Product
{
    [SearchableField( IsKey = true )]
    public string Id { get; set; } = string.Empty;

    [SearchableField]
    public string Name { get; set; } = string.Empty;

    [SearchableField]
    public ProductVersion[] Versions { get; set; } = [];
}

public record ProductVersion
{
    [SearchableField( IsFilterable = true )]
    public string Id { get; set; } = string.Empty;

    [SearchableField( IsFilterable = true )]
    public string ProductId { get; set; } = string.Empty;

    [SimpleField( IsFilterable = true )]
    public bool Published { get; set; }
}

问题 #

无论如何尝试，索引中的Versions数组始终为空。

更新：解决方案 #

通过创建SQL视图，将关联数据以JSON数组的形式嵌入主表字段，再将该视图作为Azure Search的数据源，即可生成包含嵌套数组的索引。示例SQL视图如下：

CREATE VIEW [dbo].[vw_ASProduct]
AS
SELECT *, 
    -- 关联查询ProductMarketData并转为JSON数组
    (SELECT * 
        FROM dbo.vw_ProductMarketData 
        WHERE 
            dbo.vw_ProductMarketData.MarketId=dbo.vw_MarketProduct.MarketId AND 
            dbo.vw_ProductMarketData.VersionId=dbo.vw_MarketProduct.VersionId FOR JSON AUTO ) AS Texts,
    -- 关联查询ProductMarketDataImage并转为JSON数组
    (SELECT * 
        FROM dbo.vw_ProductMarketDataImage 
        WHERE 
            dbo.vw_ProductMarketDataImage.MarketId=dbo.vw_MarketProduct.MarketId AND 
            dbo.vw_ProductMarketDataImage.VersionId=dbo.vw_MarketProduct.VersionId FOR JSON AUTO ) AS Images,
    -- 关联查询ProductMarketDataDocument并转为JSON数组
    (SELECT * 
        FROM dbo.vw_ProductMarketDataDocument 
        WHERE 
            dbo.vw_ProductMarketDataDocument.MarketId=dbo.vw_MarketProduct.MarketId AND 
            dbo.vw_ProductMarketDataDocument.VersionId=dbo.vw_MarketProduct.VersionId FOR JSON AUTO ) AS Documents
FROM dbo.vw_MarketProduct 
WHERE dbo.vw_MarketProduct.VersionName='PKA' and dbo.vw_MarketProduct.Published=1
GO

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