The above filtering options can be used as URL parameters.
Syntax:
{parameter name}={parameter prefix}{parameter value}
Example:
/resource/employee?gender=male&age=gte:20
The above example searches the employee table for male gender and age greater than or equals to 20.
| SQL Operator | Description | Parameter Prefix | Examples |
|---|---|---|---|
| = | Exact match | name=kevin |
|
| < | Lesser than | lt: |
age=lt:20 |
| <= | Lesser than or equal | lte: |
age=lte:20 |
| > | Greater than | gt: |
age=gt:20 |
| >= | Greater than or equal | gte: |
age=gte:20 |
| <> | Not equal | nte: |
age=nte:50 |
| LIKE | Search for a specified pattern in the column. Use * as wildcard. | like: |
name=like:kevin* |
| NOT LIKE | Search for a specified pattern not in the column. Use * as wildcard. | ntlike: |
name=ntlike:kevin* |
| IN | Match the list within the specified list of values | in: |
animal=in:cat,dog,bird |
| NOT IN | Match the list not within the specified list of values | ntin: |
animal=ntin:tiger,monkey |
Parameter Keyword:
$sort
Example:
/resource/employee?$sort=name:asc&$sort=age:desc
The above example returns a list of employees sorted by their name in ascending order and age in descending order. Take note of the suffix applied to the parameter value.
Parameter Keyword:
$page and $fetch
$page - the page number start from 1
$fetch - the number of records to fetch in a page
Example:
/resource/employee?$page=1&$fetch=20
The above example returns the first batch of 20 employees.
Parameter Keyword:
$view
Example:
/resource/employee?$view=name,salary
The above example only returns the name and salary columns from the list of employees. If this parameter is not specified then the service will return all columns.
Parameter Keyword:
$sum / $avg / $count / $min / $max
Example:
/resource/employee?$view=status&$sum=salary
The above example sums all the employees salary group by their status. the $view parameter can be used to specify columns in the group by.
Example:
/resource/order?$view=customer_id,status&$sum=price&$count=status
The above example executes 2 aggregate functions at the same time.
Parameter Keyword:
$distinct
Example:
/resource/employee?$distinct=status&$sort=status
The above example returns a list of distinct statuses from all the employees. The distinct function can have more than one column. The url with the distinct parameter must also specify the sort parameter with the same column name or names.