Edit

Manage indexing policies in Azure Cosmos DB

  • Article
  • 9 minutes to read

APPLIES TO: NoSQL

In Azure Cosmos DB, data is indexed following indexing policies that are defined for each container. The default indexing policy for newly created containers enforces range indexes for any string or number. This policy can be overridden with your own custom indexing policy.

Note

The method of updating indexing policies described in this article only applies to Azure Cosmos DB's API for NoSQL. Learn about indexing in Azure Cosmos DB's API for MongoDB and Secondary indexing in Azure Cosmos DB for Apache Cassandra.

Indexing policy examples

Here are some examples of indexing policies shown in their JSON format, which is how they are exposed on the Azure portal. The same parameters can be set through the Azure CLI or any SDK.

Opt-out policy to selectively exclude some property paths

    {
        "indexingMode": "consistent",
        "includedPaths": [
            {
                "path": "/*"
            }
        ],
        "excludedPaths": [
            {
                "path": "/path/to/single/excluded/property/?"
            },
            {
                "path": "/path/to/root/of/multiple/excluded/properties/*"
            }
        ]
    }

Opt-in policy to selectively include some property paths

    {
        "indexingMode": "consistent",
        "includedPaths": [
            {
                "path": "/path/to/included/property/?"
            },
            {
                "path": "/path/to/root/of/multiple/included/properties/*"
            }
        ],
        "excludedPaths": [
            {
                "path": "/*"
            }
        ]
    }

Note

It is generally recommended to use an opt-out indexing policy to let Azure Cosmos DB proactively index any new property that may be added to your data model.

Using a spatial index on a specific property path only

{
    "indexingMode": "consistent",
    "automatic": true,
    "includedPaths": [
        {
            "path": "/*"
        }
    ],
    "excludedPaths": [
        {
            "path": "/_etag/?"
        }
    ],
    "spatialIndexes": [
        {
            "path": "/path/to/geojson/property/?",
            "types": [
                "Point",
                "Polygon",
                "MultiPolygon",
                "LineString"
            ]
        }
    ]
}

Composite indexing policy examples

In addition to including or excluding paths for individual properties, you can also specify a composite index. If you would like to perform a query that has an ORDER BY clause for multiple properties, a composite index on those properties is required. Additionally, composite indexes will have a performance benefit for queries that have multiple filters or both a filter and an ORDER BY clause.

Note

Composite paths have an implicit /? since only the scalar value at that path is indexed. The /* wildcard is not supported in composite paths. You shouldn't specify /? or /* in a composite path.

Composite index defined for (name asc, age desc):

    {  
        "automatic":true,
        "indexingMode":"Consistent",
        "includedPaths":[  
            {  
                "path":"/*"
            }
        ],
        "excludedPaths":[],
        "compositeIndexes":[  
            [  
                {  
                    "path":"/name",
                    "order":"ascending"
                },
                {  
                    "path":"/age",
                    "order":"descending"
                }
            ]
        ]
    }

The above composite index on name and age is required for Query #1 and Query #2:

Query #1:

    SELECT *
    FROM c
    ORDER BY c.name ASC, c.age DESC

Query #2:

    SELECT *
    FROM c
    ORDER BY c.name DESC, c.age ASC

This composite index will benefit Query #3 and Query #4 and optimize the filters:

Query #3:

SELECT *
FROM c
WHERE c.name = "Tim"
ORDER BY c.name DESC, c.age ASC

Query #4:

SELECT *
FROM c
WHERE c.name = "Tim" AND c.age > 18

Composite index defined for (name ASC, age ASC) and (name ASC, age DESC):

You can define multiple different composite indexes within the same indexing policy.

    {  
        "automatic":true,
        "indexingMode":"Consistent",
        "includedPaths":[  
            {  
                "path":"/*"
            }
        ],
        "excludedPaths":[],
        "compositeIndexes":[  
            [  
                {  
                    "path":"/name",
                    "order":"ascending"
                },
                {  
                    "path":"/age",
                    "order":"ascending"
                }
            ],
            [  
                {  
                    "path":"/name",
                    "order":"ascending"
                },
                {  
                    "path":"/age",
                    "order":"descending"
                }
            ]
        ]
    }

Composite index defined for (name ASC, age ASC):

It is optional to specify the order. If not specified, the order is ascending.

{  
        "automatic":true,
        "indexingMode":"Consistent",
        "includedPaths":[  
            {  
                "path":"/*"
            }
        ],
        "excludedPaths":[],
        "compositeIndexes":[  
            [  
                {  
                    "path":"/name",
                },
                {  
                    "path":"/age",
                }
            ]
        ]
}

Excluding all property paths but keeping indexing active

This policy can be used in situations where the Time-to-Live (TTL) feature is active but no additional indexes are necessary (to use Azure Cosmos DB as a pure key-value store).

    {
        "indexingMode": "consistent",
        "includedPaths": [],
        "excludedPaths": [{
            "path": "/*"
        }]
    }

No indexing

This policy will turn off indexing. If indexingMode is set to none, you cannot set a TTL on the container.

    {
        "indexingMode": "none"
    }

Updating indexing policy

In Azure Cosmos DB, the indexing policy can be updated using any of the below methods:

  • from the Azure portal
  • using the Azure CLI
  • using PowerShell
  • using one of the SDKs

An indexing policy update triggers an index transformation. The progress of this transformation can also be tracked from the SDKs.

Note

When updating indexing policy, writes to Azure Cosmos DB will be uninterrupted. Learn more about indexing transformations

Use the Azure portal

Azure Cosmos DB containers store their indexing policy as a JSON document that the Azure portal lets you directly edit.

  1. Sign in to the Azure portal.

  2. Create a new Azure Cosmos DB account or select an existing account.

  3. Open the Data Explorer pane and select the container that you want to work on.

  4. Click on Scale & Settings.

  5. Modify the indexing policy JSON document (see examples below)

  6. Click Save when you are done.

Manage Indexing using Azure portal

Use the Azure CLI

To create a container with a custom indexing policy see, Create a container with a custom index policy using CLI

Use PowerShell

To create a container with a custom indexing policy see, Create a container with a custom index policy using PowerShell

Use the .NET SDK

The ContainerProperties object from the .NET SDK v3 (see this Quickstart regarding its usage) exposes an IndexingPolicy property that lets you change the IndexingMode and add or remove IncludedPaths and ExcludedPaths.

// Retrieve the container's details
ContainerResponse containerResponse = await client.GetContainer("database", "container").ReadContainerAsync();
// Set the indexing mode to consistent
containerResponse.Resource.IndexingPolicy.IndexingMode = IndexingMode.Consistent;
// Add an included path
containerResponse.Resource.IndexingPolicy.IncludedPaths.Add(new IncludedPath { Path = "/*" });
// Add an excluded path
containerResponse.Resource.IndexingPolicy.ExcludedPaths.Add(new ExcludedPath { Path = "/name/*" });
// Add a spatial index
SpatialPath spatialPath = new SpatialPath
{
    Path = "/locations/*"
};
spatialPath.SpatialTypes.Add(SpatialType.Point);
containerResponse.Resource.IndexingPolicy.SpatialIndexes.Add(spatialPath);
// Add a composite index
containerResponse.Resource.IndexingPolicy.CompositeIndexes.Add(new Collection<CompositePath> { new CompositePath() { Path = "/name", Order = CompositePathSortOrder.Ascending }, new CompositePath() { Path = "/age", Order = CompositePathSortOrder.Descending } });
// Update container with changes
await client.GetContainer("database", "container").ReplaceContainerAsync(containerResponse.Resource);

To track the index transformation progress, pass a RequestOptions object that sets the PopulateQuotaInfo property to true, then retrieve the value from the x-ms-documentdb-collection-index-transformation-progress response header.

// retrieve the container's details
ContainerResponse containerResponse = await client.GetContainer("database", "container").ReadContainerAsync(new ContainerRequestOptions { PopulateQuotaInfo = true });
// retrieve the index transformation progress from the result
long indexTransformationProgress = long.Parse(containerResponse.Headers["x-ms-documentdb-collection-index-transformation-progress"]);

The SDK V3's fluent API lets you write this definition in a concise and efficient way when defining a custom indexing policy while creating a new container:

await client.GetDatabase("database").DefineContainer(name: "container", partitionKeyPath: "/myPartitionKey")
    .WithIndexingPolicy()
        .WithIncludedPaths()
            .Path("/*")
        .Attach()
        .WithExcludedPaths()
            .Path("/name/*")
        .Attach()
        .WithSpatialIndex()
            .Path("/locations/*", SpatialType.Point)
        .Attach()
        .WithCompositeIndex()
            .Path("/name", CompositePathSortOrder.Ascending)
            .Path("/age", CompositePathSortOrder.Descending)
        .Attach()
    .Attach()
    .CreateIfNotExistsAsync();

Use the Java SDK

The DocumentCollection object from the Java SDK (see this Quickstart regarding its usage) exposes getIndexingPolicy() and setIndexingPolicy() methods. The IndexingPolicy object they manipulate lets you change the indexing mode and add or remove included and excluded paths.

// Retrieve the container's details
Observable<ResourceResponse<DocumentCollection>> containerResponse = client.readCollection(String.format("/dbs/%s/colls/%s", "database", "container"), null);
containerResponse.subscribe(result -> {
DocumentCollection container = result.getResource();
IndexingPolicy indexingPolicy = container.getIndexingPolicy();

// Set the indexing mode to consistent
indexingPolicy.setIndexingMode(IndexingMode.Consistent);

// Add an included path

Collection<IncludedPath> includedPaths = new ArrayList<>();
IncludedPath includedPath = new IncludedPath();
includedPath.setPath("/*");
includedPaths.add(includedPath);
indexingPolicy.setIncludedPaths(includedPaths);

// Add an excluded path

Collection<ExcludedPath> excludedPaths = new ArrayList<>();
ExcludedPath excludedPath = new ExcludedPath();
excludedPath.setPath("/name/*");
excludedPaths.add(excludedPath);
indexingPolicy.setExcludedPaths(excludedPaths);

// Add a spatial index

Collection<SpatialSpec> spatialIndexes = new ArrayList<SpatialSpec>();
Collection<SpatialType> collectionOfSpatialTypes = new ArrayList<SpatialType>();

SpatialSpec spec = new SpatialSpec();
spec.setPath("/locations/*");
collectionOfSpatialTypes.add(SpatialType.Point);
spec.setSpatialTypes(collectionOfSpatialTypes);
spatialIndexes.add(spec);

indexingPolicy.setSpatialIndexes(spatialIndexes);

// Add a composite index

Collection<ArrayList<CompositePath>> compositeIndexes = new ArrayList<>();
ArrayList<CompositePath> compositePaths = new ArrayList<>();

CompositePath nameCompositePath = new CompositePath();
nameCompositePath.setPath("/name");
nameCompositePath.setOrder(CompositePathSortOrder.Ascending);

CompositePath ageCompositePath = new CompositePath();
ageCompositePath.setPath("/age");
ageCompositePath.setOrder(CompositePathSortOrder.Descending);

compositePaths.add(ageCompositePath);
compositePaths.add(nameCompositePath);

compositeIndexes.add(compositePaths);
indexingPolicy.setCompositeIndexes(compositeIndexes);

// Update the container with changes

 client.replaceCollection(container, null);
});

To track the index transformation progress on a container, pass a RequestOptions object that requests the quota info to be populated, then retrieve the value from the x-ms-documentdb-collection-index-transformation-progress response header.

// set the RequestOptions object
RequestOptions requestOptions = new RequestOptions();
requestOptions.setPopulateQuotaInfo(true);
// retrieve the container's details
Observable<ResourceResponse<DocumentCollection>> containerResponse = client.readCollection(String.format("/dbs/%s/colls/%s", "database", "container"), requestOptions);
containerResponse.subscribe(result -> {
    // retrieve the index transformation progress from the response headers
    String indexTransformationProgress = result.getResponseHeaders().get("x-ms-documentdb-collection-index-transformation-progress");
});

Use the Node.js SDK

The ContainerDefinition interface from Node.js SDK (see this Quickstart regarding its usage) exposes an indexingPolicy property that lets you change the indexingMode and add or remove includedPaths and excludedPaths.

Retrieve the container's details

const containerResponse = await client.database('database').container('container').read();

Set the indexing mode to consistent

containerResponse.body.indexingPolicy.indexingMode = "consistent";

Add included path including a spatial index

containerResponse.body.indexingPolicy.includedPaths.push({
    includedPaths: [
      {
        path: "/age/*",
        indexes: [
          {
            kind: cosmos.DocumentBase.IndexKind.Range,
            dataType: cosmos.DocumentBase.DataType.String
          },
          {
            kind: cosmos.DocumentBase.IndexKind.Range,
            dataType: cosmos.DocumentBase.DataType.Number
          }
        ]
      },
      {
        path: "/locations/*",
        indexes: [
          {
            kind: cosmos.DocumentBase.IndexKind.Spatial,
            dataType: cosmos.DocumentBase.DataType.Point
          }
        ]
      }
    ]
  });

Add excluded path

containerResponse.body.indexingPolicy.excludedPaths.push({ path: '/name/*' });

Update the container with changes

const replaceResponse = await client.database('database').container('container').replace(containerResponse.body);

To track the index transformation progress on a container, pass a RequestOptions object that sets the populateQuotaInfo property to true, then retrieve the value from the x-ms-documentdb-collection-index-transformation-progress response header.

// retrieve the container's details
const containerResponse = await client.database('database').container('container').read({
    populateQuotaInfo: true
});
// retrieve the index transformation progress from the response headers
const indexTransformationProgress = replaceResponse.headers['x-ms-documentdb-collection-index-transformation-progress'];

Use the Python SDK

When using the Python SDK V3 (see this Quickstart regarding its usage), the container configuration is managed as a dictionary. From this dictionary, it is possible to access the indexing policy and all its attributes.

Retrieve the container's details

containerPath = 'dbs/database/colls/collection'
container = client.ReadContainer(containerPath)

Set the indexing mode to consistent

container['indexingPolicy']['indexingMode'] = 'consistent'

Define an indexing policy with an included path and a spatial index

container["indexingPolicy"] = {

    "indexingMode":"consistent",
    "spatialIndexes":[
                {"path":"/location/*","types":["Point"]}
             ],
    "includedPaths":[{"path":"/age/*","indexes":[]}],
    "excludedPaths":[{"path":"/*"}]
}

Define an indexing policy with an excluded path

container["indexingPolicy"] = {
    "indexingMode":"consistent",
    "includedPaths":[{"path":"/*","indexes":[]}],
    "excludedPaths":[{"path":"/name/*"}]
}

Add a composite index

container['indexingPolicy']['compositeIndexes'] = [
                [
                    {
                        "path": "/name",
                        "order": "ascending"
                    },
                    {
                        "path": "/age",
                        "order": "descending"
                    }
                ]
                ]

Update the container with changes

response = client.ReplaceContainer(containerPath, container)

Next steps

Read more about the indexing in the following articles: