Querying JSON Relational Duality Views via REST APIs

You have an array of JSON objects coming back from a REST API, that looks like this –

Because my DV has been REST Enabled (same process for any view or table), I can simply GET the data using the ords/hr/empsplusboss_dv/ endpoint.

How can we just get employees whose boss’ first name is Steven?

While it might be obvious to solve this in SQL, I want you to see how easy it is to do via REST and using our built-in filtering feature.

For this post, I will assume you know:

• 23ai is our latest version of Oracle Database
• one of it’s major features is JSON Relational Duality Views
• ORDS provides REST API support via AutoREST
• you can easily build them via SQL Developer extension for VS Code

Our Duality View

I’m going to use the same example I’ve been tinkering with lately, a DV with a self-join on EMPLOYEES. This DV provides an array of employees objects, with their manager’s name attached.

When I was building this in VS Code, it looked a bit like this –

GET all the employees, and their boss via the DV

It’s good to start at ‘the root,’ or by doing a GET on the base object, like so –

But, I only want the employees whose boss’ first name is ‘Steven.’ How do I do that? It’s very similar to what I’ve shown multiple times before, and have even called it ‘my very favorite ORDS feature!‘

If we were working with a relational object like a TABLE or VIEW, for example the EMPLOYEES table, and I didn’t want all employees, I can simply –

GET ords/employees/?q={“job_id”:{“$like”:”%CLERK”},”salary”:{“$gt”:2899}}

Or in english/SQL,

 SELECT *
   FROM EMPLOYEES
  WHERE JOB_ID LIKE '%CLERK'
        and SALARY > 2899

But we’re working with JSON objects now, and they can be complex, having arrays or objects, nested objects, etc. So we need a more powerful query language. But no worries, we have one!

Duality Views and filtering is a bit different…

{
  "_id": 103,
  "firstName": "Alexander",
  "lastName": "James",
  "compensation": {
    "sal": 9000,
    "comm": null
  },
  "boss": {
    "boss_id": 102,
    "boss_name": {
      "firstName": "Lex",
      "lastName": "Garcia"
    }
  }
}

We have a object boss, that’s comprised of simple JSON value pair, followed by another object, boss_name.

To query this we will need to use the super-simple ‘Dot Notation’ that’s provided in the Oracle Database.

This line in the ORDS AutoREST docs confirms this, see –

Ok, so what is the ‘SODA extended QBE syntax?”

You’ll find the Docs for that, with examples, here.

I’ve blogged on this before, using movies stored in a SODA collection.

Putting it all together now

We want to do an equality filter on boss.boss_name.first_Name from our Duality View, these are easy to build, you just ‘walk the tree,’ more or less.

So let’s GET our employees!

GET ords/hr/empsplusboss_dv/?q={“boss.boss_name.firstName”:{“$eq”:”Steven”}}

But wait, this is boring, how about something more likely to be used in the real world?

I like to start with the boring and simple, and scale out from there. And, I thought it was cool that you could use a self-referential table join to construct JSON objects with Duality Views.

But, I will award a point, touche, for the astute observation fair reader.

I have a STUDENT_SCHEDULE object via a Duality View…which looks like this –

And when I do GET on this REST Enabled Duality View, the data comes back like so –

The Duality View is composed of data sourced from multiple tables, and some of those tables have JSON columns.

I only want to see student schedules that have the MATH_01 course included..

GET …ords/schools/student_schedule/?q={“schedule.courseName”:{“$eq”:”MATH_01″}}

More examples for using REST APIs with Duality Views

• Blog
• Tech Brief