question

VaibhavChaudhari avatar image
0 Votes"
VaibhavChaudhari asked ajkuma-MSFT edited

Search data in multiple tables


Let’s say, I have 2 tables in Azure SQL – Employee & Address and both the tables have common field, say Address Id.

Questions:

  1. If I create separate indexes for tables, can I search the data in both indexes from single search API? Is it possible to join 2 indexes? Just cross checking if such functionality exists.

  2. Another option I am aware of is – load data from both the tables to single index using solution given in below. Is this possible only via .NET API? Can we develop it from Portal?
    https://docs.microsoft.com/en-us/azure/search/tutorial-multiple-data-sources

  3. Any other recommended approach?

azure-cognitive-search
· 1
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VaibhavChaudhari avatar image VaibhavChaudhari ·

@SnehaAgrawal-MSFT - Can you please help here. I have posted the question in internal discussion group as well but no response. High level suggestions/confirmations are welcome too.

0 Votes 0 ·

1 Answer

ajkuma-MSFT avatar image
0 Votes"
ajkuma-MSFT answered ajkuma-MSFT edited

@VaibhavChaudhari, Apologies for the delay in responding here. To benefit the community find the answer, I'm copying the response from one of our PG, on which you'd started an internal discussion.


  1. No, you cannot query multiple indexes with a single request, but you can issue two requests to query either table.
    Azure Search FAQ: Can I search across multiple indexes? No, this operation is not supported. Search is always scoped to a single index.


  2. The .NET SDK calls into the REST APIs for our service. Anything you can do in the SDK you can do via the APIs as well.
    If you're asking whether two indexers can be configured to target 2 different datasources and the same single index, then yes that is also possible.

The index writes made by the indexer are all of type “mergeOrUpload” which means that it will only update fields that have non-null values.
As long as your indexers are configured to populate different fields within the target index, you should be able to compose multiple datasources into the same index.

Thanks for the great question and your patience!

5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.