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!
thatjeffsmith
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.

3 Comments

  1. Avatar

    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!

  2. Avatar

    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

RSS
Follow by Email
LinkedIn
Share