azure - 我可以在 Azure Cosmos DB 中的复合索引中为数组建立索引吗？

Question

我在 Azure Cosmos DB 中索引数组时遇到问题

我正在尝试通过门户保存此索引策略

{
    "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"
            }
        ]
    ]
}

我收到错误“无法更新容器 DeviceEvents:消息: {"code":"BadRequest","message":"消息: {"Errors":["索引路径 '\/Items\/[]\/Name\/?'无法接受，在位置“8”附近失败。”

这似乎是数组 [] 语法给出了错误。

顺便说一句，我不确定我所做的事情是否有意义，但我有一个如下所示的查询

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"

其中 ApplicationId 是分区键，保存的项目如下所示

{
    "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
}

我需要聚合数组中的一些项目，例如获取温度最大值或类似的内容(使用用例进行测试，尽管它没有意义)。我推断，如果查询中的所有数据都在单个复合索引中，则数据库将能够在不读取文档本身的情况下进行聚合。但是我似乎根本无法添加包含数组的复合索引。

Answer 1

是的，复合索引不能包含数组路径。它应该是一个标量值。

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

引用:https://learn.microsoft.com/en-us/azure/cosmos-db/index-policy#composite-indexes