Cosmos DB中查询之间的日期 [英] Date Between Query in Cosmos DB

问题描述

我正在Cosmos DB中建立一个简单的事件存储，其中的文档结构如下:

I am in the building a simple event store in Cosmos DB that has documents that are structured something like this:

{
    "id": "e4c2bbd0-2885-4fb5-bcca-90436f79f155",
    "entityType": "contact",
    "history": [
        {
           "startDate": 1504656000,
           "endDate": 1504656000,
           "Name": "John"
        },
        {
           "startDate": 1504828800,
           "endDate": 1504828800,
           "Name": "Jon"
        }
    ]
}

这可能不是最有效的存储方式，但这就是我的开始.但是我希望能够在一段时间内从数据库中查询所有 contact 文档. startDate 和 endDate 表示记录有效的时间.当前的历史记录包含记录的整个历史记录，可能需要改进.

This might not bet the most efficient way to store it but this is what I am starting with. But I want to be able to query all contact documents out of the db for a certain period of time. The startDate and endDate represent the time the record was valid. The history currently contains the entire history of the record which probably could be improved.

我尝试过创建这样的查询:

I have tried creating a query like this:

SELECT c.entityType, c.id,history.Name, history.startDate  FROM c
JOIN history in c.history
where
    c.entityType = "contact" AND
    (history.StartDate <= 1504656001
        AND history.EndDate  >=  1504656001)

此查询应返回2017年9月7日联系人的状态，但它将返回历史记录的每一个.我玩过几种选择，但是我不确定自己缺少什么.

This query should return the state of the contact for 9/7/2017 but instead it is returning every one of the history. I have played with several options but I am not sure what I am missing.

我也尝试过设置索引(也许是问题吗?)所以我在这里包括了索引策略:

I have also tried setting the index (maybe that is the issue?) So I have included the indexing policy here:

{
"indexingMode": "consistent",
"automatic": true,
"includedPaths": [
    {
        "path": "/*",
        "indexes": [
            {
                "kind": "Range",
                "dataType": "String",
                "precision": -1
            },
            {
                "kind": "Range",
                "dataType": "Number",
                "precision": -1
            }
        ]
    }
],
"excludedPaths": []
}

我想念什么?索引正确吗?我的查询对两次查询之间的日期是否正确?

What am I missing? Is the index correct? Is my query correct for a date between query?

推荐答案

您有两个问题. Matias在评论中谈到了一个问题.

You have two issues. One is addressed by Matias in comment.

第二，您的条件是history.StartDate <= 1504656001 AND history.EndDate >= 1504656001.

播放范围，例如history.StartDate >= 1504656001 AND history.EndDate <= 1504656111.

这篇关于Cosmos DB中查询之间的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！