How to index related SQL tables in Azure Search?

There are 2 related tables: categories and products. products table has field categoryId, which contains id of it's category. How to index products table to get name or anything else of it's category?

I have 2 indexers for each table and one index.

// categories indexer
    {
        "name": "categories",
        "dataSourceName": "categories",
        "targetIndexName": "products",
            "fieldMappings": [
            {
                "sourceFieldName": "id",
                "targetFieldName": "id"
            },
            {
                "sourceFieldName": "name",
                "targetFieldName": "name"
            }
    }

// products indexer
    {
        "name": "products",
        "dataSourceName": "products",
        "targetIndexName": "products",
            "fieldMappings": [
            {
                "sourceFieldName": "id",
                "targetFieldName": "id"
            },
            {
                "sourceFieldName": "name",
                "targetFieldName": "name"
            },
                {
                "sourceFieldName": "categoryId",
                "targetFieldName": "categoryId"
            }
    }

// index
{  
    "name": "products",  
    "fields": [  
      {  
        "name": "id",  
        "type": "Edm.String",  
        ...  
      },
      {  
        "name": "categoryId",  
        "type": "Edm.String",  
        ...
      },
      {  
        "name": "category_name",  
        "type": "Edm.String",  
         ...
      },
      {  
        "name": "name",  
        "type": "Edm.String",  
        ...
      }
...

Solution

Create a view that joins categories and products tables, potentially denormalizing all caregoryIds into a collection field of a product document. (You can use the same approach for other category data). Set up an indexer for that view.

Another approach is to create two separate search indexes for products and categories, and perform the joins in the search client code.