I’ve heard quite a bit about this killer, new feature in Oracle Application Express (APEX) version 19.2..so I wanted to give it a go, and share it with you folks.
If you’re not already using APEX..you SHOULD be. And if you want an official overview and announcement of this feature, I recommend this blog post from Carsten.
So what is Faceted Search?
Have you shopped for plane tickets lately?
The search criteria in the red box, those are ‘facets.’
Now, how cool would it be if you could browse your data with a similar approach? Without writing any code?
APEX and Low Code for the win
I’ll show you what I made happen first, then I’ll show you how I got it next. Here’s my App’s ‘Search’ page – it has Facets I can use to ‘narrow down’ my data as i explore it, just like when I was searching Flights. I have NOT provided any SQL behind that part of the application – APEX is doing that for me.
Side note – Did you know that Oracle has a new motto/mantra? I think it gets to the root of what tools like APEX and SQL Developer are all about.
Our mission is to help people see data in new ways,— Larry Ellison
discover insights, unlock endless possibilities.
This faceted search feature really does allow me to see my data in new ways.
How it works
Please take this with a grain of salt, as I’m describing the feature as Mike Hichwa explained it to me, so in layman’s terms – there’s some rules coded into APEX to look for certain types of columns (number fields for example) and to discount or avoid other types of columns (foreign keys and primary keys). When you use the New App wizard, and include a Faceted Search page, APEX will automatically look for and add some Facets to the page based on these rules. This way you can see it working out-of-the-box.
THAT is the ‘no code’ part.
Now, of course I can go in and change/add/remove the Facets as I’d like – and I will probably need to make my app ‘PERFECT’ for my end users.
Adding a Facet
I want to look at the kinds of beer I’ve been drinking…maybe based on the Month in which I’m enjoying them. So to get started, the query I have power the page is going to have a TO_CHAR(DATE_FIELD, ‘MON’) in the SELECT. I can then tie my Facet to this query column.
Now, you CAN have a DATE or TIMESTAMP field as a Facet, but APEX won’t add those by default, and when you DO add them, you’ll be limited to a RANGE facet type. I wanted check boxes for Month, hence my query to convert the DATE to a MON string.
Adding a Facet is extremely easy. Just ‘right click.’
Now I just need to fill in the details, but basically:
- the COLUMN…obviously
- the type of facet – you want check boxes, radio, range…
- and for those facets, do you maybe also want a count of rows
So for my MONTH facet, I can define it thus:
Wait, wait…start over. How did you build this?
Easily. I started with some CSV data.
Check over the data preview and new table column definitions…
Cross your fingers…
Click ‘Create Application,’ and then you’re going to pick the Pages you want included.
Generate and run the program, and voila!
Removing and adding a facet to my search page
Remember, APEX is super accessible.
It’s included in your Autonomous Database Cloud service – which is also now available FOR FREE.
And it’s available for any Oracle Database running pretty much anywhere. Go to https://apex.oracle.com/en/ to get started.