Azure's CosmosDB: Query explorer get documents based on comparing timestamps as strings

Question

I have a few JSON documents on CosmosDB where one of the children nodes is a string representing the date and time in the following format "yyyy-mm-dd HH:MM:SS". I am wondering if it's possible with the query explorer to get all documents where the datetime is in a specific range. I tried to do it by string comparison but I don't know how to do that. I tried the CompareTo function but I would get an incorrect syntax error.

This is what each of my documents look like:

    { "Blocks": [...]
    }

Since the query explorer merges all documents together, this is what it gives me from executing this query "Select * from c", an outer list containing multiple dictionaries:

    [ 
     {"Blocks": [...]},
     {"Blocks": [...]},
     {"Blocks": [...]},
     ...
    ]

Blocks' value is a list which looks like the following:

    [
     {"StartDateTime" : "2017-06-12 16:00:00", ...},
     {"StartDateTime" : "2017-06-11 11:00:00", ...},
     {"StartDateTime" : "2017-06-12 13:00:00", ...},
     ...
    ]

Where each element in the inner list is a dictionary where one of the keys is "StartDateTime" with value a string in the format specified above.

My goal is to be able to query all items in that inner list where the value of StartDateTime >= DateTimeStringLower and StartDateTime < DateTimeStringUpper. Ideally, the returned file should look something like this:

    {"Blocks":
     [
      {"StartDateTime" : "2017-06-12 16:00:00", ...},
      {"StartDateTime" : "2017-06-11 11:00:00", ...},
      {"StartDateTime" : "2017-06-12 13:00:00", ...},
     ...
     ]
    }

But I can work around it if it's not exactly like that.

I have tried this query Select * from d in c.Blocks Where d.StartDateTime.CompareTo("2017-06-12") >= 0 but it gives me a syntax error on '('. Does this command even go over all Blocks?

What is the correct way to approach this? Thanks.

PS: Just a side question. Once I figure out the correct query, can I use it in python? I have this line of code docs = client.ReadDocuments(coll_link). Is there a way to get parts of the docs with the query in python instead of all documents? It would cost less Request Units in Azure.

Mikhail Shilkov Mikhail Shilkov · Accepted Answer · 2017-06-13T21:20:01

If I understand your requirement correctly, the following query should do the job:

SELECT VALUE Block 
  FROM c 
  JOIN Block IN c.Blocks
 WHERE Block.StartDateTime >= '2017-06-12' 
   AND Block.StartDateTime < '2017-06-13'

It will produce a result which looks like

[
  { "StartDateTime": "2017-06-12 16:00:00", ... },
  { "StartDateTime": "2017-06-12 13:00:00", ... },
  ...
]

For comparisons to work you need to enable Range index on your collection, at least for the compared property. See examples of index configurtion here.

Azure's CosmosDB: Query explorer get documents based on comparing timestamps as strings

1 Answers