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!
7 Comments
“You’d need to throw an UPPER or LOWER function call on the predicate and there’s now way to do that…unless you built it into the API code block itself…which is what I would do if I was going to give someone an endpoint for searches.”
I did not get this comment. Are you suggesting we have a way to specify UPPER or LOWER in the API call like ?q={“UPPER(beer_type)”:{“$like”:”%25Tripel%25″}} or something else?
Thanks
Satish
GET /ords/hr/my_emps_api/emps/search/:namein the SQL behind your api
select * from employeeswhere upper(last_name) = upper(:name);
Hi Jeff,
How can we make the filter Case INSENSITIVE ?
eg: ?q={“beer_type”:{“$like”:”%25Tripel%25″}}
Can we make it match tripel , TRIPEL etc.. by altering either the FilterObject or changing something from ORDS end ?
Thanks
You’d need to throw an UPPER or LOWER function call on the predicate and there’s now way to do that…unless you built it into the API code block itself…which is what I would do if I was going to give someone an endpoint for searches.
You could at the table or db level make strings equal regardless of case or even accented characters, but that sounds way too much overkill to me.
Sorry Jeff, nevermind: I just found out that I needed to replace all curly braces with relevant URL Encoded Characters, so given your example, with this kind of query string:
?q={“beer_type”:{“$like”:”%25Tripel%25″}}
I just used this istead:
?q=%7B”beer_type”:%7B”$like”:”%25Tripel%25″%7D%7D
and it worked! Thank you very much!
url encoding and un-encoding…is always fun. I find myself using online translators, a lot.
Hi Jeff!
This is extremely interesting, do I have to enable anything on the database to get this working? I have a very simple ORDS service with a GET handler but no matter how I format the query string it keeps on giving me Bad Request errors. Do you have any advice? Thanks!