Creating Collection in Azure Search Service using Indexer

2
votes

I am using indexer to sync data from my SQL Database to Azure Search Service. I have a field in my SQL View, which contains XML data. The Column contains a list of string. The corresponding field in my Azure Search Service Index in a Collection(Edm.String). On checking some documentations, I found that Indexer does not change Xml(SQL) to Collection(Azure Search). Is there any workaround as to how I can get create the Collection from the Xml data?

p.s I am extracting the data from a View, so I can change the Xml to JSON if needed.

1
sqljsonazureazure-sql-databaseazure-cognitive-search

1 Answers

3
votes

UPDATE on October 17, 2016: Azure Search now automatically converts a string coming from a database to an Collection(Edm.String) field if the data represents a JSON string array: for example, ["blue", "white", "red"]

Old response: great timing, we just added a new "field mappings" feature that allows you to do this. This feature will be deployed sometime early next week. I will post a comment on this thread when this is rolled out in all datacenters.

To use it, you indeed need to use JSON. Make sure your source column contains a JSON array, for example ["hello" "world"]. Then, update your indexer definition to contain the new fieldMappings property: 

"fieldMappings" : [ { "sourceFieldName" : "YOUR_SOURCE_FIELD", "targetFieldName" : "YOUR_TARGET_FIELD", "mappingFunction" : { "name" : "jsonArrayToStringCollection" } } ]

NOTE: You'll need to use API version 2015-02-28-Preview to add fieldMappings.

HTH, Eugene