Implementing All OData Query/URI Options – Part 1

Introduction

We have some very good blogs on how to create an OData service from scratch. I would recommend these blogs if you are new to OData.

Blog1   Blog2    Blog3  Blog4

SAP has given us options for multiple URI call options. In this blog, we will be implementing them. I will be trying to add all of them to a blog with example(s).

Steps

We can divide OData URI into 2 parts:

1. Do Not Need Custom Implementation (This Blog)
   

   1. $select
   2. $count
   3. $expand
   4. $format
   5. $links
   6. $value
2. Need Custom Implementation (Implementing All OData Query/URI Options – Part 2)
   
   1. $orderby
   2. $top
   3. $skip
   4. $filter
   5. $inlinecount
   6. $skiptoken

Implementation

Let’s start with the URI commands which do not need any custom implementations:

 1. $select

the $select query option in OData is used to specify which properties or fields of a resource should be included in the response. The $select option is typically used to optimize performance by limiting the number of columns that are returned in a query. The value of the $select option is a comma-separated list of the properties or fields that should be included in the response.

For example, if you want to retrieve only the name and address fields of a customer resource, you would use a query like /Customers?$select=Name, Address.

This is an example string that is likely being used to select specific fields, “Name” and “Address,” from a collection of “Customers” in a database.

/sap/opu/odata/sap/ZNS_PORELATED_SRV/EKKOSet?$select=Ebeln, Bukrs, Ernam

The above query will give selected fields i.e Ebeln, Burks and Ernam from the DB:

1. $select.jpg

We can get a similar result for a particular record as well.

 /sap/opu/odata/sap/ZNS_PORELATED_SRV/EKKOSet('4500000000')?$select=Ebeln, Bukrs, Ernam

The above query will give the below result:

1.1. $select.jpg

2. $count

The $count query option in OData is used to retrieve the number of entities in a collection. The $count option is typically used to optimize performance by limiting the amount of data that is returned in a query.

When the $count option is included in a query, the response will include the total number of entities in the collection, rather than the actual entities themselves.

For example, if you want to retrieve the number of customers in a customer resource, you would use a query like /Customers/$count.

The response will return a single number, for example 28.

It’s important to note that the $count option can only be used on collections and not on individual entities.

We can also combine the $count with other options like $top, $skip, $filter, $expand etc.

/sap/opu/odata/sap/ZNS_PORELATED_SRV/EKKOSet('6000000000')/ToPOItem/$count

The above query will count the total number of records for the mentioned PO number.

2. $count.jpg

3. $expand

The $expand query option in OData is used to retrieve related entities along with the requested entity. The $expand option is used to retrieve related data in a single request instead of making multiple requests to the server.

For example, if you have an entity called “Orders” and it has a navigation property called “OrderItems” which represents the items that are part of the order, you can use the $expand option to retrieve both the Order and the OrderItems in a single request.

The query would look like /Orders?$expand=OrderItems

This query would return the Order entity along with the related OrderItems entities in the same response. You can also specify multiple navigation properties by separating them with a comma.

It is important to note that, when using $expand query option, it increases the size of the response and can affect the performance of the query. Therefore, it’s good practice to use it judiciously and only when it’s needed.

We can also use it in combination with other query options like $select, $filter, $top, $skip etc.

/sap/opu/odata/sap/ZNS_PORELATED_SRV/EKPOSet(Ebeln='6000000000',Ebelp='00010')?$expand=ToMara,ToMatDesc&$format=json

The above query will give the below result:

3. $expand.jpg

4. $format

The $format query option in OData is used to specify the format of the response. The $format option can be used to request the response in different formats such as JSON, XML

/Orders?$format=json

/Orders?$format=xml

/sap/opu/odata/sap/ZNS_PORELATED_SRV/EKKOSet?$format=json

The above query will give the result in JSON format.

4. $format.jpg

5. $links
The $links query option in OData is used to return the links between entities. The $links option is used to retrieve navigation links between related entities in a single request.

When the $links option is included in a query, the response will include URLs for the navigation properties of the entity, which can be used to retrieve the related entities. For example, if you have an entity called “Orders” and it has a navigation property called “OrderItems” which represents the items that are part of the order, you can use the $links option to retrieve the URLs for the OrderItems entities.

The query would look like /Orders?$links=OrderItems.

This query would return the URLs for the related OrderItems entities in the same response. You can also specify multiple navigation properties by separating them with a comma.

It is important to note that, when using $links query option, it increases the size of the response and can affect the performance of the query. Therefore, it’s good practice to use it judiciously and only when it’s needed.

We can also use it in combination with other query options like $select, $filter, $expand, $format etc.

/sap/opu/odata/sap/ZNS_PORELATED_SRV/EKKOSet('6000000000')/$links/ToPOItem

The above query will give the below result:

5. $links.jpg

6. $value

The $value query option in OData is used to return only the value of a primitive property or a complex property without the property name and metadata. The $value option is used to retrieve the value of a property in a single request instead of returning the entire entity or complex object.

For example, if you have an entity called “Products” and it has a primitive property called “Price”, you can use the $value option to retrieve the value of the Price property in a single request.

The query would look like /Products(‘001’)/Price/$value.

This query would return the value of the Price property in the response.

It is important to note that, the $value option can only be used on primitive properties and complex properties and not on collection properties.

This can be used in combination with other query options like $select, $filter, $expand, $format etc.

/sap/opu/odata/sap/ZNS_PORELATED_SRV/EKKOSet('6000000000')/Bukrs/$value

The above query will show the value of Burks for the mentioned records.

6. $value.jpg

Conclusion

With the above-mentioned queries, we can utilize the inbuild Odata options without writing any additional underlying code.

If you have any thoughts or questions on the topic, please feel free to leave a comment below. I would love to hear from you.

I highly recommend that you check out the second part of the series on Implementing OData Query/URI Options Implementing All OData Query/URI Options – Part 2.

It provides valuable insights into the implementation of OData query options.

Edit 1: Thanks @gregorw for helping to enrich the blog.

In the world of Odata services, there are two types of services –

• Those created from SEGW
• Those based on CDS views

When creating Odata services from SEGW, custom implementation is required for the $orderby, $top, $skip, $filter, and $inlinecount query options. However, this is not the case when the Odata service is based on a CDS view. In this scenario, SAP provides these query options without the need for implementation, making the development process simpler and more efficient.

It’s important to note that this difference exists because of the fundamental architectural differences between SEGW and CDS views. SEGW generates ABAP code based on metadata, whereas CDS views are defined in the ABAP repository and translated into Open SQL. By leveraging the capabilities of CDS views, developers can benefit from SAP’s pre-built query options and focus on delivering value to their customers.

If you found this post helpful, please like and share it with your network 🙂

Kind Regards,

Nitin Sharma