Understanding OData (with examples)

Introduction

OData, short for Open Data Protocol, is an open protocol that enables the creation and consumption of queryable and interoperable RESTful APIs in a simple and standard way. It is designed to make data available across different software platforms and devices, regardless of the database or storage mechanism.

OData can be used in a wide range of scenarios owing to its versatile nature. It is particularly useful in Business Intelligence (BI) for real-time reporting, analytics, and dashboards. It also plays a significant role in mobile and web application development, allowing developers to easily integrate data from various sources.

Advantages of OData

OData comes with numerous advantages that make it stand out among other data access protocols:

• It offers full CRUD support (Create, Read, Update, Delete) via standard HTTP (web) protocols.
• It allows for a standardised way to expose and consume data, which simplifies the process for developers.
• OData’s support for JSON makes it compatible with a wide range of devices and applications, from web browsers to mobile devices and more.

Key Features of OData

OData boasts several key features that contribute to its versatility and efficiency. It supports Entity Data Model (EDM) which allows for a structured data description, making it easier to understand the schema of the exposed data (such as how data tables are related and joined together).

It also provides a rich and flexible querying experience similar to SQL, including sorting, filtering, and even aggregating data. This is perhaps the most important features of OData because of the way the query can be written in the URL of the OData request. This makes it extremely flexible and is a key differentiator to more standard REST APIs which do not allow this same level of flexibility (you have to use more parameters or use different endpoints – it’s just not as standardis.

Some examples

We will use the TripPin sample OData service to work through some simple examples (the service contains travel related data). You can directly call the service’s URL in a browser or in Postman. This service does not need authentication (most OData services would use authentication such as Basic Auth, Tokens or OAuth). We’ll use Postman here in the blog because of the way it formats the response data. We’ll be sticking to GET requests (i.e. getting data only, not creating, updating or deleting it).

The service is hosted by odata.org, the home of OData. There are some great resources and sample services available there.

First up, let’s query the service to understand the entities available, we can make a request to https://services.odata.org/TripPinRESTierService

We can see a few entities are available (People, Airlines, Airports). We’ll focus on the Airports entity.

Let’s now query the Airports entity to get all the records. We can make a request to https://services.odata.org/TripPinRESTierService/Airports and we’ll get a list of all of the Airports and their details. We are just appending the entity name of Airports to the service’s URL.

If we wanted to get a single Airport we can query the key/Id (in our case the IcaoCode field) such as https://services.odata.org/TripPinRESTierService/Airports('KJFK')

It’s also possible to filter to specific fields for a specific record to, such as only showing the Airport’s Country (CountryRegion field) via the following https://services.odata.org/TripPinRESTierService/Airports('KJFK')/Location/City/CountryRegion

(CountryRegion is a child attribute of City, City is a child of Location, hence the / path of the fields)

Now to the SQL type queries! Firstly we can use Select statements to be selective on the fields we want to query such as only getting the Name and Country of all of the Airports.

Let’s call https://services.odata.org/TripPinRESTierService/Airports?$select=Name, Location/City/CountryRegion

The $select parameter denotes that we what to apply this logic

Lastly, let also apply a filter statement (by appending the $filter parameter) and a filter term, such as $filter=Location/City/CountryRegion eq 'United States' which will filter the Airports where the Country is equal to the United States.

Our full request URL will be: https://services.odata.org/TripPinRESTierService/Airports?$select=Name, Location/City/CountryRegion&$filter=Location/City/CountryRegion eq 'United States'

We then get the response to our targeted query (all through the powerful $select and $filter parameters that OData offers!)

You can find more examples and more parameters at odata.org (parameters such as $top, $skip, $orderby, $count etc)

Conclusion

In summation, OData is a powerful tool that offers a standardised way to expose and consume data across different platforms and devices. Its wide array of features and flexibility makes it an excellent choice for data integration.