Unable to map nested datasource field of cosmos db to a root index field of Azure indexer using REST APIs

Question

I have a mongo db collection users with the following data format

{
    "name": "abc",
    "email": "abc@xyz.com"
    "address": {
        "city": "Gurgaon",
        "state": "Haryana"
    }
}

Now I'm creating a datasource, an index, and an indexer for this collection using azure rest apis.

Datasource

def create_datasource():
  request_body = {
      "name": 'users-datasource',
      "description": "",
      "type": "cosmosdb",
      "credentials": {
          "connectionString": "<db conenction url>"
      },
      "container": {"name": "users"},
      "dataChangeDetectionPolicy": {
          "@odata.type": "#Microsoft.Azure.Search.HighWaterMarkChangeDetectionPolicy",
          "highWaterMarkColumnName": "_ts"
      }
  }
  resp = requests.post(url="<create-datasource-api-url>", data=json.dumps(request_body), 
    headers=headers)

Index for the above datasource

def create_index(config):

  request_body = {
      'name': "users-index",
      'fields': [
          {
              'name': 'name',
              'type': 'Edm.String'
          },
          {
              'name': 'email',
              'type': 'Edm.DateTimeOffset'
          },
          {
              'name': 'address',
              'type': 'Edm.String'
          },
          {
              'name': 'doc_id',
              'type': 'Edm.String',
              'key': True
          }
      ]
  }
  resp = requests.post(url="<azure-create-index-api-url>", data=json.dumps(request_body), 
    headers=config.headers)

Now the inxder for the above datasource and index

def create_interviews_indexer(config):
  request_body = {
    "name": "users-indexer",
    "dataSourceName": "users-datasource",
    "targetIndexName": users-index,
    "schedule": {"interval": "PT5M"},
    "fieldMappings": [
        {"sourceFieldName": "address.city", "targetFieldName": "address"},
    ]
  }
  resp = requests.post("create-indexer-pi-url", data=json.dumps(request_body), 
      headers=config.headers)

This creates the indexer without any exception, but when I check the retrieved data in azure portal for the users-indexer, the address field is null and is not getting any value from address.city field mapping that is provided while creating the indexer.

I have also tried the following code as a mapping but its also not working.

"fieldMappings": [
        {"sourceFieldName": "/address/city", "targetFieldName": "address"},
    ]

The azure documentation also does not say anything about this kind of mapping. So if anyone can help me on this, it will be very much appreciated.

Gaurav Mantri Gaurav Mantri · Accepted Answer · 2019-10-07T06:41:34

container element in data source definition allows you to specify a query that you can use to flatten your JSON document (Ref: https://docs.microsoft.com/en-us/rest/api/searchservice/create-data-source) so instead of doing column mapping in the indexer definition, you can write a query and get the output in desired format.

Your code for creating data source in that case would be:

def create_datasource():
  request_body = {
      "name": 'users-datasource',
      "description": "",
      "type": "cosmosdb",
      "credentials": {
          "connectionString": "<db conenction url>",
      },
      "container": {
        "name": "users",
        "query": "SELECT a.name, a.email, a.address.city as address FROM a",
      },
      "dataChangeDetectionPolicy": {
          "@odata.type": "#Microsoft.Azure.Search.HighWaterMarkChangeDetectionPolicy",
          "highWaterMarkColumnName": "_ts"
      }
  }
  resp = requests.post(url="<create-datasource-api-url>", data=json.dumps(request_body), 
    headers=headers)

Unable to map nested datasource field of cosmos db to a root index field of Azure indexer using REST APIs

3 Answers