oData and JSON payload examples


oData and JSON Format

The Open Data Protocol (OData) is a data access protocol built on core protocols like HTTP and commonly accepted methodologies like REST for the web. OData provides a uniform way to query and manipulate data sets through CRUD operations (create, read, update, and delete).

JSON is a lightweight data-interchange format. It is easy for humans to read and write. It is easy for machines to parse and generate. JSON is a text format that is completely language independent. JSON is built on a collection of name/value pairs realized in most modern languages.

OData’s JSON format extends JSON by defining a set of canonical annotations for control information such as ids, types, and links, and custom annotations MAY be used to add domain-specific information to the payload.

 

A key feature of OData’s JSON format is to allow omitting predictable parts of the wire format from the actual payload. Expressions are used to compute missing links, type information, and other control data on client side. Annotations are used in JSON to capture control information that cannot be predicted (e.g., the next link of a collection of entities) as well as a mechanism to provide values where a computed value would be wrong (e.g., if the media read link of one particular entity does not follow the standard URL conventions).

 

Computing values from metadata expressions on client side could be very expense. To accommodate for this the Accept header allows the client to control the amount of control information added to the response.

This doc is aiming to provide a quick overview of what’s the JSON payload would be look like in various typical scenarios.

 

Data model Used for the Sample

We use the Northwind database as the SQL sample database in all our JSON examples. Northwind is a fictitious company that imports and exports foods globally. The Northwind sample database provides a good database structure for our JSON experiment.

Please see http://msdn.microsoft.com/en-us/library/ms227484(v=vs.80).aspx to get instructions of how to setup the database with your sqlserver

The following database diagram illustrates the Northwind database structure:

 

 

 

Response payload samples

This section provides JSON samples of response payloads for the payload kinds supported in OData. The samples are based on a northwind domain.

Read an EntitySet

http://services.odata.org/V3/OData/OData.svc/Products?$format=json

{
   "odata.metadata":"http://services.odata.org/V3/OData/OData.svc/$metadata#Products",
   "value":[
      {
         "ID":0,
         "Name":"Bread",
         "Description":"Whole grain bread",
         "ReleaseDate":"1992-01-01T00:00:00",
         "DiscontinuedDate":null,
         "Rating":4,
         "Price":"2.5"
      },
      {
         "ID":1,
         "Name":"Milk",
         "Description":"Low fat milk",
         "ReleaseDate":"1995-10-01T00:00:00",
         "DiscontinuedDate":null,
         "Rating":3,
         "Price":"3.5"
      },
      {
         "ID":2,
         "Name":"Vint soda",
         "Description":"Americana Variety - Mix of 6 flavors",
         "ReleaseDate":"2000-10-01T00:00:00",
         "DiscontinuedDate":null,
         "Rating":3,
         "Price":"20.9"
      },
   …
   ]
}

 

 

 

Read an Entity

http://services.odata.org/V3/OData/OData.svc/Products(2)?$format=json

 

 

{
   "odata.metadata":"http://services.odata.org/V3/OData/OData.svc/$metadata#Products/@Element",
   "ID":2,
   "Name":"Vint soda",
   "Description":"Americana Variety - Mix of 6 flavors",
   "ReleaseDate":"2000-10-01T00:00:00",
   "DiscontinuedDate":null,
   "Rating":3,
   "Price":"20.9"
}

 

Read Property

 

http://services.odata.org/V3/OData/OData.svc/Products(2)/Description?$format=json

{
   "odata.metadata":"http://services.odata.org/V3/OData/OData.svc/$metadata#Edm.String",
   "value":"Americana Variety - Mix of 6 flavors"
}

 

http://odataservices.azurewebsites.net/OData/OData.svc/Products(0)/Name?$format=application/json

{
   "odata.metadata":"http://odataservices.azurewebsites.net/OData/OData.svc/$metadata#Edm.String",
   "value":"Bread"
}

 

 

Remove an Entity

 

DELETE http://services.odata.org/V3/(S(ettihtez1pypsghekhjamb1u))/OData/OData.svc/Products(200) HTTP/1.1

DataServiceVersion: 1.0;NetFx

MaxDataServiceVersion: 3.0;NetFx

Accept: application/json;odata=minimalmetadata

Accept-Charset: UTF-8

User-Agent: Microsoft ADO.NET Data Services

Host: services.odata.org

 

Create an Entry

 

POST http://services.odata.org/v3/(S(ettihtez1pypsghekhjamb1u))/odata/odata.svc/Products HTTP/1.1

DataServiceVersion: 3.0;NetFx

MaxDataServiceVersion: 3.0;NetFx

Content-Type: application/json;odata=minimalmetadata

Accept: application/json;odata=minimalmetadata

Accept-Charset: UTF-8

User-Agent: Microsoft ADO.NET Data Services

Host: services.odata.org

Content-Length: 180

Expect: 100-continue

 

 

{
   "odata.type":"ODataDemo.Product",
   "Description":null,
   "DiscontinuedDate":null,
   "ID":200,
   "Name":"My new product from leo",
   "Price":"5.6",
   "Rating":0,
   "ReleaseDate":"0001-01-01T00:00:00"
}

 

Update an Entry with PATCH and PUT

PATCH

Services SHOULD support PATCH as the preferred means of updating an entity. PATCH provides more resiliency between clients and services by directly modifying only those values specified by the client.

PATCH http://services.odata.org/V3/(S(ettihtez1pypsghekhjamb1u))/OData/OData.svc/Products(200) HTTP/1.1

DataServiceVersion: 3.0;NetFx

MaxDataServiceVersion: 3.0;NetFx

Content-Type: application/json;odata=minimalmetadata

Accept: application/json;odata=minimalmetadata

Accept-Charset: UTF-8

User-Agent: Microsoft ADO.NET Data Services

Host: services.odata.org

Content-Length: 180

Expect: 100-continue

 

{
   "Name":"Update name from leo"
}

 

PUT

Services MAY additionally support PUT, but should be aware of the potential for data-loss in round-tripping properties that the client may not know about in advance, such as open or added properties, or properties not specified in metadata. Services that support PUT MUST replace all values of structural properties with those specified in the request body

PUT http://services.odata.org/V3/(S(ettihtez1pypsghekhjamb1u))/OData/OData.svc/Products(200) HTTP/1.1

DataServiceVersion: 3.0;NetFx

MaxDataServiceVersion: 3.0;NetFx

Content-Type: application/json;odata=minimalmetadata

Accept: application/json;odata=minimalmetadata

Accept-Charset: UTF-8

User-Agent: Microsoft ADO.NET Data Services

Host: services.odata.org

Content-Length: 180

Expect: 100-continue

 

{
   "odata.type":"ODataDemo.Product",
   "Description":null,
   "DiscontinuedDate":null,
   "ID":200,
   "Name":"Update name from leo",
   "Price":"5.6",
   "Rating":0,
   "ReleaseDate":"0001-01-01T00:00:00"
}

 

 

Read Complex Type

 

http://services.odata.org/V3/OData/OData.svc/Suppliers(1)/Address?$format=json

{
   "odata.metadata":"http://services.odata.org/V3/OData/OData.svc/$metadata#ODataDemo.Address",
   "Street":"NE 40th",
   "City":"Redmond",
   "State":"WA",
   "ZipCode":"98052",
   "Country":"USA"
}

 

Read Entry with expanded navigation links

http://odataservices.azurewebsites.net/OData/OData.svc/Products(0)/Category?$expand=ID&$format=json

{
   "odata.metadata":"http://odataservices.azurewebsites.net/OData/OData.svc/$metadata#Categories/@Element",
   "ID":0,
   "Name":"Food"
}

Read Service document

http://odataservices.azurewebsites.net/OData/OData.svc/?$format=json

{
   "odata.metadata":"http://odataservices.azurewebsites.net/OData/OData.svc/$metadata",
   "value":[
      {
         "name":"Products",
         "url":"Products"
      },
      {
         "name":"Advertisements",
         "url":"Advertisements"
      },
      {
         "name":"Categories",
         "url":"Categories"
      },
      {
         "name":"Suppliers",
         "url":"Suppliers"
      }
   ]

 

Read an EntitySet with full metadata

http://services.odata.org/V3/OData/OData.svc/Products?$format=application/json;odata=fullmetadata

{
   "odata.metadata":"http://services.odata.org/V3/OData/OData.svc/$metadata#Products",
   "value":[
      {
         "odata.type":"ODataDemo.Product",
         "odata.id":"http://services.odata.org/V3/OData/OData.svc/Products(0)",
         "odata.editLink":"Products(0)",
         "Category@odata.navigationLinkUrl":"Products(0)/Category",
         "Category@odata.associationLinkUrl":"Products(0)/$links/Category",
         "Supplier@odata.navigationLinkUrl":"Products(0)/Supplier",
         "Supplier@odata.associationLinkUrl":"Products(0)/$links/Supplier",
         "ID":0,
         "Name":"Bread",
         "Description":"Whole grain bread",
         "ReleaseDate@odata.type":"Edm.DateTime",
         "ReleaseDate":"1992-01-01T00:00:00",
         "DiscontinuedDate":null,
         "Rating":4,
         "Price@odata.type":"Edm.Decimal",
         "Price":"2.5"
      },
      {
         "odata.type":"ODataDemo.Product",
         "odata.id":"http://services.odata.org/V3/OData/OData.svc/Products(1)",
         "odata.editLink":"Products(1)",
         "Category@odata.navigationLinkUrl":"Products(1)/Category",
         "Category@odata.associationLinkUrl":"Products(1)/$links/Category",
         "Supplier@odata.navigationLinkUrl":"Products(1)/Supplier",
         "Supplier@odata.associationLinkUrl":"Products(1)/$links/Supplier",
         "ID":1,
         "Name":"Milk",
         "Description":"Low fat milk",
         "ReleaseDate@odata.type":"Edm.DateTime",
         "ReleaseDate":"1995-10-01T00:00:00",
         "DiscontinuedDate":null,
         "Rating":3,
         "Price@odata.type":"Edm.Decimal",
         "Price":"3.5"
      },
      ]
}

 

Read an Entity with full metadata

 

http://services.odata.org/V3/OData/OData.svc/Products(2)?$format=application/json;odata=fullmetadata

{
   "odata.metadata":"http://services.odata.org/V3/OData/OData.svc/$metadata#Products/@Element",
   "odata.type":"ODataDemo.Product",
   "odata.id":"http://services.odata.org/V3/OData/OData.svc/Products(2)",
   "odata.editLink":"Products(2)",
   "Category@odata.navigationLinkUrl":"Products(2)/Category",
   "Category@odata.associationLinkUrl":"Products(2)/$links/Category",
   "Supplier@odata.navigationLinkUrl":"Products(2)/Supplier",
   "Supplier@odata.associationLinkUrl":"Products(2)/$links/Supplier",
   "ID":2,
   "Name":"Vint soda",
   "Description":"Americana Variety - Mix of 6 flavors",
   "ReleaseDate@odata.type":"Edm.DateTime",
   "ReleaseDate":"2000-10-01T00:00:00",
   "DiscontinuedDate":null,
   "Rating":3,
   "Price@odata.type":"Edm.Decimal",
   "Price":"20.9"
}

 

 

 

Read Entry with expanded navigation links with full metadata

 

http://odataservices.azurewebsites.net/OData/OData.svc/Products(0)/Category?$expand=ID&$format=application/json;odata=fullmetadata

{
   "odata.metadata":"http://odataservices.azurewebsites.net/OData/OData.svc/$metadata#Categories/@Element",
   "odata.type":"ODataDemo.Category",
   "odata.id":"http://odataservices.azurewebsites.net/OData/OData.svc/Categories(0)",
   "odata.editLink":"Categories(0)",
   "Products@odata.navigationLinkUrl":"Categories(0)/Products",
   "Products@odata.associationLinkUrl":"Categories(0)/$links/Products",
   "ID":0,
   "Name":"Food"
}

 


Comments (7)

  1. Good says:

    Nice post !

    Do you have some examples to use OData functions too ?

    Regards

  2. leohu says:

    are u talking about general odata functionality or the "function" in spec 4.0?

  3. chenjf2 says:

    Hi, ask a question about Edm.DateTime atom format:

    http://www.odata.org/…/atom-format

    default: yyyy "-" mm "-" dd "T" hh ":" mm [":" ss["." fffffff]]

    for example china beijing Time : 2013-10-10 08:00 , result to: 2013-10-10T00:00

    what can i do , change default format, to china format?

    thinks a lot.

    my email: 18552070@qq.com

    I'm sorray,My English bad, chinese as follow:

    ——————-

    你好,问一个关于WCF Data Service的Edm.DateTime序列化格式的问题,

    http://www.odata.org/…/atom-format

    默认格式:yyyy "-" mm "-" dd "T" hh ":" mm [":" ss["." fffffff]]

    比如 中文北京时间:2013-10-10 08:00,默认输出变成:2013-10-10T00:00

    (日期和时间中间多一个T,中文时差8小时)

    如何才能改成本地化的时间格式,这样显示对用户比较友好。

    非常感谢!

    我的邮箱:18552070@qq.com

  4. Good says:

    @ leohu Yes, I'm talking of  "function" in spec 4.0.

    Do you have some informations ?

    Thanks a lot

  5. leo says:

    im posting a doc about function pretty soon.

    the feature is currently under internal review.

  6. Naaz says:

    I am not able to create a entity with create command plz help me

Skip to main content