Often times I have been asked by users of Cloud Storage Studio regarding how to write query syntax especially filter criteria in Cloud Storage Studio against table storage to filter the data. This blog post is a compilation to responses to those questions.
Basic syntax for a query against table storage is something like this:
http://<storageaccount>.table.core.windows.net/<tablename>?$filter=<URLEncodedFilterCriteria>
You can read more about the operators supported in filter by visiting the sites below so I will not go in details about them:
Querying Tables and Entities: http://msdn.microsoft.com/en-us/library/dd894031.aspx
Query Operators (Table Service Support): http://msdn.microsoft.com/en-us/library/dd135725.aspx
What I want to talk about in this blog post instead is how to construct the “$filter” criteria based on the Azure Table data types. Let’s assume that you’ve an entity in a table called MyTable which has the following structure as specified below:
| Attribute Name | Data Type | Value |
| PartitionKey | String | MyPK |
| RowKey | String | MyRK |
| Timestamp | DateTime | 2010-08-13 10:32:38.0453121 |
| SomeDoubleValue | Double | 123.45 |
| SomeLongValue | Int64 | 12345678901234567890 |
| SomeIntValue | Int32 | 123456 |
| SomeBooleanValue | Boolean | true |
| SomeGuidValue | Guid | 6bc44494-d2b7-4738-8f87-acd692226a5c |
Let’s assume you want to write a filter expression for each data type. Here is how you would write the filter expression:
| Data Type | Filter Expression |
| String | PartitionKey eq ‘MyPK’ |
| DateTime | Timestamp eq datetime‘2010-08-13T10:32:38.0453121Z’ |
| Double | SomeDoubleValue eq 123.45 |
| Int64 | SomeLongValue eq 12345678901234567890L |
| Int32 | SomeIntValue eq 123456 |
| Boolean | SomeBooleanValue eq true |
| Guid | Guid’6bc44494-d2b7-4738-8f87-acd692226a5c’ |
In the table above, please pay special attention to DateTime, Int64 & Guid data types.
If you’re writing queries by yourself and using them via REST calls, don’t forget to URL encode the filter expression. So your querying by Datetime field would look something like: $filter=Timestamp+eq+datetime’2010-08-13T10%3a32%3a38.0453121Z’
However if you’re using Cloud Storage Studio, URL encoding is not required as Cloud Storage Studio takes care of it for you.
From performance point of view, it is recommended that your query filter must include “PartitionKey” and/or “RowKey” as Windows Azure tables have indexes on these attributes. If these attributes are not included, it will result in full table scan and could lead to inefficient and costly operations depending on your table size. An excellent resource for Windows Azure Storage is the blog by Windows Azure Storage Team here: http://blogs.msdn.com/b/windowsazurestorage/.
Hope this helps.
Any feedback and/or corrections are welcome.