Can I index an array in a composite index in Azure Cosmos DB?

Question

I have a problem indexing an array in Azure Cosmos DB

I am trying to save this indexing policy via the portal

{
    "indexingMode": "consistent",
    "automatic": true,
    "includedPaths": [
        {
            "path": "/*"
        }
    ],
    "excludedPaths": [
        {
            "path": "/\"_etag\"/?"
        }
    ],
    "compositeIndexes": [
        [
            {
                "path": "/DeviceId",
                "order": "ascending"
            },
            {
                "path": "/TimeStamp",
                "order": "ascending"
            },
            {
                "path": "/Items/[]/Name/?",
                "order": "ascending"
            },
            {
                "path": "/Items/[]/DoubleValue/?",
                "order": "ascending"
            }
        ]
    ]
}

I get the error "Failed to update container DeviceEvents: Message: {"code":"BadRequest","message":"Message: {"Errors":["The indexing path '\/Items\/[]\/Name\/?' could not be accepted, failed near position '8'."

This seems to be the array [] syntax that is giving an error.

On a side note I am not sure what I am doing makes sense at all but I have a query that looks like this

SELECT SUM(de0["DoubleValue"])
FROM root JOIN de0 IN root["Items"]
WHERE root["ApplicationId"] = 57 AND root["DeviceId"] = 126 AND root["TimeStamp"] >= "2021-02-21T17:55:29.7389397Z" AND de0["Name"] = "Use Case"

Where ApplicationId is the partition key and the item saved looks like this

{
    "id": "59ab9323-26ca-436f-8d29-e1ddd826f025",
    "DeviceId": 3,
    "ApplicationId": 3,
    "RawData": "640F7A000A00E30142000000",
    "TimeStamp": "2021-02-20T18:36:52.833174Z",
    "Items": [
        {
            "Name": "Battery Status",
            "StringValue": "Full",
            "DoubleValue": null
        },
        {
            "Name": "Use Case",
            "StringValue": null,
            "DoubleValue": 12
        },
        {
            "Name": "Battery Voltage",
            "StringValue": null,
            "DoubleValue": 3.962
        },
        {
            "Name": "Rain Gauge Count",
            "StringValue": null,
            "DoubleValue": 10
        }
    ],
    "_rid": "CgdVAO7B0DNkAAAAAAAAAA==",
    "_self": "dbs/CgdVAA==/colls/CgdVAO7B0DM=/docs/CgdVAO7B0DNkAAAAAAAAAA==/",
    "_etag": "\"61008771-0000-0d00-0000-603156c50000\"",
    "_attachments": "attachments/",
    "_ts": 1613846213
}

I need to aggregate on some of these items in the array like say get MAX on temperature or something like this (using Use Case for test although it doesn't make sense). I reasoned that if all the data in the query is in a single composite index the database would be able to do the aggregation without reading the documents themselves. However I can't seem to add a composite index containing an array at all.

Steve Zhao Steve Zhao · Accepted Answer · 2021-04-30T10:01:04

Yes, composite index can't contain an array path. It should be a scalar value.

Unlike with included or excluded paths, you can't create a path with the /* wildcard. Every composite path has an implicit /? at the end of the path that you don't need to specify. Composite paths lead to a scalar value and this is the only value that is included in the composite index.

Reference:https://docs.microsoft.com/en-us/azure/cosmos-db/index-policy#composite-indexes

Can I index an array in a composite index in Azure Cosmos DB?

1 Answers