Book a Demo Free Trial

Specifying $filter Criteria When Querying Azure Table Storage Using REST API

Gaurav Mantri

Aug 13, 2010

Category: Azure Storage

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.