Indexing DocumentDB with Azure Seach

This post will show how to configure DocumentDB as a data source to be indexed using Azure Search.

Background

I wrote a post Indexing Azure SQL Database with Azure Search that shows how to index an Azure SQL Database using Azure Search.  Once I started playing around with it and reading through the documentation, I noticed that the documentation for the Create Data Source API mentioned DocumentDB as a data source.  I thought I would write a quick post showing how to do this since I haven’t seen any documentation on it yet.

Create the DocumentDB Database

Go to the new Azure portal (https://portal.azure.com) and create a new DocumentDB service.

image

Provide the name, resource group, and location.

image

Click OK, provisioning takes around 10 minutes.  Once the service is created, create a database “SampleDatabase”.

image

Once created, scroll down and click on the newly created database.

image

Click on the Add Collection button and name the new collection “families”.

image

Click on the newly added collection.

image

Click “Create Document”.

image

You will be able to add JSON into the editor.  We create two documents:

FlintstoneFamily

  1. {
  2.     "id": "FlintstoneFamily",
  3.     "lastName": "Flintstone",
  4.     "parents": [
  5.        { "firstName": "Fred" },
  6.        { "firstName": "Wilma"}
  7.     ],
  8.     "children": [
  9.         {
  10.             "firstName": "Pebbles",
  11.                         "gender": "female"
  12.         }
  13.     ],
  14.     "pets": [{ "firstName": "Dino" }]
  15. }

And:

RubbleFamily

  1. {
  2.     "id": "RubbleFamily",
  3.     "lastName": "Rubble",
  4.     "parents": [
  5.        { "firstName": "Barney" },
  6.        { "firstName": "Betty" }
  7.     ],
  8.     "children": [
  9.         {
  10.             "firstName": "Bamm Bamm",
  11.             "gender": "male"
  12.         }
  13.     ],
  14.     "pets": [{ "firstName": "Hoppy" }]
  15. }

A quick query of the documents using the SQL query:

DocumentDB Query

  1. SELECT c.firstName from Families f join c IN f.children

image

We see that we have results.

image

Now let’s configure Azure Search. 

Create the Search Service

Create a new Azure Search service.

image

Once created, you will need the key for the service in order to issue REST calls.

image

 

Create the Azure Search Data Source

Just as we did in the previous post, we will use the REST API to create a data source using the Create Data Source API.  You need to provide the name of your service, the API Key for your Search service, the name of your DocumentDB service, and the account key for your DocumentDB service. 

Create Data Source

  1. POST https://<Your Search Service>.search.windows.net/datasources?api-version=2015-02-28 HTTP/1.1
  2. Content-Type: application/json
  3. api-key: <Your API Key>
  4.  
  5. {
  6.     "name" : "mydocdbdatasource",
  7.     "type" : "documentdb",
  8.     "credentials" :
  9.     {
  10.         "connectionString": "AccountEndpoint=https://<Your DocumentDB Service>.documents.azure.com;Database=SampleDatabase;AccountKey=<Your Account Key>"
  11.     },
  12.     "container" : { "name" : "families" }
  13. }

The connection string uses the name of your DocumentDB service as well as an AccountKey.  The key can be obtained in the portal.

image

Create an Azure Search Index

Now that we have the datasource named “mydocdbdatasource”, we create an index.  You can use the portal to do this, or we can just use the API again.  Azure Search does not currently support complex JSON types, it only supports simple types and a collection of strings.  Note that the “parents”, “children”, and “pets” types here are represented as string collections.

Create Index

  1. POST https://<Your Search Service>.search.windows.net/indexes/?api-version=2015-02-28 HTTP/1.1
  2. Content-Type: application/json
  3. api-key: <Your API Key>
  4.  
  5. {
  6.     "name":"familyindex",
  7.     "fields":[
  8.         {"name":"id","type":"Edm.String","searchable":false,"filterable":false,"retrievable":true,"sortable":false,"facetable":false,"key":true},
  9.         {"name":"lastName","type":"Edm.String","searchable":true,"filterable":false,"retrievable":true,"sortable":true,"facetable":true,"key":false},        
  10.         {"name":"parents","type":"Collection(Edm.String)","searchable":true,"filterable":true,"retrievable":true,"sortable":false,"facetable":false,"key":false},
  11.         {"name":"children","type":"Collection(Edm.String)","searchable":true,"filterable":true,"retrievable":true,"sortable":false,"facetable":false,"key":false},
  12.         {"name":"pets","type":"Collection(Edm.String)","searchable":true,"filterable":true,"retrievable":true,"sortable":false,"facetable":false,"key":false}
  13.     ]
  14. }

Create the Indexer

The last step is to create an indexer.  This is the part that connects the index to the data source, and allows us to run the indexer manually or on a scheduled basis.

Create Indexer

  1. POST https://<Your Search Service>.search.windows.net/indexers?api-version=2015-02-28 HTTP/1.1
  2. Content-Type: application/json
  3. api-key: <Your API Key>
  4.  
  5. {
  6.        "name" : "familyindexer",
  7.        "dataSourceName" : "docdbsource",
  8.        "targetIndexName" : "familyindex"
  9. }

Once the indexer is created, you can check its status. 

Get Indexer Status

  1. GET https://<Your Search Service>.search.windows.net/indexers/familyindexer/status?api-version=2015-02-28 HTTP/1.1
  2. Content-Type: application/json
  3. api-key: <Your API Key>
  4.  
  5. {
  6.        "name" : "familyindexer",
  7.        "dataSourceName" : "docdbsource",
  8.        "targetIndexName" : "familyindex"
  9. }

The result shows how many items were processed and the last time the indexer executed.  As an example, here is the result returned from the status method for my service.  Note there are 2 items processed, one for each document.

Status Response

  1. {
  2.     "@odata.context":"https://kirkesearch.search.windows.net/$metadata#Microsoft.Azure.Search.V2015_02_28.IndexerExecutionInfo",
  3.     "status":"running",
  4.     "lastResult":
  5.     {
  6.         "status":"success",
  7.         "errorMessage":null,
  8.         "startTime":"2015-03-09T18:13:06.928Z",
  9.         "endTime":"2015-03-09T18:13:07.552Z",
  10.         "errors":[],
  11.         "itemsProcessed":2,
  12.         "itemsFailed":0,
  13.         "initialTrackingState":null,
  14.         "finalTrackingState":null
  15.     },
  16.     "executionHistory":
  17.         [
  18.             {
  19.                 "status":"success",
  20.                 "errorMessage":null,
  21.                 "startTime":"2015-03-09T18:13:06.928Z",
  22.                 "endTime":"2015-03-09T18:13:07.552Z",
  23.                 "errors":[],
  24.                 "itemsProcessed":2,
  25.                 "itemsFailed":0,
  26.                 "initialTrackingState":null,
  27.                 "finalTrackingState":null
  28.             }
  29.         ]
  30. }

Testing It Out

Now that we have the index, data source, and indexer, we can test the application.  While we could use an SDK to write an application, it makes sense to show how easy it is to query using the REST API and a simple HTTP GET request.

GET Search Results

  1. GET https://<Your Search Service>.search.windows.net/indexes/familyindex/docs?api-version=2015-02-28&search=Flintstone HTTP/1.1
  2. Content-Type: application/json
  3. api-key: <Your API Key>

The response includes our data.  Our application could then parse the results to present the results to the end user.  Since this is just a an HTTP GET with a request body, we can easily use this in any platform!

Search Response

  1. {
  2.     "id":"FlintstoneFamily",
  3.     "isRegistered":false,
  4.     "lastName":"Flintstone",
  5.     "parents":
  6.         [
  7.             "{\r\n  \"firstName\": \"Fred\"\r\n}",
  8.             "{\r\n  \"firstName\": \"Wilma\"\r\n}"
  9.         ],
  10.     "children":
  11.         [
  12.             "{\r\n  \"firstName\": \"Pebbles\",\r\n  \"gender\": \"female\"\r\n}"
  13.         ],
  14.     "pets":
  15.         [
  16.             "{\r\n  \"firstName\": \"Dino\"\r\n}"
  17.         ]
  18. }

We can see that the properties are returned just as we expect, we can access the “id” property to retrieve the value “FlintstoneFamily”, the “lastName” property to obtain the value “Flintstone”. 

Flattening Data

Something to call out… notice lines 5, 10, and 14 in the previous code snippet where the complex types are represented as string arrays instead of JSON objects.  This happens because we specified in the index schema that these values were of type “Collection(Edm.String)” due to the fact that Azure Search doesn’t yet understand complex JSON types.  If you have a complex type and need to search against its contents, there is a query property that can be applied to the data source in order to flatten the data and enable Azure Search to index it, as shown in line 16 below.  We can use an HTTP PUT to update the data source with our query:

Code Snippet

  1. PUT https://<Your Search Service>.search.windows.net/datasources/docdbsource?api-version=2015-02-28 HTTP/1.1
  2. Content-Type: application/json
  3. api-key: <Your API Key>
  4.  
  5.  
  6. {
  7.     "name" : "docdbsource",
  8.     "type" : "documentdb",
  9.     "credentials" :
  10.     {
  11.         "connectionString": "AccountEndpoint=https://<Your DocDB Service>.documents.azure.com;AccountKey=<Your Account Key>;Database=SampleDatabase"
  12.     },
  13.     "container" :
  14.     {
  15.         "name" : "families",
  16.         "query" : "SELECT f.id, c.firstName, f.lastName, c.gender FROM families f join c IN f.children"
  17.     }
  18. }

If we flatten the data in this manner (changing the shape of data returned) you need to delete the index and create it again using the four string fields “id”, “firstName”, “lastName”, and “gender”.  Notice I said delete the index.. updates to the index currently only allow you to add new fields, not modify existing fields. 

Flattened Schema

  1. POST https://<Your Search Service>.search.windows.net/indexes/?api-version=2015-02-28 HTTP/1.1
  2. Content-Type: application/json
  3. api-key: <Your API Key>
  4.  
  5. {
  6.     "name":"familyindex",
  7.     "fields":[
  8.         {"name":"id","type":"Edm.String","searchable":false,"filterable":false,"retrievable":true,"sortable":false,"facetable":false,"key":true},
  9.         {"name":"lastName","type":"Edm.String","searchable":true,"filterable":false,"retrievable":true,"sortable":true,"facetable":true,"key":false},        
  10.         {"name":"firstName","type":"Edm.String","searchable":true,"filterable":false,"retrievable":true,"sortable":true,"facetable":true,"key":false},
  11.         {"name":"gender","type":"Edm.String","searchable":true,"filterable":false,"retrievable":true,"sortable":true,"facetable":true,"key":false}
  12.     ]
  13. }

Here we show the results in Fiddler when we execute a search query and can see the individual properties of the object returned, and now “gender” and “firstName” are now part of the index.

Search Results

  1. {
  2.     "@odata.context":"https://kirkesearch.search.windows.net/indexes('familyindex')/$metadata#docs(id,lastName,firstName,gender)",
  3.     "value":
  4.         [
  5.             {
  6.                 "@search.score":0.38537163,
  7.                 "id":"FlintstoneFamily",
  8.                 "lastName":"Flintstone",
  9.                 "firstName":"Pebbles",
  10.                 "gender":"female"
  11.             }
  12.         ]
  13. }

Let’s see it in Fiddler with the JSON inspector so you can see the HTTP GET request and the JSON inspector used in the response.

image

A word of caution here… there is only one child per household in my data set. If I had instead used “parents” where there are multiple results per household, I would not have received data for the “firstName” property because the indexer was unable to differentiate between results based on the key in the index. If this is the case for you, you will need to structure your flattening query differently to create a unique key.

To learn more about DocumentDB queries, see the article Query DocumentDB

The Azure Search team uses UserVoice to track suggestions for service improvements, and the ability to model complex types in the index is already there.  If you want to see this feature, then add some votes! 

Summary

I have been playing around with DocumentDB lately and I think it is a fantastic service (more on that later).  Coupling it with Azure Search just feels natural.  Connecting the two is easy, and Azure Search does a fantastic job of indexing top-level content.  If your documents are flat, then you will find this is a very simple win for your application.  For documents with nested complexity, you will need to evaluate how you can flatten the structure. 

For More Information

Create Data Source API

Indexing Azure SQL Database with Azure Search

Azure Search Service REST API

Improve Azure Search – UserVoice

Query DocumentDB