Oracle REST APIs & Query Parameter Examples using EMPLOYEES

This post will be using our tried, true, and boring HR.EMPLOYEES table for all of the examples. If I had a need to get a list of EMPLOYEES whose:

• JOB_ID ends with ‘CLERK’ AND
• SALARY is greater than 2899 OR
• EMPLOYEE_ID is 114.

This would be accomplished via SQL using something like this –

SELECT *
  FROM employees
 WHERE JOB_ID LIKE '%CLERK'
       AND salary > 2899
       OR EMPLOYEE_ID = 114
 ORDER BY salary DESC

And the result could look something like this:

But Jeff, I thought this was about my REST APIs and how to filter their results?

Well, if your Oracle REST API is backed by SQL, then we can do a:

• GET /schema/uri_template/ — to get all items in that collection, i.e. rows from SQL
• GET /schema/uri_template/?=q{“column”:{“$eq”:”value”}} to get all items in that collection where a COLUMN in the query results or TABLE is equal to ‘value’

I talk about this feature here, using some fun BEER examples from my UNTAPPD personal data. And this feature is of course, documented (Docs.)

But I got a request from a reader for a deeper dive on this topic, with more complicated examples, using the HR schema, so let’s do that now!

The REST API

We can either AUTOREST enable the EMPLOYEES table and have the GET employees/ generated for us, or we could write a custom module/template/handler combo to have the same endpoint return the list of employees with OUR SQL.

Let’s do the latter.

BEGIN
 ORDS_METADATA.ORDS.create_service(
      p_module_name        => 'FILTERING',
      p_base_path          => 'filters',
      p_pattern            => 'employees/',
      p_method             => 'GET',
      p_source_type        => 'json/collection',
      p_source             => 'select * from employees',
      p_status             => 'PUBLISHED',    
      p_module_comments    => 'examples showing how to filter SQL based GET requests',
      p_template_comments  => 'our EMPLOYEES, as stored in EMPLOYEES',
      p_handler_comments   => 'GET the EMPLOYEES');
 COMMIT;
END;
/

GET just the ‘CLERKS’

So instead of getting all the employees, I just want the employees whose JOB title ends in ‘CLERK.’ We’ll use a wildcard with the LIKE predicate.

/filters/employees/?q={“job_id”:{“$like”:”%CLERK”}}

And calling that…

GET just the clerks who make at least 2899 (SALARY)

?q={“job_id”:{“$like”:”%CLERK”},”salary”:{“$gt”:2899}}

We’re building a JSON document, basically, in our URI query parameter.

So if we look at it this way…

{
	"job_id": {
		"$like": "%CLERK"
	},
	"salary": {
		"$gt": 2899
	}
}

So we have two columns, JOB_ID and SALARY, as attributes, whose values are more JSON with the query predicate function and value.

GET CLERKS who make at least 2899 and hired after 1997

?q={“job_id”:{“$like”: “%CLERK”},”salary”:{“$gt”: 2899},”hire_date”:{“$gt”:{“$date”:”1998-12-31T12:59:59Z”}}}

This one gets a bit trickier. We’re working with a DATE in the database, so we need to tell ORDS the value coming in IS a date as there is no DATE data type defined in JSON.

If we break down that query string, it looks like this –

{
	"job_id": {
		"$like": "%CLERK"
	},
	"salary": {
		"$gt": 2899
	},
	"hire_date": {
		"$gt": {
			"$date": "1997-12-31T12:59:59Z"
		}
	}
}

And if we call that –

When working with DATES in your REST API query parameters, pay particular attention to this section in the docs –

GET (CLERKS, salary 2899+ hired after 1997) OR EMP 114

?q={“$or”:[{“employee_id”:{“$eq”:114}},{“$and”:[{“job_id”:{“$like”:”%CLERK”}},{“salary”:{“$gt”:2899}},{“hire_date”:{“$gt”:{“$date”:”1997-12-31T12:59:59Z”}}}]}]}

I did not enjoy writing this one, and I had some help from a co-worker, thanks Rene!

We’re introducing two new concepts here, $or & $and.

The $or says we have either A (purple) or B (teal) that needs satisfied. And for B, we have an array of conditions that must be ALL met via the AND.

The B items need wrapped as an array under $and.

The entire set of conditions is wrapped as the $or.

Can calling it I get 10 items back, employee ID 114, and 9 CLERKs of a certain salary and hired after a certain date.

All of these requests are using a single API.

A single GET API defined as simply ‘SELECT * FROM EMPLOYEES’ is being used to satisfy all of these requests for different groups of employees. So as the REST API developer, you just have to work on the ‘superset’, and then your API consumers can filter out what they’d like, with no code changes from you!

Don’t forget about LIMIT

If you had 25,000 employees that met all these conditions, maybe you’d want to SORT on salary descending, and then LIMIT your request to the first 10 employees.

That’s easy as well.