
I said on Twitter yesterday that this was one of my favorite features…
One of my favorite @OracleREST features, adding query predicates to existing RESTful Service calls
— Jeff Smith 🥃 ☜ (@thatjeffsmith) September 2, 2019
…WHERE RATING_SCORE = 5 pic.twitter.com/bWD7LzeZkQ
So I thought I’d share a few concrete examples here, and clarify something mentioned in the Docs.
3.2.5.2 Filtering in Queries
(Docs) – ” This section describes and provides examples of filtering in queries against REST-enabled tables and views.”
This is true, but it’s ALSO true that this feature is available for SQL based GET Handlers.
Given this REST Service Handler definition:

I can use the same features for AUTO REST enabled TABLES for filtering/ordering my collection items to get JUST the data I want on this RESTful Service. Let’s walk through some examples!
WHERE BEER_TYPE LIKE ‘%Triple%’
This translates to this query string –
?q={"beer_type":{"$like":"%25Tripel%25"}}
The %25 is to URL encode the % character, and making this request, gives me what I’m looking for.

WHERE BEER_TYPE LIKE ‘%TRIPLE%’ AND BEER_ABV > 9.5
?q={"beer_type":{"$like":"%25Tripel%25"},"beer_abv":{"$gt":9.5}}

WHERE BEER_TYPE LIKE ‘%TRIPLE%’ AND BEER_ABV > 9.5
ORDER BY BEER_NAME DESC
?q={"beer_type":{"$like":"%25Tripel%25"},"beer_abv":{"$gt":9.5},"$orderby":{"beer_name":"desc"}}

I’m not that good with {JSON}
I don’t want you to think I can just type out these query strings in my browser URL bar…I mean, I can, but when they get nested/complex, I go to something like JSONLint and validate my requests there, especially when ORDS starts giving me 400 BAD REQUEST type errors.
You can also specify the data for a given SCN or Timestamp (Flashback!)
Fearful your data is changing as you page through a particularly long record set? Throw on a “$asof” with a SCN# or a DATE!
