Python or Filtering in Queries: sorting JSON responses from an Oracle Database

In a recent post, I discussed taking the JSON response from my REST-enabled database table (which looked like this):

And sorting those cities after I performed a GET request (using some python code). However, after a recent discussion and a suggestion from thatjeffsmith, I think I should share another way you can sort this data from the source.

Background

If you recall, the portion of my python code that:

1. Performs a GET request
2. Extracts the cities to a list, and
3. Sorts them in ascending order,

…looks like this:

def city_choices_python(): 
    import requests
    import json 
    city_list = []

    URL = 'https://gf641ea24ecc468-dbmcdeebyface.adb.us-ashburn-1.oraclecloudapps.com/ords/pythondev/cities/'
   
    response = requests.get(URL)
    r = json.loads(response.text)
    for nestedArr in r['items']:
        city_list.append(nestedArr['city'])
    return sorted(city_list)

As you can see, in this version the URI provided by ORDS remains untouched. I’m just using that simple “sorted(city_list)” function to return a list of sorted cities in ascending order (FYI: its the last line in this code block).

Filtering in queries

This approach worked fine in my case, however I am now…enlightened…

Meaning, I am now aware that I have other options. It hadn’t really occurred to me that I could filter in queries at the time of the get request.

Filtering is the process of limiting a collection resource by using a per-request dynamic filter definition across multiple page resources, where each page contains a subset of items found in the complete collection. Filtering enables efficient traversal of large collections.

ORDS Docs

What I like about this is that filtering in queries doesn’t require any changes to the the original URI. Meaning, you wouldn’t need to go back into (in my case I’m using…) Database Actions to modify anything. You simply include filters along with the URI.

In my case it would look like something like this:

https://gf641ea24ecc468-dbmcdeebyface.adb.us-ashburn-1.oraclecloudapps.com/ords/pythondev/cities/?q={"$orderby":{"city":"asc"}}

Time-out!

This will screw with your head, so listen up. Quotation marks (e.g. ”
“) can’t be transmitted over the internet, so they need to be converted to the ASCII character-set.

Quotations in HTML-5 will look like “%22” in a URL. So if you take that above URL copy it, and then paste it again, it may end up looking like this in your browser’s address bar:

https://gf641ea24ecc468-dbmcdeebyface.adb.us-ashburn-1.oraclecloudapps.com/ords/pythondev/cities/?q={%22$orderby%22:{%22city%22:%22asc%22}}

Where you’ll start out with UTF-8 encoding (quotation marks), at some point you’ll see ASCII encoding (which is a combination of the % and hexadecimal encoding).

I only mention this because I noticed when I took the updated URL and copied it into my VS Code editor it ended up looking like this:

def city_choices_ords(): 
    import requests
    import json 
    city_list = []

    URL = 'https://gf641ea24ecc468-dbmcdeebyface.adb.us-ashburn-1.oraclecloudapps.com/ords/pythondev/cities/?q={%22$orderby%22:{%22city%22:%22asc%22}}'

    response = requests.get(URL)
    r = json.loads(response.text)
    for nestedArr in r['items']:
        city_list.append(nestedArr['city'])
    return(city_list) 

Don’t be alarmed, everything still works. But the query will look like this:

?q={%22$orderby%22:{%22city%22:%22asc%22}}

What is this saying?

The query above, requests that the cities table be ordered (“orderby”) in ascending (“asc”) order according to the “city” column. You can see this in action if you point your browser to it. It will display like this:

Ordering isn’t the only type of filtering you can do, the combinations are pretty much infinite…

…Take a look.

Comparing performance

One last thing…I conducted a very crude A/B performance test, comparing the two code versions.

In short, there was no appreciable difference. I should remind that I’m working with a very small database object (a table with 2 columns and 18 cities). So if there is a difference one way or another, that difference is negligible.

I still recorded the test for fun. And you’ll see across the ten requests (five for each version), the elapsed times were essentially the same. I know this isn’t statistically sound, but this was done to satisfy my own curiosity. Our documentation actually states that filtering is advantageous for traversing large objects, so this is probably a terrible example. And as requests scale, I have to image filtering in queries would begin to pull ahead.

Nonetheless, see the results for yourself:

The End

Now you’ve got a couple options for sorting when working with a REST-enabled table (or other object). The nice thing is that setting up these endpoints with ORDS can still be accomplished with simple mouse clicks.

Also, remember, sorting isn’t the only thing that can be accomplished when filtering in queries. There are TONs of other capabilities, too many to list here. But I would bookmark this page so you can reference later on.

I don’t want to say you can filter everything, but it sure looks like it. And for a python newbie like myself, this might be a better approach moving forward.

Funny story, the day I added that “sort()” function in my code was literally the same day (earlier that morning in fact) that I watched a “python tips” video, where sorting was mentioned. It was pure luck.

Had it not been for that video, I would have been stuck for at least another day trying to figure out how to sort my list.

Hey, at least now I know I won’t be restricted to what I can filter based on the python functions I do or don’t know. I can’t be the only one….right?

Follow

Be sure to follow along for more fun:

• Twitter
• Mastodon
• YouTube
• GitHub
• Discord
• LinkedIn
• Stack Overflow
• Medium
• Tealfeed