I have two tables with 1..* relation:

Table 'Product'

I have two tables with 1..* relation:

Table 'Product'

Table 'ProductVersion'

Now I want to merge these data sources in Azure Search to a document index that looks like this:

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

My attempt:

Create index:

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

    await indexClient.CreateIndexAsync( definition );
}

Create and run indexer for Product data source:

SearchIndexerDataSourceConnection dataSource =
    await indexerClient.GetDataSourceConnectionAsync( dataSourceName );

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

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 );
    //Rest the indexer if it exsits.
    await indexerClient.ResetIndexerAsync( dbIndexer.Name );
}
catch ( RequestFailedException ex ) when ( ex.Status == 404 )
{
    //if the specified indexer not exist, 404 will be thrown.
}

await indexerClient.CreateOrUpdateIndexerAsync( dbIndexer );

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

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

Create and run indexer for ProductVersion is basibally the same but with field mapping:

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

And here's my model:

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; }
}

No matter what I try, Versions array always ends up empty in the index.

UPDATE

Solution based on Balaji's comment:

CREATE VIEW [dbo].[vw_ASProduct]
AS
SELECT *, 
    (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,
    (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,
    (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

• merge
• collections
• azure-sql-database
• azure-ai-search

• 1 The issue is that Azure Cognitive Search doesn't automatically merge related tables into a nested array. By default, each table is indexed separately, and there is no built-in way to "join" them inside the indexing process. Instead, use a SQL View to Pre-Aggregate Data – Balaji Commented Mar 28 at 11:12
• @Balaji Thank you! I updated the post with my solution following the steps in this article: link – Tungviktarn Commented Mar 29 at 13:43

1 Answer 1

How do I merge two Azure SQL data sources?

As I mentioned in comments i tried in my environment by using SQL view and it successfully merged as you can see in the below output.

View in SQL:

CREATE VIEW [dbo].[vw_ASProduct] AS
SELECT 
    mp.Id AS ProductId,
    mp.VersionName,
    mp.Published,
    (SELECT * FROM dbo.vw_ProductMarketData 
        WHERE dbo.vw_ProductMarketData.MarketId = mp.MarketId 
          AND dbo.vw_ProductMarketData.VersionId = mp.VersionId 
        FOR JSON AUTO) AS Texts,
    (SELECT * FROM dbo.vw_ProductMarketDataImage 
        WHERE dbo.vw_ProductMarketDataImage.MarketId = mp.MarketId 
          AND dbo.vw_ProductMarketDataImage.VersionId = mp.VersionId 
        FOR JSON AUTO) AS Images,
    (SELECT * FROM dbo.vw_ProductMarketDataDocument 
        WHERE dbo.vw_ProductMarketDataDocument.MarketId = mp.MarketId 
          AND dbo.vw_ProductMarketDataDocument.VersionId = mp.VersionId 
        FOR JSON AUTO) AS Documents
FROM dbo.vw_MarketProduct mp 
WHERE mp.VersionName = 'PKA' 
  AND mp.Published = 1;

class Program
{
    static async Task Main(string[] args)
    {
        string searchServiceEndpoint = "https://<searchService>.search.windows";
        string searchServiceApiKey = "<key>";
        string sqlConnectionString = "Server=tcp:<server>.database.windows;Database=<db>;User ID=<userId>;Password=<pwd>;Trusted_Connection=False;Encrypt=True;";

        string dataSourceName = "product-data-source";
        string indexName = "product-index";
        string indexerName = "product-indexer";
        string sqlViewName = "vw_ASProduct";  

        var indexerClient = new SearchIndexerClient(
            new Uri(searchServiceEndpoint), 
            new AzureKeyCredential(searchServiceApiKey));

        var indexClient = new SearchIndexClient(
            new Uri(searchServiceEndpoint), 
            new AzureKeyCredential(searchServiceApiKey));

        await CreateDataSourceAsync(indexerClient, dataSourceName, sqlConnectionString, sqlViewName);
        await CreateIndexAsync(indexClient, indexName);
        await CreateIndexerAsync(indexerClient, dataSourceName, indexName, indexerName);
    }

    static async Task CreateDataSourceAsync(SearchIndexerClient indexerClient, string dataSourceName, string connectionString, string tableName)
    {
        Console.WriteLine("Creating Data Source...");

        var dataSource = new SearchIndexerDataSourceConnection(
            name: dataSourceName,
            type: SearchIndexerDataSourceType.AzureSql,
            connectionString: connectionString,
            container: new SearchIndexerDataContainer(tableName)
        );

        await indexerClient.CreateOrUpdateDataSourceConnectionAsync(dataSource);
        Console.WriteLine("Data Source Created Successfully!");
    }

    static async Task CreateIndexAsync(SearchIndexClient indexClient, string indexName)
    {
        Console.WriteLine("Creating Index...");

        var index = new SearchIndex(indexName)
        {
            Fields =
            {
                new SimpleField("Id", SearchFieldDataType.String) { IsKey = true, IsFilterable = true },
                new SearchableField("Name") { IsFilterable = true, IsSortable = true },

                new ComplexField("Versions", collection: true)  
                {
                    Fields = 
                    {
                        new SimpleField("Id", SearchFieldDataType.Int32) { IsFilterable = true },
                        new SimpleField("ProductId", SearchFieldDataType.Int32) { IsFilterable = true },
                        new SimpleField("Published", SearchFieldDataType.Boolean) { IsFilterable = true }
                    }
                },

                new ComplexField("Texts", collection: true),    
                new ComplexField("Images", collection: true),
                new ComplexField("Documents", collection: true)
            }
        };

        await indexClient.CreateOrUpdateIndexAsync(index);
        Console.WriteLine("Index Created Successfully!");
    }

    static async Task CreateIndexerAsync(SearchIndexerClient indexerClient, string dataSourceName, string indexName, string indexerName)
    {
        Console.WriteLine("Creating Indexer...");

        var indexer = new SearchIndexer(indexerName, dataSourceName, indexName)
        {
            Schedule = new IndexingSchedule(TimeSpan.FromDays(1)),

            FieldMappings =
            {
                new FieldMapping("ProductId") { TargetFieldName = "Id" },  
                new FieldMapping("Texts") { MappingFunction = new FieldMappingFunction("jsonParse") },
                new FieldMapping("Images") { MappingFunction = new FieldMappingFunction("jsonParse") },
                new FieldMapping("Documents") { MappingFunction = new FieldMappingFunction("jsonParse") }
            }
        };

        await indexerClient.CreateOrUpdateIndexerAsync(indexer);
        Console.WriteLine("Indexer Created Successfully!");

        await indexerClient.RunIndexerAsync(indexerName);
        Console.WriteLine("Indexer Running!");
    }
}

Output:

Creating Data Source...
Data Source Created Successfully!

Creating Index...
Index Created Successfully!

Creating Indexer...
Indexer Created Successfully!

Indexer Running!

Below is the data in the Azure Cognitive Search:

[
  {
    "Id": "1",
    "Name": "Product 1",
    "Versions": [
      {
        "Id": 10,
        "ProductId": 1,
        "Published": true
      },
      {
        "Id": 11,
        "ProductId": 1,
        "Published": false
      }
    ],
    "Texts": [
      { "MarketId": 1, "VersionId": 10, "Text": "Some Text" }
    ],
    "Images": [
      { "MarketId": 1, "VersionId": 10, "ImageUrl": "http://..." }
    ],
    "Documents": [
      { "MarketId": 1, "VersionId": 10, "DocumentUrl": "http://..." }
    ]
  },
  {
    "Id": "2",
    "Name": "Product 2",
    "Versions": [],
    "Texts": [],
    "Images": [],
    "Documents": []
  }
]