You have an array of JSON objects coming back from a REST API, that looks like this –
![](https://www.thatjeffsmith.com/wp-content/uploads/2024/08/duality-view-boss1.png)
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 –
![](https://www.thatjeffsmith.com/wp-content/uploads/2024/08/image-6-1024x665.png)
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 –
![](https://www.thatjeffsmith.com/wp-content/uploads/2024/08/image-7-1024x888.png)
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
![](https://www.thatjeffsmith.com/wp-content/uploads/2023/03/image-18-882x1024.png)
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 –
![](https://www.thatjeffsmith.com/wp-content/uploads/2024/08/image-8.png)
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.
![](https://www.thatjeffsmith.com/wp-content/uploads/2024/08/dv-boss-bossname-1024x888.png)
So let’s GET our employees!
GET ords/hr/empsplusboss_dv/?q={“boss.boss_name.firstName”:{“$eq”:”Steven”}}
![](https://www.thatjeffsmith.com/wp-content/uploads/2024/08/image-10-1024x620.png)
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 –
![](https://www.thatjeffsmith.com/wp-content/uploads/2024/08/image-12-1024x699.png)
And when I do GET on this REST Enabled Duality View, the data comes back like so –
![](https://www.thatjeffsmith.com/wp-content/uploads/2024/08/image-13-1024x734.png)
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″}}
![](https://www.thatjeffsmith.com/wp-content/uploads/2024/08/image-14-1024x920.png)
2 Comments
…..We want to do an equality filter on boss.boss_name.first_Name from our Duality View…..
the path expression “boss.boss_name.first_Name” is Incorrect.
instead it should be “boss.boss_name.firstName” as per the sample json payload listed there…
Thanks, it’s fixed now 🙂