CODEWITHSUNDEEP
azureelasticsearchazure-cognitive-search
user7995357
user7995357

Reputation: 151

Azure Search- replicating result of nested SQL query

I have a database comprising of the following schema depicting the linkage between individuals who connect with multiple Advisors and these Advisors have affiliations with multiple organizations

Individuals--> Advisors (m:n relationship)
Advisors --> Enterprises (m:n relationship)

The business need is to enable search on all these concepts and organize results around AdvisorIds. As an example, display of a search result could be as follows

a) Advisor1-> connected to Individuals A,B,C; and linked to Enterprises X,Y

b) Advisor2-> connected to Individuals A, E; and linked to Enterprises M,X,Z

Towards this, we created a flattened table on these concepts and the relationship between them. Hence the same AdvisorId would appear in multiple rows

When I search for a string, I want to ensure that ALL records around an AdvisorId to be returned together irrespective of search score of the individual records.

One approach could be

 a) first run an Azure Search and get a result of AdvisorId, ordered by search score of each record. This will repeat Advisor Ids

b) take a distinct set of  AdvisorIds (across pages) via standard SQL

c) for each AdvisorId, pick all the related records via standard SQL

2 questions

Here a lot of processing in (b) and (c) will be done outside Azure leading to delays. Also, if I were to use pagination for (a), I am never sure of number of AdvisorId's, I end up with after the distinct operation

I wanted to check if there is a way to get the nested search implemented in Azure to do (a), (b) and (c) as a single API call

If I were to use facets for handling (a) and (b) together, how do I ensure that the ordering is based on the best search-score document within a facet

Upvotes: 1

Views: 208

Answers (1)

Bruce Johnston
Bruce Johnston

Reputation: 8634

There isn't a way to achieve what you want in a single request unless you model your data differently. Instead of denormalizing the Individuals-Advisors and Advisors-Enterprises relationships, it may be possible instead to have one document per Advisor and use collections to store information about the related Individuals and Enterprises. This may or may not work for you depending on whether you need to supported correlated filtering on Individuals and Enterprises that are related to an Advisor. There is a whitepaper here that should help you evaluate whether this approach would work for you.

Another option might be to model Individuals, Advisors, and Enterprises as separate indexes, issue three queries, and do a client-side join. However, this is limited by the number of Advisor IDs you'd need to send in the queries on Individuals and Enterprises. Azure Search has limits on the size of filters that can make this impractical unless your queries have low recall.

We are working on making Azure Search better for scenarios like yours. For example, we're currently working on adding support for complex types. Please vote on User Voice and feel free to suggest other features that would help.

Upvotes: 1

Related Questions