Azure Search - How to index Many (DB items) to One (Azure Blob)

Question

We've got a custom database with a table "Assets". It has e.g. Title, and a reference to a blob in Azure Blob Storage. So, there may be 10 rows in "Assets" for the same Azure Blob.

When the user searches for a text that's in the text of the Azure Blob, it should return all linked "Assets". If that Blob was linked from 10 "Assets" it should display 10 results; all linking to the same Asset.

I looked at the example for multiple data sources, but that's a one-to-one relationship. In my case it's a many-to-one relationship. Is this possible? Or do I have to create logic when querying to combine the two data sources?

To clarify, some example data:

Assets (table in our db)

              Title                   Blob ID
Asset 1       Asset 1                 12345
Asset 2       Asset 2                 12345
Asset 3       Asset 3                 12345
Asset 4       Asset 4                 12345

Azure Blobs Will contain 1 document (Word, Excel, etc.) with ID 12345

When a user searches on a text that's contained in document 12345, it should display Asset 1, Asset 2, Asset 3, and Asset 4.

Answer 1

Here's something that you can try. Maybe a better way would be thinking your index in the following way, however, you'll need to extract the content from the blobs manually:

{
    "BlobId: "12345",
    "BlobContent": ".....",
    "Titles: ["Asset1", "Asset2", "Asset3", "Asset4"]
}