Some Query Filtering Examples in ORDS

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

…WHERE RATING_SCORE = 5 pic.twitter.com/bWD7LzeZkQ

— Jeff Smith 🥃 ☜ (@thatjeffsmith) September 2, 2019

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!