Querying Azure Storage Tables

Azure Table storage is a NoSQL key-value store that can store a large amount of structured, non-relational data. This document explains how to query entities within an Azure Table. Querying is a fundamental operation for retrieving specific data that meets certain criteria. Azure Table storage supports a rich query language that allows for filtering, sorting, and projection of entities.

Important: Queries in Azure Table storage are performed by constructing a filter expression. This expression specifies the conditions that entities must meet to be included in the results.

Filter Expressions

Filter expressions are string literals that define the query criteria. They are composed of property names, comparison operators, and literal values. You can combine multiple conditions using logical operators (and, or).

Supported Operators

The following operators are supported in filter expressions:

Operator	Description
`eq`	Equal
`ne`	Not equal
`gt`	Greater than
`ge`	Greater than or equal to
`lt`	Less than
`le`	Less than or equal to

Logical Operators

You can combine conditions using the following logical operators:

and: Both conditions must be true.
or: At least one of the conditions must be true.

Property Types and Literals

When constructing filter expressions, be mindful of property types and how to represent literals:

String Literals: Enclosed in single quotes (e.g., 'completed').
DateTime Literals: Represented as strings in ISO 8601 format, followed by the letter L (e.g., datetime'2023-10-27T10:00:00Z'L).
GUID Literals: Represented as strings in GUID format, followed by the letter guid (e.g., guid'a1b2c3d4-e5f6-7890-1234-567890abcdef'guid).
Boolean Literals: true or false.
32-bit Integer Literals: Appended with y (e.g., 100y).
64-bit Integer Literals: Appended with l (e.g., 2000l).
Double-precision Floating-point Literals: Use standard decimal notation (e.g., 123.45).

Special Properties

Azure Table storage reserves two special properties for all entities:

PartitionKey: A string that identifies the partition of the entity.
RowKey: A string that uniquely identifies the entity within its partition.

These properties are crucial for efficient querying. Queries that filter on PartitionKey are highly efficient because they can retrieve all entities within a specific partition. Queries that include both PartitionKey and RowKey can retrieve a single entity very quickly.

Examples of Query Filters

1. Retrieving All Entities in a Partition

To retrieve all entities from a partition named Customers:

PartitionKey eq 'Customers'

2. Retrieving a Specific Entity

To retrieve a specific entity with PartitionKey Customers and RowKey user123:

PartitionKey eq 'Customers' and RowKey eq 'user123'

3. Filtering by a Custom Property

To retrieve entities where the Status property is 'Active':

Status eq 'Active'

4. Combining Filters

To retrieve active customers who registered after a specific date:

PartitionKey eq 'Customers' and Status eq 'Active' and Timestamp gt datetime'2023-01-01T00:00:00Z'L

5. Filtering on Numeric Values

To retrieve orders with a total amount greater than 100:

OrderTotal gt 100.00

Note: For optimal performance, design your table schema to leverage PartitionKey and RowKey. Queries that can be satisfied by a single partition access are significantly faster.

Projection

Projection allows you to specify which properties to return in the query results, reducing the amount of data transferred and processed. This is done by including a $select query option with a comma-separated list of property names.

Example of Projection

To retrieve only the Name and Email properties for all active customers:

PartitionKey eq 'Customers' and Status eq 'Active' &$select=Name,Email

Ordering

Azure Table storage supports ordering query results. You can specify an order by property and direction (ascending or descending) using the $orderby query option.

Important: For ordering to work effectively, the PartitionKey and RowKey must be the first properties in the order-by clause, or the query must be scoped to a single partition.

Example of Ordering

To retrieve all active customers, ordered by their registration date in ascending order:

PartitionKey eq 'Customers' and Status eq 'Active' &$orderby=Timestamp asc

You can also order by multiple properties:

PartitionKey eq 'Orders' &$orderby=OrderDate desc, OrderTotal asc

Next Steps

Now that you understand how to query Azure Storage Tables, you can proceed to learn about:

Updating Entities
Deleting Entities (This would be a hypothetical link)