Combine 2 Azure SQL tables into 1 index in Azure Cognitive Search

Question

I'm following this example on how to combine 2 different data sources into 1 index and the result is exactly what i want: Indexed Hotels from one data source each with an array (or list) of Room details from another data source.

In my case, both my datasources come from one Azure SQL database which doesn't use partition keys:

        DataSource hotelSource = DataSource.AzureSql(
            name: "hotels-sql",
            sqlConnectionString: Configuration["ConnectionStrings"],
            tableOrViewName: "hotels");
        hotelSource.DataChangeDetectionPolicy = new SqlIntegratedChangeTrackingPolicy();

        DataSource roomSource = DataSource.AzureSql(
            name: "rooms-sql",
            sqlConnectionString: Configuration["ConnectionStrings"],
            tableOrViewName: "rooms");
        roomSource.DataChangeDetectionPolicy = new SqlIntegratedChangeTrackingPolicy();

I set up the index like so:

    fields = new List<Field>
    {
        Field.New("Id", DataType.String, isKey: true),
        Field.New("Name", DataType.String, isSearchable: true, isRetrievable: false, analyzerName: AnalyzerName.StandardLucene),
        Field.New("Description", DataType.String, isSearchable: true, isRetrievable: false, analyzerName: AnalyzerName.StandardLucene),
        Field.New("Category", DataType.String, isRetrievable: false, isFilterable: true),
        new Field("Rooms", DataType.Collection(DataType.Complex), new List<Field>
        {
            Field.New("Name", DataType.String, isRetrievable: false, isSearchable: true, analyzerName: AnalyzerName.StandardLucene),
            Field.New("Description", DataType.String, isRetrievable: false, isSearchable: true, analyzerName: AnalyzerName.StandardLucene),
            Field.New("Category", DataType.String, isRetrievable: false, isFilterable: true)
        })
    };

    var definition = new Index()
    {
        Name = indexName,
        Fields = fields,
        ScoringProfiles = new List<ScoringProfile>
        {
            new ScoringProfile("main", new TextWeights(new Dictionary<string, double>
            {
                {"Name", 1},
                {"Description", 0.8},
                {"Rooms/Name", 0.4},
                {"Rooms/Description", 0.3}
            }))
        },
        DefaultScoringProfile = "main"
    };

    Index index = searchService.Indexes.Create(definition);

I set up the mappings like so:

Indexer hotelIndexer = new Indexer(
                name: "hotels-indexer",
                dataSourceName: hotelSource.Name,
                targetIndexName: index.Name,
                schedule: new IndexingSchedule(TimeSpan.FromMinutes(5)));

List<FieldMapping> map = new List<FieldMapping> {
                new FieldMapping("HotelId", "Id")
            };

Indexer roomIndexer = new Indexer(
                name: "rooms-indexer",
                dataSourceName: roomSource.Name,
                targetIndexName: index.Name,
                fieldMappings: map,
                schedule: new IndexingSchedule(TimeSpan.FromMinutes(5)));

The Rooms table contains a column 'HotelId' which points to the Id of the Hotel it belongs to.

The result should be that the Rooms list in the Hotels index is filled with Rooms by the roomIndexer however the actual result is that the Rooms are indexed alongside the Hotels as if they were Hotels themselves. The Rooms list remains empty.

I hope I provided enough information with this.

Answer 1

Azure Search does not support appending to a collection field (Rooms in the Hotels index) - it seems that you've modeled your data expecting that it does.

Instead, you could try flattening all rooms in a hotel into a single field (probably in stringified JSON representation that Azure Search can consume).