Date Between Query in Cosmos DB

Question

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

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)

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?

This a better solution https://learn.microsoft.com/en-us/azure/cosmos-db/working-with-dates — Jonathan, Sep 06 '17 at 21:29
Is the history attribute an unbound list? Can it grow to a huge size? If so, you might consider moving the history to different objects with a different `entityType`. Please see [this answer](https://stackoverflow.com/a/27466029/5641598). — Matias Quaranta, Sep 07 '17 at 13:00
@MatiasQuaranta what are the limitations of the unbound list? Are you saying that I should just normalize the data better? I am not sure how big that history list could grow. I would say potentially 300-500 entities max. — Jonathan, Sep 07 '17 at 13:32
The problem with an unbound list is that your document size will grow, making your queries more expensive to run and you might run into some concurrency issues if two writers want to add history to the same document (unless you are explicitly handling concurrency). Partial updates are [not yet supported](https://feedback.azure.com/forums/263030-azure-cosmos-db/suggestions/6693091-be-able-to-do-partial-updates-on-document) so the issue is that you'd have to update the entire document everytime you add 1 history entry. — Matias Quaranta, Sep 07 '17 at 14:12

score 3 · Answer 1 · edited Aug 05 '19 at 07:27

3

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

Second, your condition is history.StartDate <= 1504656001 AND history.EndDate >= 1504656001.

play with the range for e.g. history.StartDate >= 1504656001 AND history.EndDate <= 1504656111.

edited Aug 05 '19 at 07:27

Jamie Rees

7,973
2
45
83

answered Sep 07 '17 at 21:54

Rafat Sarosh

989
7
16

Hi my field value is "TimeStamp": "2020-10-16T21:18:29" and i want a query which gives me records between last 5 minutes support run query right now at 21:20 then give me result between 21:15:01 to 21:19:59 can you help with select ? – Neo Oct 16 '20 at 15:59

Date Between Query in Cosmos DB

1 Answers1

Linked