Mostly Making oracle easy, mostly:
Search for:
ThatJeffSmith
In ORDS

Some Query Filtering Examples in ORDS

2 Mins Read
I don’t want all my beers, just the ones I rated with a score of 5

I said on Twitter yesterday that this was one of my favorite features…

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:

Maybe we want to add some more WHERE clauses or an ORDER BY?

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.

A quadruple, tripel play!

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"}}
Still my Tripels and 9.5+% items, but now also sorted.

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!

Note that DATES in ORDS are always treated in Zulu Time!
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

11 Comments

  1. Harry Reply

    Hi,
    Is it possible to use the opposite ? I mean “NOT LIKE” ?
    I tried couple of things but always receiving bad request , seems like this operator is not supported ?
    Thanks.

  3. Satish Reply

    “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

    • thatjeffsmith Post Author

      GET /ords/hr/my_emps_api/emps/search/:name

      in the SQL behind your api
      select * from employees
      where upper(last_name) = upper(:name);

  4. Satish Reply

    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

    • thatjeffsmith Post Author

      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.

  5. Santo Reply

    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!

    • thatjeffsmith Post Author

      url encoding and un-encoding…is always fun. I find myself using online translators, a lot.

  6. Santo Reply

    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!

Write A Comment