Skip to main content

Return to Question

Update with solution.
Source Link
Tungviktarn
Tungviktarn
  • 17
  • 4

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

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
deleted 308 characters in body; edited title
Source Link
Thom A ♦
Thom A ♦
  • 97.7k
  • 12
  • 67
  • 102

Azure Search - how How do I merge two Azure SQL data sources?

Table 'Product' | Id | Name | | -- | ---- | | 1 | Product 1 | | 2 | Product 2 |

IdName
1Product 1
2Product 2

Table 'ProductVersion' | Id | ProductId | Published | | -- | --------- | --------- | | 10 | 1 | 1 | | 11 | 1 | 0 |

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

        await indexClient.CreateIndexAsync( definition );
    }

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

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

Azure Search - how do I merge two Azure SQL data sources?

Table 'Product' | Id | Name | | -- | ---- | | 1 | Product 1 | | 2 | Product 2 |

Table 'ProductVersion' | Id | ProductId | Published | | -- | --------- | --------- | | 10 | 1 | 1 | | 11 | 1 | 0 |

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

        await indexClient.CreateIndexAsync( definition );
    }

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

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

How do I merge two Azure SQL data sources?

Table 'Product'

IdName
1Product 1
2Product 2

Table 'ProductVersion'

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

    await indexClient.CreateIndexAsync( definition );
}

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

dbIndexer.FieldMappings.Add(
    new FieldMapping( "ProductId" )
    {
        TargetFieldName = "Id"
    } );
Source Link
Tungviktarn
Tungviktarn
  • 17
  • 4

Azure Search - how do I merge two Azure SQL data sources?

I have two tables with 1..* relation:

Table 'Product' | Id | Name | | -- | ---- | | 1 | Product 1 | | 2 | Product 2 |

Table 'ProductVersion' | Id | ProductId | Published | | -- | --------- | --------- | | 10 | 1 | 1 | | 11 | 1 | 0 |

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.