Correct way to query a Cosmos DB Table

Question

I am trying to use Cosmos DB Tables. What I am noticing is that if I query on Timestamp property, no data is returned.

Here's the query I am using:

Timestamp ge datetime'2010-01-01T00:00:00'

I believe my query is correct because the same query runs perfectly fine against a table in my Storage Account.

If I query on any other attribute, the query runs perfectly fine.

I tried running this query in both Cerebrata Cerulean and in Microsoft Storage Explorer and I am getting no results in both places.

However when I run the same query in Azure Portal Data Explorer, data is returned. I opened developer tools in Azure Portal and noticed that the Portal is not making OData query. Instead it is making SQL API query. For example, in the above case the query that's being sent is:

Select * from c where c._ts > [epoch value indicating time]

Similarly if I query on an attribute using the tools above:

AttributeName eq 'Some Attribute Value'

Same query is being sent in Azure Portal as

SELECT * FROM c WHERE  c.AttributeName["$v"] = 'Some Attribute Value'

All the documentation states that I should be able to write OData queries and they should work but I am not finding it to be correct.

So what's the correct way of querying Cosmos DB Tables?

UPDATE

Seems this is not a problem with just Timestamp property but all Edm.DateTime kind of properties.

UPDATE #2

So I opened up my Cosmos DB Table account as SQL API account to see how the data is actually stored under the hood.

First thing I observed is that Timestamp property is not getting stored at all. Value of Timestamp (in Storage Table Entity) is actually getting stored as _ts system property and that too as Epoch seconds.

Next thing I noticed is that all Date/Time kind of properties are actually getting converted into a 20 character long strings and are stored something like the following:

"SourceTimestamp": {
    "$t": 9,
    "$v": "00637219463290953744"
},

I am wondering if that has something to do with not being able to issue ODATA queries directly.

BTW, I forgot to mention that I am using Azure Storage Node SDK to access my Cosmos Table account (as this is what Microsoft is recommending considering there's no Node SDK specifically for Table API).

Have you looked at this Gaurav? docs.microsoft.com/en-us/azure/cosmos-db/working-with-dates I'm wondering if the problem is the format of dates you have. — Mark Brown
@MarkBrown..Thanks for replying and sharing that link, I have already gone through that. 2 things: 1) The linked article is specific for SQL API I believe and 2) I think my query is right because the same query runs perfectly fine in Data Explorer in Azure Portal. Anyways, I have updated my question with some more information. Hopefully this will help you find more information. — Gaurav Mantri
Thanks for the update. Will investigate and get back to you. — Mark Brown

Mark Brown Mark Brown · Accepted Answer · 2020-04-29T17:23:03

Thanks for your patience while I looked into this.

The root cause for this behavior is while Storage table stores with time granularity of ticks, Cosmos DB's _ts is at a second level of granularity. This isn't OData related. We actually block queries for timestamp properties because it was confusing customers and overall Timestamp based queries are not recommended for Storage Tables.

The workaround for this is to add your own custom datetime or long data type property and set the value yourself from the client.

We will address this in a future update but this work is not currently scheduled.

Thanks.

Correct way to query a Cosmos DB Table

1 Answers