How to query fields with multiple values in Azure Cognitive Search

Working on Azure Cognitive Search with backend as MS SQL table, have some scenarios where need help to define a query.

Sample table structure and data :

Scenarios 1 : Need to define a query which will return data based on category. I have tied query using search.ismatch but its uses prefix search and matches other categories as well with similar kind of values i.e. "Embedded" and "Embedded Vision"

$filter=Region eq 'AA' and search.ismatch('Embedded*','Category')

https://{AZ_RESOURCE_NAME}.search.windows.net/indexes/{INDEX_NAME}/docs?api-version=2020-06-30-Preview&$count=true&$filter=Region eq  'AA' and search.ismatch('Embedded*','Category')

And it will response with below result, where it include "Embedded" and "Embedded Vision" both categories.

But my expectation is to fetch data only if it match "Embedded" category, as highlighted below

Scenario 2: For the above Scenario 1, Need little enhancement to find records with multiple category

For example if I pass multiple categories (i.e. "Embedded" , "Automation") need below highlighted output

Solution

To solve above mention problem used a below SQL function which will convert category to a json string array supported by Collection(Edm.String) data type in Azure Search.

Sql Function

CREATE FUNCTION dbo.GetCategoryAsArray
(
    @ID VARCHAR(20)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    
    DECLARE @result NVARCHAR(MAX) = ''

    SET @result = REPLACE( 
        STUFF(
            (SELECT 
                ','''+ TRIM(Value) + ''''
             FROM dbo.TABLEA p
                CROSS APPLY STRING_SPLIT (Category, ';')
             WHERE p.ID = @ID
                FOR XML PATH('')
            ),1,1,''),'&amp;','&')
    RETURN '[' + @result + ']'
END
GO

View to use function and return desired data

CREATE View dbo.TABLEA_VIEW AS
select 
       id
      ,dbo. GetCategoryAsArray(id) as CategoryArr
      ,type
      ,region
      ,Category
from dbo.TABLEA

Defined a new Azure Search Index using above SQL View as data source and during Index column mapping defined CategoryArr column as Collection(Edm.String) data type

Query to use to achieve expected output from Azure Search

$filter=Region eq  'AA' and CategoryArr/any(c: search.in(c, 'Embedded, Automation'))