azure-search-indexers-indexing-collections_1.sql

CREATE FUNCTION [dbo].[getCategoriesFlat] (
@ProductCategoryID INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN

	DECLARE @result VARCHAR(MAX);

	WITH Hierarchy (ProductCategoryID, ParentProductCategoryID, Name, [Level], Flat)
	AS
	(
		SELECT 
			pc.ProductCategoryID,
			pc.ParentProductCategoryID,
			pc.Name,
			0 AS [Level],
			CAST(' ''' + pc.Name + ''' ' AS VARCHAR(MAX))
		FROM SalesLT.ProductCategory AS pc
		WHERE ProductCategoryID = @ProductCategoryID
		UNION ALL
		SELECT 
			pcc.ProductCategoryID,
			pcc.ParentProductCategoryID,
			pcc.Name,
			Level + 1 AS [Level],
			CAST(h.Flat + ',  ''' + CAST(pcc.Name AS VARCHAR(MAX)) + ''' ' AS VARCHAR(MAX)) 
		FROM SalesLT.ProductCategory AS pcc
		INNER JOIN Hierarchy AS h ON h.ParentProductCategoryID = pcc.ProductCategoryID 
	)
	SELECT TOP 1 @result = Flat FROM Hierarchy
	ORDER BY [Level] DESC;

	RETURN '[' + @result + ']';

END
GO

azure-search-indexers-indexing-collections_2.sql

CREATE VIEW dbo.testview2 AS
	SELECT 
		ProductID as id,
		Name as name,
		dbo.getCategoriesFlat(p.ProductCategoryID) AS categories 
	FROM SalesLT.Product AS p

GO

azure-search-indexers-indexing-collections_3.json

 {  
   "name":"index1",
   "fields":[  
      {  
         "name":"id",
         "type":"Edm.String",
         "searchable":true,
         "filterable":false,
         "sortable":false,
         "facetable":false,
         "key":true,
         "retrievable":true
      },
      {  
         "name":"name",
         "type":"Edm.String",
         "searchable":true,
         "filterable":false,
         "sortable":false,
         "facetable":false,
         "key":false,
         "retrievable":true
      },
      {  
         "name":"categories",
         "type":"Collection(Edm.String)",
         "searchable":true,
         "filterable":false,
         "sortable":false,
         "facetable":false,
         "key":false,
         "retrievable":true
      }
   ]
}

azure-search-indexers-indexing-collections_3.sql

SELECT * FROM dbo.testview2 

azure-search-indexers-indexing-collections_4.json

 {  
   "name":"indexer1",
   "dataSourceName":"datasourcek",
   "targetIndexName":"indexert",
   "fieldMappings":[  
      {  
         "sourceFieldName":"id",
         "targetFieldName":"id"
      },
      {  
         "sourceFieldName":"name",
         "targetFieldName":"name"
      },
      {  
         "sourceFieldName":"categories",
         "targetFieldName":"categories",
         "mappingFunction":{  
            "name":"jsonArrayToStringCollection"
         }
      }
   ]
}

azure-search-indexers-indexing-collections_5.json

{
  "@odata.context": "https://mysearchservice.search.windows.net/indexes('index1')/$metadata#docs(id,name,categories)",
  "value": [
    {
      "@search.score": 2.0054593,
      "id": "877",
      "name": "Bike Wash - Dissolver",
      "categories": [
        "Cleaners",
        "Accessories"
      ]
    }
  ]
}