Manage indexing policies in Azure Cosmos DB
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.
Sign in to the Azure portal.
Create a new Azure Cosmos DB account or select an existing account.
Open the Data Explorer pane and select the container that you want to work on.
Click on Scale & Settings.
Modify the indexing policy JSON document (see examples below)
Click Save when you are done.
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:
Feedback
Submit and view feedback for