Specifying Queries Using REST or the Browser

Article
06/24/2009

[This document supports a preliminary release of a software product that may be changed substantially prior to final commercial release. This document is provided for informational purposes only.]

The following topic describes specifying queries in applications using the REST interface and in the Web browser.

To understand the query samples given below, you need to be familiar with the URI types and the three-level containment model described in SDS Data Model Overview (Authorities, Containers, Entities and Flexible Entities).

Escaping

When you specify queries in REST, there are rules that govern what makes a valid URI. Some characters are reserved for special meaning. These characters must be escaped when they are used in a query string. The rules that dictate which characters need to be escaped are described in the URI specification at http://www.ietf.org/rfc/rfc2396.txt.

Although there are many escaping rules, some of the most common examples are as follows:

Replace spaces in the query with the "%20" string.
Replace logical operator AND (&&) with "%26%26" string.
Replace the following characters as shown, when they used in where conditions:

;	%3B
?	%3F
:	%3A
&	%26
=	%3D
$	%24
,	%2C

Executing queries in REST applications

In REST, everything on the web is a resource that can be accessed with a URI. The authorities, containers and entities in SDS are all resources in the REST terminology. For example,

This URI retrieves a specific authority.

https://<authority-id>.data.database.windows.net/v1/

This URI retrieves a specific container within a specific authority.

https://<authority-id>.data.database.windows.net/v1/<container-id>

This URI retrieves an entity within a specified container.

https://<authority-id>.data.database.windows.net/v1/<container-id>/<entity-id>

The GET operations above return the XML representation of the specified flexible entity. In REST applications, you sent a GET request with resource URI specified in the web request. For example this code fragment sends a GET request to retrieve entity e1 from container c1 in myAuth authority.

[C#]
string entityUri = "https://myAuth.data.database.windows.net/v1/c1/e1";
WebRequest request = HttpWebRequest.Create(entityUri);
request.Method = "GET";
request.Credentials = new NetworkCredential(userName, password);
  using (HttpWebResponse response = 
                    HttpWebResponse)request.GetResponse())
  {
      // Read and process the response
       …
  }

The web response returns the XML representation of the entity as shown in this sample output

<Entity xmlns:s="https://schemas.microsoft.com/sitka/2008/03/" 
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
             xmlns:x="http://www.w3.org/2001/XMLSchema">
  <s:Id>e1</s:Id>
  <s:Version>86359</s:Version>
  <SomeStringProp xsi:type="x:string">string value</ SomeStringProp >
  <SomeNumericProp xsi:type="x:decimal">0</SomeNumericProp>
  <SomeDataProp xsi:type="x:dateTime">2008-11-10T01:23:50.3506062</SomeDateProp>
…
</Entity>

The GET request can also be used to execute a query to retrieve multiple entities. In this case, the resource URI is followed by a query. When creating the web request following information is required:

The scope
- If query your authorities, the service must be in scope
- If querying for containers in an authority, the authority URI must be in scope
- If querying for entities in a container, the container must be in scope
The scope is then followed by query (q='query expression')

The following code fragment specifies a query to retrieve all entities stored in a container c1 within an authority (muAuth).

string query = "https://myAuth.data.database.windows.net/v1/c1?q='from e in entities select e'";
WebRequest request = HttpWebRequest.Create(query);
request.Method = "GET";
request.Credentials = new NetworkCredential(userName, password);
  using (HttpWebResponse response = 
                    HttpWebResponse)request.GetResponse())
  {
      // Read and process the response
       …
  }

The response in this case is an entity set as shown in the following sample output. Note that the first entity is a blob entity because it has <Content> element.

<s:EntitySet xmlns:s="https://schemas.microsoft.com/sitka/2008/03/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:x="http://www.w3.org/2001/XMLSchema">
  <s:Entity>
    <s:Id>6b27ad79-7c7c-4042-a55b-c9824285c075</s:Id>
    <s:Version>86856</s:Version>
    <s:Content content-type="application/vnd.ms-wpl" content-length="301" content-disposition="inline; filename=DropMe.wpl"/>
  </s:Entity>
  <Entity>
    <s:Id>e1</s:Id>
    <s:Version>86359</s:Version>
  <SomeStringProp xsi:type="x:string">string value</ SomeStringProp >
  <SomeNumericProp xsi:type="x:decimal">0</SomeNumericProp>
  <SomeDataProp xsi:type="x:dateTime">2008-11-10T01:23:50.3506062</SomeDateProp>
…
  </Entity>
</s:EntitySet>

For a working query sample using REST interface, see Specifying Queries Using REST.

Executing Queries in the Browser

These queries can also be executed directly in the browser by specifying the scope followed by a query

Query Examples

The following query examples can be specified in the browser or specified in REST applications as earlier in this topic.

Example A. Find All Containers in an Authority.

To find all containers within a specific authority, you write the authority URI following by the query expression.

https://<authority-id>.data.database.windows.net/v1/q='<query-expression>'

All the following query expressions are equivalent. They return all entities in the specified scope:

q='from e in entities select e'
q='entities'
q=''
q=

The last two query expressions are empty queries where quotes are optional. The following query specifies empty query to retrieve all containers.

***

To find all containers in an authority, you write the authority URI followed by an empty query (?q='' or ?q=):

https://<authority-id>.data.database.windows.net/v1/?q=''

The empty query string indicates select all. For example, the following expression retrieves all containers in the "books-docsamples" authority, you write:

https://books-docsamples.data.database.windows.net/v1/?q=''

A sample result is shown below:

<s:EntitySet xmlns:s="https://schemas.microsoft.com/sitka/2008/03/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:x="http://www.w3.org/2001/XMLSchema">
  <s:Container>
    <s:Id>UsedBooks</s:Id>
    <s:Version>1</s:Version>
  </s:Container>
  <s:Container>
    <s:Id>TechnicalBooks</s:Id>
    <s:Version>1</s:Version>
  </s:Container>
  …
</s:EntitySet>

Example B. Find All Entities in a Container.

To find entities in a container, you write the container URI followed by an empty query (?q='' or ?q=).

https://<authority-id>.data.database.windows.net/v1/<container-id>?q=''

For example, the following expression retrieves all entities in the "UsedBooks" container in the "books-docsamples" authority.

https://books-docsamples.data.database.windows.net/v1/UsedBooks2?q=''

A sample result fragment is shown below:

<s:EntitySet 
     xmlns:s="https://schemas.microsoft.com/sitka/2008/03/" 
     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
     xmlns:x="http://www.w3.org/2001/XMLSchema">
  <UsedBookKind>
    <s:Id>5fdc7c86-b3de-4f6f-a424-3e9c4d9215ad_MySampleBook</s:Id>
    <s:Version>1</s:Version>
    <Title xsi:type="x:string">My Book</Title>
    <ISBN xsi:type="x:string">1-57880-066-36</ISBN>
    <Author xsi:type="x:string">Mr. Author</Author>
    <Publisher xsi:type="x:string">Mr. Publisher</Publisher>
    <InPrint xsi:type="x:boolean">false</InPrint>
    <NumberOfCopiesSold xsi:type="x:decimal">250</NumberOfCopiesSold>
    <PublicationDate xsi:type="x:dateTime">2004-01-27T00:00:00</PublicationDate>
    <CoverPhoto xsi:type="x:base64Binary">AQID</CoverPhoto>
  </UsedBookKind>
  <UsedBookKind>
    …
   </UsedBookKind>
   …
</s:EntitySet>

Example C. Query Id Metadata property (Retrieve Entity Given Its Id).

The following query searches for all books whose ids are greater than some value. Id is a metadata property, the condition in the where clause uses the '.' notation. When specifying this query in a URI you need to set the scope to a specific container:

https://<authority_id>.data.database.windows.net/v1/<container_id>

The URI is followed by the query expression (using "?q="):

from   e 
in     entities 
where  e.Id > "SomeEntityID" 
select e

Combining the two and applying the escaping rules (for example replacing a space with %20) yields:

https://<authority_id>.data.database.windows.net/v1/<container_id>?q='from%20e%20in%20entities%20where%20e.Id%20>%20"SomeEntityId"%20select%20e'

The query returns the XML representation of set of book entities found.

Example D. Query Kind Metadata property (Retrieve entities of a specific kind).

The following query searches a container for entities of a specific kind ("UsedBook"). Because Kind is a metadata property you use the '.' notation in the where clause.

When specifying this query in a URI you need to set the scope to a specific container:

https://<authority_id>.data.database.windows.net/v1/<container_id>

The URI is followed by the query expression (using "?q="):

from   e 
in     entities 
where  e.Kind == "UsedBook" 
select e

Combining the two and applying the escaping rules (for example replacing a space with %20) yields:

https://<authority-id>.data.database.windows.net/v1/<container-id>?q='from%20e%20in%20entities%20where%20e.Kind=="UsedBook"%20select%20e'

Example E. Query a String Flexible Property (Given a Title, Find a Book)

This query searches for one or more books with a specific title. The books whose Title matches are returned. When you specify this query in a URI, you need to set the scope to a specific container:

https://<authority_id>.data.database.windows.net/v1/<container_id>

The URI is followed by the query expression (using "?q="):

from   e 
in     entities 
where  e["Title"] == "My First Book" 
select e

Combining the two and applying the escaping rules (for example, replacing a space with %20) yields the following:

https://<authority_id>.data.database.windows.net/v1/<container_id>?q='from%20e%20in%20entities%20where%20e["Title"]%20==%20"YourEntityId"%20select%20e'

Example F. Query Numeric Flexible Property (Find Books Where Copies Sold < 1000)

This query searches for all books whose NumberOfCopiesSold property value is less than 1000. The query illustrates numeric property value comparisons. When you specify this query in a URI, you need to set the scope to a specific container:

https://<authority_id>.data.database.windows.net/v1/<container_id>

The URI is followed by the query expression (using "?q="):

from   e 
in     entities 
where  e["NumberOfCopiesSold"] < 1000 
select e

Combining the two and applying the escaping rules (for example, replacing a space with %20) yields:

https://<authority_id>.data.database.windows.net/v1/<container_id>?q='%20from%20e%20in%20entities%20where%20e["NumberOfCopiesSold"]%20<%201000%20select%20e%20'

Example G. Query Boolean Flexible Property (Find Books That Are Out of Print)

This query retrieves out-of-print books. The condition in the where clause compares the InPrint Boolean property values to filter the entities. When you specify this query in a URI, you need to set the scope to a specific container:

https://<authority_id>.data.database.windows.net/v1/<container_id>

The URI is followed by the query expression (using "?q="):

from   e 
in     entities 
where  e["InPrint"] == false 
select e

Combining the two and applying the escaping rules (for example, replacing spaces with %20) yields:

https://<authority_id>.data.database.windows.net/v1/<container_id>?q='from%20e%20in%20entities%20where%20e["InPrint"]%20==%20false%20select%20e'

Example H. Query binary Flexible Property

In a query that compares value of a binary flexible property, the comparison value must be provided using the hex encoding. The query must specify the binary() function to convert the hex string to binary for comparison as shown in the following sample query. The query retrieves all entities in the specified container, that satisfy the condition in the where clause.

https://<authority-id>.data.database.windows.net/v1/<container-id>?q='from e in entities where e["binaryProperty"]==Binary("hex string") select e'

Query Using Composite Conditions (Given a Publisher, Find All Books That Are Out of Print).

The query retrieves the out-of-print books that were published by a specific publisher. This example illustrates the use of the logical "AND" (&&) operator. When you specify this query in a URI, you need to set the scope to a specific container:

https://<authority_id>.data.database.windows.net/v1/<container_id>

The URI is followed by the query expression (using "?q="):

from   e 
in     entities 
where  e["InPrint"] == false && e["Publisher"] == "Mr. Publisher3" select e

Combining the two and applying the escaping rules (for example, replacing spaces by %20 and & by %26) yields:

https://<authority_id>.data.database.windows.net/v1/<container_id>?q='from%20e%20in%20entities%20where%20e["InPrint"]%20==%20false%20%26%26%20e["Publisher"]%20==%20"Mr.%20Publisher3"%20select%20e'

Example J. Query Using Date Flexible Property (Find All Books Published After Mar 27, 2006).

This query illustrates comparing date values. The query retrieves books published after a specific date. When you specify this query in a URI, you need to set the scope to a specific container:

https://<authority_id>.data.database.windows.net/v1/<container_id>

The URI is followed by the query expression (using "?q="):

from   e 
in     entities 
where  e["PublicationDate"] > DateTime("2006-03-27") 
select e

Combining the two and applying the escaping rules (for example, replacing a space with %20) yields:

https://<authority_id>.data.database.windows.net/v1/<container_id>?q='from%20e%20in%20entities%20where%20e["PublicationDate"]%20>%20DateTime("2006-03-27")%20select%20e'

Example K. Query Using Metrics properties on authorities and containers

As described in Understanding Metrics Metadata on Authorities and Containers, authorities and containers provide metrics information as properties. These metrics properties are implemented as flexible properties.

Find authorities that have no containers
```
https://data.database.windows.net/v1/?q='from e in entities where e["EntityCount"] == 0 select e'
```
To find user authorities the query specifies the service scope. The condition is applied to all the user authorities and only the authorities satisfying the condition are returned.
Find containers, within an authority, that store more than 1000 entities.
```
https://<authority_id>.data.database.windows.net/v1/?q='from e in entities where e["EntityCount"] > 1000 select e'
```
This query is specified with the specific authority in scope. The filter expression is applied to all the containers in the authority and only the containers satisfying the condition are returned.
Find empty containers in a given authority
```
To be provided.
```

Within an authority, find containers for which there are more than 100,000 GET requests for entities within it.

https://<authority-id>.data.database.windows.net/v1/?q='from e in entities where e["GetCount"] > 100000 select e'

Share via

Specifying Queries Using REST or the Browser

Escaping

Executing queries in REST applications

Executing Queries in the Browser

Query Examples

Example A. Find All Containers in an Authority.

Example B. Find All Entities in a Container.

Example C. Query Id Metadata property (Retrieve Entity Given Its Id).

Example D. Query Kind Metadata property (Retrieve entities of a specific kind).

Example E. Query a String Flexible Property (Given a Title, Find a Book)

Example F. Query Numeric Flexible Property (Find Books Where Copies Sold < 1000)

Example G. Query Boolean Flexible Property (Find Books That Are Out of Print)

Example H. Query binary Flexible Property

Query Using Composite Conditions (Given a Publisher, Find All Books That Are Out of Print).

Example J. Query Using Date Flexible Property (Find All Books Published After Mar 27, 2006).

Example K. Query Using Metrics properties on authorities and containers

See Also

Concepts

Additional resources