Querying Azure Storage Tables

Introduction

Azure Storage Tables provide a NoSQL key-attribute store. Querying these tables efficiently is crucial for retrieving the data you need. This document outlines the various methods and syntax for querying Azure Storage Tables, primarily leveraging the OData protocol.

You can query tables to retrieve entities that match specific criteria. The queries are built using OData syntax, which allows for filtering, projection, sorting, and pagination.

Query Operators

Azure Table Storage supports a rich set of operators for building queries. These operators are used within the $filter query option.

String properties can also be queried using methods like startswith, endswith, and substringof.

Projection

Projection allows you to select specific properties (columns) to return in your query results, reducing bandwidth and improving performance. This is achieved using the $select query option.

Example: To retrieve only the Name and Email properties:

GET /MyTable(PartitionKey='abc',RowKey='123')?$select=Name,Email HTTP/1.1

Filtering

Filtering is used to retrieve entities that match specific criteria. This is done using the $filter query option. You can filter on any property, including PartitionKey and RowKey.

Example: To retrieve all entities where Age is greater than 30:

GET /MyTable?$filter=Age gt 30 HTTP/1.1

Example: To retrieve entities where City is 'London' and Age is less than 25:

GET /MyTable?$filter=City eq 'London' and Age lt 25 HTTP/1.1

When filtering on string properties, ensure strings are enclosed in single quotes.

Sorting

You can sort your query results in ascending or descending order using the $orderby query option. By default, results are sorted by PartitionKey then RowKey.

Example: To sort by LastName in ascending order:

GET /MyTable?$orderby=LastName ASC HTTP/1.1

Example: To sort by Score in descending order, then by Name ascending:

GET /MyTable?$orderby=Score desc,Name asc HTTP/1.1

Pagination

Azure Storage Tables return results in pages. By default, each page contains up to 1000 entities. To retrieve subsequent pages, you use the NextPageLink provided in the response. You can also control the page size using the $top query option, though there's a hard limit of 1000 entities per page.

Example: To retrieve the first 50 entities:

GET /MyTable?$top=50 HTTP/1.1

OData Query Syntax

Azure Storage Table queries adhere to the OData protocol. The common query options include:

• $filter: Filters entities based on a specified condition.
• $select: Selects specific properties to return.
• $orderby: Sorts the query results.
• $top: Specifies the maximum number of entities to return.
• $skip: Skips a specified number of entities.
• $inlinecount: (Deprecated, use NextPageLink) - Used to request the total number of entities matching the query.

These options are appended to the table resource URL using the query string syntax (? and &).

Examples

Here are a few common query examples:

Example 1: Get entities with a specific PartitionKey

GET /Customers?$filter=PartitionKey eq 'USA' HTTP/1.1

Example 2: Get entities with a specific PartitionKey and RowKey

GET /Customers(PartitionKey='USA',RowKey='customer1001') HTTP/1.1

Example 3: Filter by a string property and select specific columns

GET /Products?$filter=Category eq 'Electronics'&$select=ProductID,Name,Price HTTP/1.1

Example 4: Sort by date and filter

GET /Orders?$filter=OrderDate ge '2023-01-01'&$orderby=OrderDate desc HTTP/1.1

Example 5: Combined filter, select, and order

GET /Employees?$filter=Department eq 'Sales' and Salary gt 50000&$select=EmployeeID,Name,Department,Salary&$orderby=Salary desc HTTP/1.1