If this topic sounds familiar, it should! We’ve talked about it a few times here already, but it’s a critical feature of ORDS and it merits multiple posts. Today I wanted to shine a quick light on:
- In string operator ($instr)
- LIKE operator ($like)
Let’s start with some sample data.
My STRAVA table has 700+ records in it, and while I’m somewhat consistent with the naming, I’m not perfect.
I’m going to show how to use these query parameters on REST APIs for this table, both using AutoREST and a Query Backed Module/GET Handler.
You can find these features documented here.
INSTR – Searches string for a specific substring
My GET Handler does exactly what you see above. It pulls everything. But maybe I only want to see activities where I’ve been rowing, or I went for a row?
/ords/admin/strava/activities/?q={"activity_type":{"$instr":"RoW"}}
I’m pretty consistent on the activity_type, but not so much on the activity description. Maybe let’s look for all the 10k stuff I’ve done?
Hmm, can I actually look for that specific emoji? Yup!
ords/admin/strava/activities/?q={"description":{"$instr":"🚣"}}
Whilst the case of the text doesn’t matter, diacritics remain distinct. So e <> é <> è <> ē.
LIKE – Allows wildcards to perform pattern matching
I may want to be a bit more selective on my searches. I have a specifc pattern I want to find. That’s where our old friend LIKE becomes useful.
Since it’s Monday morning, let’s switch over to my other favorite hobby and instead of a SQL based REST API, we’ll look at a AutoREST enabled table.
These types of predicates are extremely common and make for fun indexing schemes.
Let’s look for ‘Imperial’ anywhere in the BEER_TYPE column.
ords/admin/brews/?q={"beer_type":{"$like":"%Imperial%"}}
It’s similar to what we already look at with the $instr examples except that it’s case-sensitive, AND I pick the patterns. So ‘imperial’ <> ‘Imperial’ – and I get to pick where the wild-cards go. So ‘Imperial%’ would not pick up on ‘Double Imperial…’
Let’s get a bit more creative.
ords/admin/brews/?q={"beer_name":{"$like":"%Coffee%Choc%"}}
Happy Monday everyone 🙂
If you’re ever in North Carolina this time of year, and you enjoy tasting regional fare, this product out of Winston-Salem is very good – in small quantities.
9 Comments
Hi Jeff,
is there interest in supporting oracle text predicates in query filters?
There is now, you’re the first to ask for it!
In my app I have one search field and it’s used to filter on 5 fields with q parameter of “autorested” table. One of the fields is a date and seeing it in json as string made me hope to be able to filter as string.
Funny thing is that some value is working but it’s hard to understand what is matching and why
there is no concept of a DATE in JSON…To represent dates in JavaScript, JSON uses ISO 8601 string format to encode dates as a string.
The values are stored as DATEs in the database, and your query parameters are transformed into WHERE clauses, so they need to be valid for the data type in play.
“query parameters are transformed into WHERE clauses” seems OK, but I have different results on sqldeveloper and in app calling the REST API.
For example in sqldeveloper I can select with
SELECT * FROM table where instr(dataora, ‘-02-‘)0
SELECT * FROM table where dataora like ‘%-01-%’
and get the records with months 02 and 01
but when queried with REST API with
q={“dataora”: {“$instr”: “-01-“}}
q={“dataora”: {“$like”: “%-02-%”}}
the result is always
{
“items”: [],
“hasMore”: false,
“limit”: 25,
“offset”: 0,
“count”: 0,
“links”: [
{
“rel”: “self”,
…cut
Here’s the funny thing:
calling the same autorest get API with
q={“dataora”: {“$instr”: “18”}}
I get the dates with day 18
and with
{“dataora”: {“$instr”: “20”}}
I get the dates with day 20
like the only filter working is on the day part of the date,
same query with year is giving 0 results
Use the proper date functions when working with dates, like between. The way you’re doing it, you’re relying on implicit text conversion and that’s not reliable, esp when date formats change, which is probably what you’re running into.
You are right
{“dataora”: {“$instr”: “jan”}}
is filtering all January dates.
Now it’s clear, so autorest is not enough anymore ,I’ll create a GET module.
Thanks Jeff
How filtering date fields works? I tried $instr and $like but no success
Dates aren’t strings..what do you want?