How to Filter, Sort & Search REST API

Filtering, sorting and searching are basic features that an API must possess to serve data to the client application efficiently.

Sorting

Sorting allows you to order the results by any field, in ascending or descending order. Ascending and descending mean for alphabetical, numerical, and date-based responses. Sorting is an important feature for any API endpoint that returns a lot of data.

Ascending Order

Descending Order

1. Single Column Sort

If you only need to sort one column at a time, you could put the column name in sort_by and the sort direction in order.

Example formats

• GET /users?sort_by=first_name&order=asc
• GET /users?sort_by=first_name&order=desc

SELECT * FROM users ORDER BY first_name ASC

SELECT * FROM users ORDER BY first_name DESC

2. Multiple Column Sort

If the ability to sort multiple columns is required, you could comma-separate each column:order pair and put it in one sort parameter. This could also be used for a single column if you prefer the syntax.

Example formats

• GET /users?sort=first_name:asc,age:desc

SELECT * FROM users
ORDER BY first_name ASC, age DESC

Filtering

In filtering URL parameters is the easiest way to add basic filtering to REST APIs.  There are several ways to handle it. Some APIs will use a POST and pass all the data in the body of the request for searching. But a GET is preferable.

Example format

• GET users?filter={“first_name”:[“jhon”,”sam”],”age”:[20,25,30]}

1. String (exact)

Search by a single column.

Example format

• GET /users?first_name=Sam

SELECT * FROM users
WHERE first_name = 'Sam'

2. String (exact, multiple)

Multiple options for a single column can be handled in different ways.

Example format

• GET /users?first_name=Tina,Sam
• GET /users?first_name=Tina&first_name=Sam
• GET /users?first_name[]=Tania&first_name[]=Joe

SELECT * FROM users
WHERE first_name IN ('Tina', 'Sam')

3. Number

Exact number search on a column.

Example format

• GET /users?age=30

SELECT * FROM users
WHERE age = 31

4. Number – greater than

use gt: to handle greater than and gte: greater than or equal

Example format

• GET /users?age=gt:31

SELECT * FROM users
WHERE age > 31

5. Number – less than

use lt: to handle less than and lte: less than or equal

Example format

• GET /users?age=lt:31

SELECT * FROM users
WHERE age < 31

6. Number – Range

Range between two number values, using [and] in between them could be one option. Depending on options like greater than and greater than or equal, or other options.

Example format

• GET /users?age=gt:22[and]lt:30

SELECT * FROM users
WHERE age > 22 AND age < 30

7. Number – Date

If you need a range between two dates, you can use start and end, or since and to.

Example format

• GET /users?start=01-01-2000&end=09-09-2022

SELECT * FROM users
WHERE created_at BETWEEN '01-01-2000' AND '09-09-2022'

Searching

When full text search is used as a mechanism of retrieving resource instances for a specific type of resource, it can be exposed on the API as a query parameter on the resource’s endpoint. Here, ?q represents the query. Search queries should be passed straight to the search engine and API output should be in the same format as a normal list result.

Example

• GET /tickets?q=return&state=open&sort=-priority,created_at

Search REST API With The Query Text Parameter

A string that contains the text for the search query. The Search REST API service supports both HTTP POST and HTTP GET requests.

Endpoints