Windows Azure Tables: Introducing JSON

Windows Azure Storage team is excited to announce the release of JSON support for Windows Azure Tables as part of version “2013-08-15”. JSON is an alternate OData payload format to AtomPub, which significantly reduces the size of the payload and results in lower latency. To reduce the payload size even further we are providing a way to turn off the payload echo during inserts. Both of these new features are now the default behavior in the newly released Windows Azure Storage Client 3.0 Library.

What is JSON

JSON, JavaScript Object Notation, is a lightweight text format for serializing structured data. Similar to AtomPub, OData extends JSON format by defining general conventions for entities and properties. Unlike AtomPub, parts of the response payload in OData JSON is omitted to reduce the payload size. To reconstitute this data on the receiving end, expressions are used to compute missing links, type and control data. OData supports multiple formats for JSON:

• nometadata – As the name suggests, this format excludes metadata that is used to interpret the data types. This format is the most efficient on transfers which is useful when client is aware on how to interpret the data types for custom properties.
• minimalmetadata – This format contains data type information for custom properties of certain types that cannot be implicitly interpreted. This is useful for query when the client is unaware of the data types such as general tools or Azure Table browsers. However, it still excludes type information for system properties and certain additional information such as edit link, id etc. which can be reconstructed by the client. This is the default level utilized by the Windows Azure Storage Client 3.0 Library
• fullmetadata – This format is useful for generic OData readers that requires type definition for even system properties and requires OData information like edit link, id etc. In most cases for Azure Tables Service, fullmetadata is unnecessary.

For more information regarding the details of JSON payload format and REST API details see Payload Format for Table Service Operation.

To take full advantage of JSON and the additional performance improvements, consider upgrading to Windows Azure Storage Client 3.0 which uses JSON and turns off echo on Insert by default. Older versions of the library do not support JSON.

AtomPub vs JSON format

As mentioned earlier using JSON results in significant reduction in payload size when compared to AtomPub. As a result using JSON is the recommended format and the newly released Windows Azure Storage Client 3.0 Library uses JSON with minimal metadata by default. To get a feel on how JSON request/response looks like as compared to AtomPub, please refer to the Payload Format for Table Service Operations MSDN documentation where payload examples are provided for both AtomPub and JSON format. We have also provided a sample of the JSON payload at the end of this blog.

To compare JSON and AtomPub, we ran the example provided by end of the blog in both JSON and AtomPub and compared the payload for both minimal and no metadata. The example generates 6 requests which includes checking for a table existence, creating the table, inserting 3 entities and querying all entities in that table. The following table summarizes the amount of data transferred back and forth in bytes.

As you can see, and for this example, both minimal and no metadata of the JSON format provide noticeable savings with over 75% reduction in the case of no metadata when compared to AtomPub. This would significantly increase the responsiveness of applications since they spent less time generating and parsing requests in addition to reduced network transfer time.

Other benefits of JSON can be summarized as follow:

1. Other than the performance benefits described above, JSON would reduce your cost as you will be transferring less data.
2. Combining JSON, CORS and SAS features will enable you to build scalable applications where you can access and manipulate your Windows Azure Table data from the web browser directly through JavaScript code.
3. Another benefit of JSON over AtomPub is the fact that some applications may already be using JSON format as the internal object model in which case using JSON with Windows Azure Tables will be a natural transition as it avoids transformations.

Turning off Insert Entity Response Echo Content

In this release, users can further reduce bandwidth usage by turning off the echo of the payload in the response during entity insertion. Through the ODATA wire protocol, echo content can be turned off by specifying the following HTTP header name and value “Prefer: return-no-content”. More information can be found in the Setting the Prefer Header to Manage Response Echo on Insert Operations MSDN documentation. On the Client Library front, no echo is the default behavior of the Windows Azure Storage Client Library 3.0. Note that content echo can still be turned ON for any legacy reasons by setting echoContent to true on the TableOperation.Insert method (example is provided in a subsequent section).

The comparison data provided in the above table was with content echo enabled. However, on a re-run with echo disabled, an additional 30% saving can be seen over JSON NoMetadata payload size. This is very beneficial if the application makes a lot of entity insertions where apart from network transfer reduction application will see great reduction in IO and CPU usage.

Using JSON with Windows Azure Storage Client Library 3.0

The Windows Azure Storage Client Library 3.0 supports JSON as part of the Table Service layer and the WCF Data services layer. We highly recommend our customers to use the Table Service layer as it is optimized for Azure Tables, has better performance as described in here and supports all flavors of JSON format.

Table Service Layer

The Table Service Layer supports all flavors of JSON formats in addition to AtomPub. The format can be set on the CloudTableClient object as seen below:

 CloudTableClient tableClient = new CloudTableClient(baseUri, cred)
{
     // Values supported can be AtomPub, Json, JsonFullMetadata or JsonNoMetadata
     PayloadFormat = TablePayloadFormat.JsonNoMetadata
};

Note that the default value is JSON i.e. JSON Minimal Metadata. You can also decide which format to use per request by passing in a TableRequestOptions with your choice of PayLoadFormat to the CloudTable.Execute method.

In order to control the no-content echo for Insert Entity, you can do so by passing in the appropriate value to the TableOperation.Insert method; Note that by default, the client library will request that no-content is echoed back.

Example:

 // Note that the default value for echoContent is already false
table.Execute(TableOperation.Insert(customer, echoContent: false));

As you can see, the echoContent is set at the individual entity operation level and therefore this would also be applicable to batch operations when using table.ExecuteBatch.

JSON NoMetadata client side type resolution

When using JSON No metadata via the Table Service Layer the client library will “infer” the property types by inspecting the type information on the POCO entity type provided by the client as shown in the JSON example at the end of the blog. (Note, by default the client will inspect an entity type once and cache the resulting information. This cache can be disabled by setting TableEntity.DisablePropertyResolverCache = true;) Additionally, in some scenarios clients may wish to provide the property type information at runtime such as when querying with the DynamicTableEntity or doing complex queries that may return heterogeneous entities. To support this scenario the client can provide a PropertyResolver Func on the TableRequestOptions which allows clients to return an EdmType enumeration for each property based on the data received from the service. The sample below illustrates a PropertyResolver that would allow a user to query the customer data in the example below into DynamicTableEntities.

 TableRequestOptions options = new TableRequestOptions()
     {
          PropertyResolver = (partitionKey, rowKey, propName, propValue) => 
          {
               if(propName == "CustomerSince")
               {
                   return EdmType.DateTime;
               }
               else if(propName == "Rating")
               {
                   return EdmType.Int32;
               }
               else
               {
                   return EdmType.String;
               }
          };
     };

TableQuery<DynamicTableEntity> query = (from ent in complexEntityTable.CreateQuery<DynamicTableEntity>()
                                        select ent).WithOptions(options);

WCF Data Services Layer

As mentioned before, we recommend using the Table Service layer to access Windows Azure Tables. However, if that is not possible for legacy reasons, you might find this section useful.

The WCF Data Services Layer payload format as part the Windows Azure Storage Client Library 3.0 defaults to JSON minimal metadata which is the most concise JSON format supported by the .NET WCF Data Services (there is no support for nometadata). In fact, if your application uses projections (i.e. $select) WCF Data Services will revert to using JSON fullmetadata, which is the most verbose JSON format.

If you wish to use AtomPub, you can set such payload format by calling the following method:

 // tableDataContext is of TableServiceContext type that inherits from the WCF DataServiceContext class
tableDataContext.Format.UseAtom();

In case you decide you want to switch back to JSON (i.e. minimalmetdata and fullmetdata) you can do so by calling the following method:

 tableDataContext.Format.UseJson(new TableStorageModel(tableClient.Credentials.AccountName));

In order to turn off echoing back content on all Insert Operations, you can set the following property:

 // Default value is None which would result in echoing back the content
tableDataContext.AddAndUpdateResponsePreference = DataServiceResponsePreference.NoContent;

Note that both the JSON and no-content echo settings apply to all operations.

JSON example using Windows Azure Storage Client 3.0

In this example, we create a simple address book by storing a few customer’s information in a table, and then we query the content of the Customers table using the table service layer of the Storage Client library. We will also use JSON nometadata and ensure that no-content echo is enabled.

Here is some excerpts of code for the example:

 const string customersTableName = "Customers";

const string connectionString = "DefaultEndpointsProtocol=https;AccountName=[ACCOUNT NAME];AccountKey=[ACCOUNT KEY]";
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(connectionString);                 
            
CloudTableClient tableClient = storageAccount.CreateCloudTableClient();

// Values supported can be AtomPub, Json, JsonFullMetadata or JsonNoMetadata with Json being the default value
tableClient.PayloadFormat = TablePayloadFormat.JsonNoMetadata;

// Create the Customers table
CloudTable table = tableClient.GetTableReference(customersTableName);
table.CreateIfNotExists();

// Insert a couple of customers into the Customers table
foreach (CustomerEntity customer in GetCustomersToInsert())
{
     // Note that the default value for echoContent is already false
     table.Execute(TableOperation.Insert(customer, echoContent: false));
}

            
// Query all customers with first letter of their FirstName in between [I-X] and 
// with rating bigger than or equal to 2. 
// The response have a payload format of JSON no metadata and the 
// client library will map the properties returned back to the CustomerEntity object
IQueryable<CustomerEntity> query = from customer in table.CreateQuery<CustomerEntity>()
                                   where string.Compare(customer.PartitionKey,"I") >=0 && 
                                   string.Compare(customer.PartitionKey,"X")<=0 &&
                                   customer.Rating >= 2
                                   select customer;

CustomerEntity[] customers = query.ToArray();

Here is the CustomerEntity class definition and the GetCustomersToInsert() method that initializes 3 CustomerEntity objects.

 public class CustomerEntity : TableEntity
{
    public CustomerEntity() { }

    public CustomerEntity(string firstName, string lastName)
    {    
        this.PartitionKey = firstName;
        this.RowKey = lastName;
    }

    [IgnoreProperty]
    public string FirstName
    {
        get { return this.PartitionKey; }
    }

    [IgnoreProperty]
    public string LastName
    {
        get { return this.RowKey; }
    }

    public string Address { get; set; }
    public string Email { get; set; }
    public string PhoneNumber { get; set; }
    public DateTime? CustomerSince { get; set; }
    public int? Rating { get; set; }
}

private static IEnumerable<CustomerEntity> GetCustomersToInsert()
{
    return new[]
    {
     new CustomerEntity("Walter", "Harp")
     {
      Address = "1345 Fictitious St, St Buffalo, NY 98052",
      CustomerSince = DateTime.Parse("01/05/2010"),
      Email = "Walter@contoso.com",
      PhoneNumber = "425-555-0101",
      Rating = 4
     },
     new CustomerEntity("Jonathan", "Foster")
     {
      Address = "1234 SomeStreet St, Bellevue, WA 75001",
      CustomerSince = DateTime.Parse("01/05/2005"),
      Email = "Jonathan@fourthcoffee.com",
      PhoneNumber = "425-555-0101",
      Rating = 3
     },
     new CustomerEntity("Lisa", "Miller")
     {
      Address = "4567 NiceStreet St, Seattle, WA 54332",
      CustomerSince = DateTime.Parse("01/05/2003"),
      Email = "Lisa@northwindtraders.com",
      PhoneNumber = "425-555-0101",
      Rating = 2
     }
    };
}

JSON Payload Example

Here are the 3 different response payloads corresponding to AtomPub, JSON minimalmetadata and JSON nometadata for the query request generated as part of the previous example. Note that the payload have been formatted for readability purposes. The actual wire payload does not have any indentation or newline breaks.

AtomPub

 <?xml version="1.0" encoding="utf-8"?>
<feed xml:base="https://someaccount.table.core.windows.net/" xmlns="https://www.w3.org/2005/Atom" xmlns:d="https://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="https://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns:georss="https://www.georss.org/georss" xmlns:gml="https://www.opengis.net/gml">
  <id>https://someaccount.table.core.windows.net/Customers</id>
  <title type="text">Customers</title>
  <updated>2013-12-03T06:37:21Z</updated>
  <link rel="self" title="Customers" href="Customers" />
  <entry m:etag="W/&quot;datetime'2013-12-03T06%3A37%3A20.9709094Z'&quot;">
    <id>https://someaccount.table.core.windows.net/Customers(PartitionKey='Jonathan',RowKey='Foster')</id>
    <category term="someaccount.Customers" scheme="https://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
    <link rel="edit" title="Customers" href="Customers(PartitionKey='Jonathan',RowKey='Foster')" />
    <title />
    <updated>2013-12-03T06:37:21Z</updated>
    <author>
      <name />
    </author>
    <content type="application/xml">
      <m:properties>
        <d:PartitionKey>Jonathan</d:PartitionKey>
        <d:RowKey>Foster</d:RowKey>
        <d:Timestamp m:type="Edm.DateTime">2013-12-03T06:37:20.9709094Z</d:Timestamp>
        <d:Address>1234 SomeStreet St, Bellevue, WA 75001</d:Address>
        <d:Email>Jonathan@fourthcoffee.com</d:Email>
        <d:PhoneNumber>425-555-0101</d:PhoneNumber>
        <d:CustomerSince m:type="Edm.DateTime">2005-01-05T00:00:00Z</d:CustomerSince>
        <d:Rating m:type="Edm.Int32">3</d:Rating>
      </m:properties>
    </content>
  </entry>
  <entry m:etag="W/&quot;datetime'2013-12-03T06%3A37%3A21.1259249Z'&quot;">
    <id>https://someaccount.table.core.windows.net/Customers(PartitionKey='Lisa',RowKey='Miller')</id>
    <category term="someaccount.Customers" scheme="https://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
    <link rel="edit" title="Customers" href="Customers(PartitionKey='Lisa',RowKey='Miller')" />
    <title />
    <updated>2013-12-03T06:37:21Z</updated>
    <author>
      <name />
    </author>
    <content type="application/xml">
      <m:properties>
        <d:PartitionKey>Lisa</d:PartitionKey>
        <d:RowKey>Miller</d:RowKey>
        <d:Timestamp m:type="Edm.DateTime">2013-12-03T06:37:21.1259249Z</d:Timestamp>
        <d:Address>4567 NiceStreet St, Seattle, WA 54332</d:Address>
        <d:Email>Lisa@northwindtraders.com</d:Email>
        <d:PhoneNumber>425-555-0101</d:PhoneNumber>
        <d:CustomerSince m:type="Edm.DateTime">2003-01-05T00:00:00Z</d:CustomerSince>
        <d:Rating m:type="Edm.Int32">2</d:Rating>
      </m:properties>
    </content>
  </entry>
  <entry m:etag="W/&quot;datetime'2013-12-03T06%3A37%3A20.7628886Z'&quot;">
    <id>https://someaccount.table.core.windows.net/Customers(PartitionKey='Walter',RowKey='Harp')</id>
    <category term="someaccount.Customers" scheme="https://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
    <link rel="edit" title="Customers" href="Customers(PartitionKey='Walter',RowKey='Harp')" />
    <title />
    <updated>2013-12-03T06:37:21Z</updated>
    <author>
      <name />
    </author>
    <content type="application/xml">
      <m:properties>
        <d:PartitionKey>Walter</d:PartitionKey>
        <d:RowKey>Harp</d:RowKey>
        <d:Timestamp m:type="Edm.DateTime">2013-12-03T06:37:20.7628886Z</d:Timestamp>
        <d:Address>1345 Fictitious St, St Buffalo, NY 98052</d:Address>
        <d:Email>Walter@contoso.com</d:Email>
        <d:PhoneNumber>425-555-0101</d:PhoneNumber>
        <d:CustomerSince m:type="Edm.DateTime">2010-01-05T00:00:00Z</d:CustomerSince>
        <d:Rating m:type="Edm.Int32">4</d:Rating>
      </m:properties>
    </content>
  </entry>
</feed>

JSON minimalmetadata

 {
   "odata.metadata":"https://someaccount.table.core.windows.net/$metadata#Customers",
   "value":[
      {
         "PartitionKey":"Jonathan",
         "RowKey":"Foster",
         "Timestamp":"2013-12-03T06:39:56.6443475Z",
         "Address":"1234 SomeStreet St, Bellevue, WA 75001",
         "Email":"Jonathan@fourthcoffee.com",
         "PhoneNumber":"425-555-0101",
         "CustomerSince@odata.type":"Edm.DateTime",
         "CustomerSince":"2005-01-05T00:00:00Z",
         "Rating":3
      },
      {
         "PartitionKey":"Lisa",
         "RowKey":"Miller",
         "Timestamp":"2013-12-03T06:39:56.7943625Z",
         "Address":"4567 NiceStreet St, Seattle, WA 54332",
         "Email":"Lisa@northwindtraders.com",
         "PhoneNumber":"425-555-0101",
         "CustomerSince@odata.type":"Edm.DateTime",
         "CustomerSince":"2003-01-05T00:00:00Z",
         "Rating":2
      },
      {
         "PartitionKey":"Walter",
         "RowKey":"Harp",
         "Timestamp":"2013-12-03T06:39:56.4743305Z",
         "Address":"1345 Fictitious St, St Buffalo, NY 98052",
         "Email":"Walter@contoso.com",
         "PhoneNumber":"425-555-0101",
         "CustomerSince@odata.type":"Edm.DateTime",
         "CustomerSince":"2010-01-05T00:00:00Z",
         "Rating":4
      }
   ]
}

JSON nometadata

 "value":[
   {
      "PartitionKey":"Jonathan",
      "RowKey":"Foster",
      "Timestamp":"2013-12-03T06:45:00.7254269Z",
      "Address":"1234 SomeStreet St, Bellevue, WA 75001",
      "Email":"Jonathan@fourthcoffee.com",
      "PhoneNumber":"425-555-0101",
      "CustomerSince":"2005-01-05T00:00:00Z",
      "Rating":3
   },
   {
      "PartitionKey":"Lisa",
      "RowKey":"Miller",
      "Timestamp":"2013-12-03T06:45:00.8834427Z",
      "Address":"4567 NiceStreet St, Seattle, WA 54332",
      "Email":"Lisa@northwindtraders.com",
      "PhoneNumber":"425-555-0101",
      "CustomerSince":"2003-01-05T00:00:00Z",
      "Rating":2
   },
   {
      "PartitionKey":"Walter",
      "RowKey":"Harp",
      "Timestamp":"2013-12-03T06:45:00.5384082Z",
      "Address":"1345 Fictitious St, St Buffalo, NY 98052",
      "Email":"Walter@contoso.com",
      "PhoneNumber":"425-555-0101",
      "CustomerSince":"2010-01-05T00:00:00Z",
      "Rating":4
   }
]
}

Resources

Windows Azure Storage Release - Introducing CORS, JSON, Minute Metrics, and More

Windows Azure Storage Client Library (3.0) Binary - https://www.nuget.org/packages/WindowsAzure.Storage

Windows Azure Storage Client Library (3.0) Source - https://github.com/WindowsAzure/azure-storage-net

Please let us know if you have any further questions either via forum or comments on this post,

Jean Ghanem, Sam Merat, Joe Giardino, and Jai Haridas

Comments

• Anonymous
  December 12, 2013
  Can you provide an example with JQuery? How do we make a call directly to Table Storage for data binding in JavaScript?

• Anonymous
  December 12, 2013
  @Raj, we are working on a blog covering CORS which also has JSON example using JQuery that demonstrates what you have requested. We should have that in the next couple of weeks. -Jai

• Anonymous
  January 28, 2014
  Is there any additional 3.0 documentation, yet?  Is it possible to work with raw JSON (both read/write) on the server with the .NET client library?

• Anonymous
  May 13, 2014
  Is the Json output format supported for querying the Storage Analytics too, i.e. Tables ("MetricsCapacityBlob").

• Anonymous
  May 13, 2014
  @Lakshmi - yes it is supported for Metrics tables too.