CODEWITHSUNDEEP
azure-cognitive-search
lagrange-
lagrange-

Reputation: 215

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",  
        ...
      }
...

Upvotes: 1

Views: 434

Answers (1)

Eugene Shvets
Eugene Shvets

Reputation: 4671

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.

Upvotes: 1

Related Questions