Create a view from a JSON Collection and REST-enable it with ORDS

by

The DBMS_CLOUD PL/SQL Package

You can use this PL/SQL procedure (in the DBMS_CLOUD package) along with the file_uri_list URL (seen in the code below) to create and then add JSON documents to a JSON Collection (good info on JSON Collections in the Oracle database).

In this example, we call this collection Movie_Collection. 

-- create and load movie json collection from a public bucket on object storage

begin
dbms_cloud.copy_collection (
    collection_name => 'MOVIE_COLLECTION',file_uri_list   => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/c4u04/b/moviestream_gold/o/movie/movies.json',
    format => '{ignoreblanklines:true}');
end;
/
👆🏻 This is the code I used to create, copy, and ingest this collection into my Autonomous Database.
README: As far as I know, the link above (the one in the code example) will remain stable for the foreseeable future. I'm pretty sure we use it in one of many of our LiveLabs. A lot of what I'm covering here is actually in Task 7 of this LiveLab.
Notice the “PL/SQL procedure successfully completed.” message.

Create a view

With that JSON collection in place, I can then create (aka I’ll continue stealing this code from the same LiveLab) a View of it using the following SQL code:

/* Create a view over the collection to make queries easy */

create or replace view movie as
select
    json_value(json_document, '$.movie_id' returning number) as movie_id,
    json_value(json_document, '$.title') as title,
    json_value(json_document, '$.budget' returning number) as budget,
    json_value(json_document, '$.list_price' returning number) as list_price,
    json_value(json_document, '$.gross' returning number) as gross,
    json_query(json_document, '$.genre' returning varchar2(400)) as genre,
    json_value(json_document, '$.sku' returning varchar2(30)) as sku,
    json_value(json_document, '$.year' returning number) as year,
    json_value(json_document, '$.opening_date' returning date) as opening_date,
    json_value(json_document, '$.views' returning number) as views,
    json_query(json_document, '$.cast' returning varchar2(4000)) as cast,
    json_query(json_document, '$.crew' returning varchar2(4000)) as crew,
    json_query(json_document, '$.studio' returning varchar2(4000)) as studio,
    json_value(json_document, '$.main_subject' returning varchar2(400)) as main_subject,
    json_query(json_document, '$.awards' returning varchar2(4000)) as awards,
    json_query(json_document, '$.nominations' returning varchar2(4000)) as nominations,
    json_value(json_document, '$.runtime' returning number) as runtime,
    json_value(json_document, '$.summary' returning varchar2(10000)) as summary
from movie_collection
;

Here is what the code looks like in the SQL Worksheet (a part of Database Actions).

Accomplished in the SQL Worksheet.

With that View created, you could go one step further and query with even more specific SQL. In this case, I’ll query the View but exclude any entries where a movie cast does not exist:

Select
title,
year, 
gross, 
cast
from movie 
Where cast is not null 
Order By 3 DESC nulls last
Fetch first 10 rows only;

Here is the SQL, with the Script Output below:

Notice the output of the executed SQL.

ORDSify it®

With ORDS, we can REST-enable pretty much any database object we want.

I have objects, Greg. Can you REST-enable me?

But after spending a few minutes with this collection, I found the MOVIE View to be the easiest, most sensible object to highlight. It’s a straightforward process, with primarily right-mouse clicks.

From the Navigator Panel, select Views from the list of available database objects.

From the Navigator Tab, select Views from the drop-down menu.

Then, right-click on the Movie View, and select REST, then Enable.

Right-click on the Movie View and select REST then Enable.

A slider will appear, for this example, I’ll keep everything default and click Enable (no authentication either, I’m being lazy).

This slider will appear; since you are auto-REST enabling, you can accept the default settings and click Enable.

A Confirmation notification will appear in the upper right-hand corner of the browser 👍🏻.

You’ll know it worked because you’ll see this confirmation message appear.

Navigate back to the Movie View, and select the cURL command option. Twasn’t there before, tis now!

Go back to the Movie view and select the [new] cURL command option.

Select the GET ALL endpoint, and copy the URI. JUST the URI portion!

From the list of available auto-magically created REST endpoints, copy the GET ALL endpoint.

Open a new browser tab, or window, and navigate to the URI. You’ll see everything in the Movie View now!

That endpoint (URI) in the browser looks like this 👆🏻

Why a view?

Yeah, good question. There is probably a performance improvement with using Views. Based on what I’m finding, I can’t definitively say, but they require zero storage, so that’s at least a savings on some resource somewhere. Additionally, I think the customization is pretty compelling, too. What do I mean by that? Well, allow me to elucidate:

Views can provide a different representation (such as subsets or supersets) of the data that resides within other tables and views. Views are very powerful because they allow you to tailor the presentation of data to different types of users.

Oracle Database Administrator’s Guide Release 23

In this case, the Movie View returns everything found in the collection. I can subset this even further though; by taking that SQL query and REST-enabling it, too. I will, but in a future post.

For now, I’ve left you with an easy way to REST-enable a View (In this case, based on a JSON Collection) that resides in your Autonomous Database.

If you want to try the LiveLab (which you should, as it’s easy and VERY informative), go here. You’ll need an Always Free OCI account, too (so you can provision an Autonomous Database). You can sign up here.

Oh, and we are entering into the season of Cloud World 2024, so mark your calendars 🤪!

That’s all for now 😘.

Follow

And don’t forget to follow, like, subscribe, share, taunt, troll, or stalk me!

Leave a Comment