Query Options in OData – Part 1

Query options are basically used to fetch a minimum number of records from a collection or sort the collection, to view the metadata, or to choose only some fields to appear etc. These query options start with ‘$’ followed by the query option name. Some of the examples of query options are $metadata, $format, $count, $select, $top, $skip etc.

In this post, we will learn some important query options $metadata, $format, $count, $select, $top, $skip.

Some basic and needful query options are listed by default in gateway. To use the query options, select the ‘Add URI Option’ in the Gateway and choose the required one from the pop up by double clicking it.

Query Options in OData

Note: Make sure the GET_ENTITYSET method of the entity is implemented before performing the query options, because most of the query options work on a collection of records. In this case, I have implemented logic as below.

Query Options in OData

We need to extend logic in the above method for some (not all) query options

Query option 1: $metadata

This query option is used to fetch the metadata of the service. Metadata is nothing but data about data. In our scenario, it will return metadata of data model.

Request URI:

Query Options in OData

Note: By default, metadata will be returned in xml format. We cannot request metadata in json format

Query option 2: $format

This Query option is used to return the data in the required format i.e., xml, json etc. The default format of data returned is xml. The JSON format is more recommended as it is very quick and light weight.

First, I will fetch data using below URI

Request URI:

Query Options in OData

Here, data is returned in xml format which is by default, we do not need to mention the $format query in the URI.

Now we will request the same data in JSON format

Request URI:

Query Options in OData

The same data is returned in json format

Query option 3: $count

This query option is used to display the number of records in a collection.

Request URI:

Query Options in OData

In this scenario, it will return the number of records that exists in the Airlineset

Query option 4: $select

This query option is used to choose only necessary properties of the entity to appear in the HTTP response body.

Consider, we fetched data for Airlines and it has four fields in the response body as marked below.

Query Options in OData

Here, if we want to restrict displaying unnecessary fields, we can use the $select query. Now, I will only display field ‘Carrid’

Request URI:

Query Options in OData

Note: If you want to fetch more than one field, then the fields in the select query are separated by comma (,) as below URI

Request URI:

Query option 5: $top & $skip

The $top query option is used to fetch only the top n records from table and the $skip query option is used to fetch all the records by skipping the first n records

Consider, we have a table with 10 records. Example: (1,2,3,4,5,6,7,8,9,10)

If we want to fetch only first 3 records, then we go for $top=3. Result: (1,2,3)

If we want to fetch last 3 records, then we go for $skip=7. Result: (8,9,10)

If we want to fetch 3 records by skipping first 2 records, then we go for $skip=2&$top=3. Result: (4,5)

Note: If we mention both queries in same URI, then priority is given to $skip and then $top. So, first it will skip n records and then fetch the top n records.

In case of Airlines example, I have total of 19 records. (Marked as blue in response body)

Query Options in OData

Note: if you want to see the http response in this way, click on the button Data explorer. If you want to return to normal payload, click on the Original payload button as marked as black in above.

Now I will fetch only top 3 records.

Request URI:

Query Options in OData

Now I will fetch all the records by skipping first 3 records.

Request URI:

Query Options in OData

Now I will fetch top 3 records by skip 2 records

Request URI:

Query Options in OData

For $top & $skip, we need to extend the logic in *_GET_ENTITYSET method. The importing parameter IS_PAGING will hold the values at runtime for top and skip queries which are passed in URI.

Query Options in OData

 

Leave a Reply