Going beyond ORMs with JSON Relational Duality Views

Going beyond ORMs

1. Oracle Database Group
Oracle Database 23ai
Andres Almiray
Senior Principal Product Manager
Database Group
March 2025
Going Beyond ORMS

2. Andres Almiray
Senior Principal Product Manager
2

3. Copyright © 2024, Oracle and/or its affiliates
Relational: The good and the bad
Documents (JSON): The good and the bad
Can we combine the good from both?
JSON Relational Duality Views
Demo
Agenda
3

5. Oracle Converged Database
Blockchain tables
In-Database Machine Learning
Spatial
Relational
Columnar analytics
Property Graph/RDFNative Binary JSON/XML
Text Data
Transactional Event Queue
Vector embeddings
Internet of Things
External Data

6. Copyright © 2024, Oracle and/or its affiliates
Oracle Converged Database
6
REST/GraphQL
APEXProgramming Languages
Java, Python, JavaScript, .Net, PL/SQL, C/C++, etc.
SQLcl/SQL Developer

7. Copyright © 2024, Oracle and/or its affiliates
Simple Example: Conference
7
SessionAttendee Speaker
Entities

8. Copyright © 2024, Oracle and/or its affiliates
Simple Example: Conference
8
SessionAttendee Speaker
Relationships, Cardinalities
N 1N M

9. Copyright © 2024, Oracle and/or its affiliates
Simple Example: Conference, RELATIONAL
9
SessionAttendee Speaker
Tables
N 1N M
ATTENDEE
AID NAME
A1 Jill
A2 Sanjay
SESSION
SIDNAME ROOM SPID
S1JSON OSLO SP1
S2SQL TOKYO SP2
SPEAKER
SPIDNAME PHONE
SP1 Carla 650..
SP2 Pascal408...
ATT_SES_MAP
AID SID
A1 S1
A2 S2

10. Copyright © 2024, Oracle and/or its affiliates
Simple Example: Conference, RELATIONAL
10
SessionAttendee Speaker
N 1N M
ATTENDEE
AID NAME
A1 Jill
A2 Sanjay
SESSION
SIDNAME ROOM SPID
S1JSON OSLO SP1
S2SQL TOKYO SP2
SPEAKER
SPIDNAME PHONE
SP1 Carla 650..
SP2 Pascal408...
ATT_SES_MAP
AID SID
A1 S1
A2 S2
References, Links -> used for Joins

11. Copyright © 2024, Oracle and/or its affiliates
•No data duplication -> consistency
•Use case flexibility, SQL
• only select columns that are needed
• query can start at any table and include any other table (joins)
• example: Attendee Schedule, Speaker Schedule, Session Catalog, Session with most Attendees,...
•Relational model relies on algebra: Optimizer
Relational: the GOOD
11
SESSION
SIDNAME ROOM SPID
S1JSON OSLO SP1
S2SQL TOKYO SP2
SPEAKER
SPIDNAME PHONE
SP1 Carla 650..
SP2 Pascal408...
ATT_SES_MAP
AID SID
A1 S1
A2 S2
ATTENDEE
AID NAME
A1 Jill
A2 Sanjay

12. Copyright © 2024, Oracle and/or its affiliates
•Needs definition of all tables, columns, data types
•requires perfect upfront knowledge
•schema first, data later
•Hard to evolve, not schema-flexible
•'Normalization' breaks business objects into many tables
•SQL usually not integrated into programming language (SQL is a string or generated by
ORM)
Relational: the BAD
12
ATTENDEE
AID NAME
A1 Jill
A2 Carla
SESSION
SIDNAME ROOM SPID
S1JSON OSLO SP1
S2SQL TOKYO SP2
SPEAKER
SPIDNAME PHONE
SP1 Beda 650..
SP2 Zhen 408...
ATT_SES_MAP
AID SID
A1 S1
A2 S2

13. Developing apps using normalized tables is very flexible,
but it is not always easy for developers
App Dev Example — Conference Schedule
ATTENDEE
SPEAKER
SESSION
ATT_SESS_MAP
To build Jill’s schedule, the developer must run
database operations on each of the four tables
SQL
SQL
SQL
SQL
Copyright © 2024, Oracle and/or its affiliates13
SESSION SCHEDULE FOR JILL
(ACME INC)
Time 4:00 PM
Room B405
Teacher Beda
Time 2:00 PM
Room A102
Teacher Pascal
Hackolade Demo SQL for Dummies

14. Relational Data and Developers
Ideally, the developer wants to build Jill’s schedule
using a single simple database operation
Database
Operation
Copyright © 2024, Oracle and/or its affiliates14
SESSION SCHEDULE FOR JILL
(ACME INC)
Time 4:00 PM
Room B405
Teacher Beda
Time 2:00 PM
Room A102
Teacher Pascal
Hackolade Demo SQL for Dummies

15. Copyright © 2024, Oracle and/or its affiliates
JSON: Hierarchy 1 – Attendee Schedule
15
one object per Attendee
Session
Attendee
Speaker
{
"_id" : "3245",
"name" : "Jill",
"company" : "ACME Inc",

"schedule" : [
{

"code" : "DB12",
"session" : "SQL",

"time" : "14:00",
"room" : "A102",

"speaker" : "Adam"
},
{

"code" : "CODE3",
"session" : "NodeJs",

"time" : "16:00",
"room" : "R12",

"speaker" : "Claudia"
}
]
}

16. Copyright © 2024, Oracle and/or its affiliates
JSON: Hierarchy 2 – Session Catalog
16
one object per Session
{
"code" : "DB12",
"name". : "SQL",

"time" : "14:00",
"room" : "A102",

"speaker" : "Adam",
"numAtt" : 12,
"roomCap" : 60
}
{
"code" : "CODE2",
"name". : "NodeJS",

"time" : "16:00",
"room" : "R12",

"speaker" : "Claudia",
"numAtt" : 75,
"roomCap" : 75
}
Session
Attendee
Speaker

17. Copyright © 2024, Oracle and/or its affiliates
JSON: Hierarchy 3 – Speaker Schedule
17
one object per Speaker
{
"speakerId" : "S1",
"name". : "Adam",

"phone" : "650-392-000",
}
{
"speakerId" : "S2",
"name". : "Claudia",

"phone" : "[phone]",
}
Session
Speaker

18. Copyright © 2024, Oracle and/or its affiliates
•All information for the usecase in one object
•Usually retrieved by a simple 'get' operation from the app code (no SQL)
•Schema flexible
•data first, schema later
•Single hierarchy only possible for simple use cases
•Embedding causes duplication
•Harder to keep consistent and to optimize
JSON: the GOOD, the BAD
18

19. Copyright © 2024, Oracle and/or its affiliates19
Can't we normalize JSON

the same way as tables?

20. Copyright © 2024, Oracle and/or its affiliates
Document Database Normalization
20
{
"attendee : "S3245",
"name" : "Jill",

"schedule" :
[ {

"time" : "14:00",
"session" : "SQL++",
"room" : "A102",

"speaker" : "Adam"
},
…
]
}
SCHEDULE FOR: JILL
{
"attendee" : "S3245",
[ {"session" : "M201 }

… ]
}
{
"attendeeId" : "S3245",
"name" : "Jill",
"company" : "ACME
inc"
…}
The Schedule
document can be
changed to reference
a separate Attendee
document instead of
embedding it

21. Copyright © 2024, Oracle and/or its affiliates
Document Database Normalization
21
{
"attendee : "S3245",
"name" : "Jill",

"schedule" :
[ {

"time" : "14:00",
"session" : "SQL++",
"room" : "A102",

"speaker" : "Adam"
},
…
]
}
SCHEDULE FOR: JILL
{
"attendee" : "S3245",
[ {"session" : "M201 }

… ]
}
{
"attendeeId" : "S3245",
"name" : "Jill",
"company" : "ACME inc"
…}
{
"sessionId" : "M201",
"name" : "SQL++",
"teacher" : "T543"
…}
and reference a
separate Session
document

22. Copyright © 2024, Oracle and/or its affiliates
Document Database Normalization
22
{
"attendee : "S3245",
"name" : "Jill",

"schedule" :
[ {

"time" : "14:00",
"session" : "SQL++",
"room" : "A102",

"speaker" : "Adam"
},
…
]
}
SCHEDULE FOR: JILL
{
"attendee" : "S3245",
[ {"session" : "M201 }

… ]
}
{
"attendeeId" : "S3245",
"name" : "Jill",
"company" : "ACME inc"
…}
{
"sessionId" : "M201",
"name" : "SQL++",
"teacher" : "T543"
…}
{
”speakerId" :
"T543",
"name" : "Adam"
…}
which references a
separate Speaker
document

23. Copyright © 2024, Oracle and/or its affiliates23
When documents
are normalized 

their simplicity is lost

24. Document Database Fragmentation
Copyright © 2024, Oracle and/or its affiliates24
ATTENDEE
SPEAKER
SESSION
SCHEDULE
Normalizing documents
produces the worst of both
worlds
•The document structure now
mirrors the normalized table
schema
{
"attendeeId" : "S3245",
[ {"course" : "M201 }

… ]
}
{
”speakerId" :
"T543",
"name" : "Adam"
…}
{
"sessionId" : "M201",
"name" : "Math
201",
"speaker" : "T543"
…}
{
"atendeeId" : "S3245",
"name" : "Jill",
"company" : "ACME
Inc"
…}

25. Document Database Fragmentation
Copyright © 2024, Oracle and/or its affiliates25
ATTENDEE
SPEAKER
SESSION
SCHEDULE
{
"attendeeId" : "S3245",
[ {"course" : "M201 }

… ]
}
{
”speakerId" :
"T543",
"name" : "Adam"
…}
{
"sessionId" : "M201",
"name" : "Math
201",
"speaker" : "T543"
…}
{
"atendeeId" : "S3245",
"name" : "Jill",
"company" : "ACME
Inc"
…}
The simplicity of
documents at the
application level is lost
•Without gaining the power of
SQL and relational at the
database level
Performance suffers due
to reference chasing and
loss of shard locality
•Referential integrity must be
enforced by every app

26. Big Picture - Documents
Copyright © 2024, Oracle and/or its affiliates26
JSON
Benefits
Use Case Complexity
Documents are great 

for simple apps

27. Big Picture - Documents
Copyright © 2024, Oracle and/or its affiliates27
JSON
Benefits
Use Case Complexity
Become hazardous as
apps get more complex
Because of this,
many data experts
consider pure Document
Databases an anti-
pattern

28. Big Picture – Relational
Copyright © 2024, Oracle and/or its affiliates28
Benefits
Use Case Complexity
Relational
Relational is not as
easy for simple apps
Its power becomes
vital as app complexity
increases

29. Copyright © 2024, Oracle and/or its affiliates
Benefits
Use Case Complexity
JSON
Oracle Enables Developers to Deliver the Best of Both
With Oracle, developers
can already choose the
data format that
maximizes the benefits
for each use case
29
Relational

30. Copyright © 2024, Oracle and/or its affiliates30
This is great 

Can we do even better?

31. Copyright © 2024, Oracle and/or its affiliates31
Instead of choosing

Relational OR Documents
Can we get the benefits of 

Relational PLUS Documents?

32. Copyright © 2024, Oracle and/or its affiliates
Benefits
Use Case Complexity
JSON
Relational
Can We Get All the Benefits of Both, for Every Use Case?
PLUS
32
JSON PLUS
Relational
Relational
•Use Case Flexibility
•Queryability
•Consistency
•Space Efficiency
Document
•Easy mapping to language types
•Agile schema-less development
•Hierarchical data format
•Standard interchange format

33. Copyright © 2024, Oracle and/or its affiliates33
It’s here, we call it
JSON Document Relational Duality


34. JSON Document Relational Duality
Data is stored as rows 

in tables to provide the benefits of the
relational model and SQL access
Storage Format
Copyright © 2024, Oracle and/or its affiliates34
Rows can include JSON columns to store data
whose schema is dynamic or evolving
TABLE
Column 1 Column 2 Column 3
… … …
… … …
… … …
… … …
TABLE
Column 1 Column 2 Column 3
… … …
… … …
… … …
… … …
TABLE
Column 1 Column 2 Column 3
… … …
… … …
… … …
… … …

35. JSON Document Relational Duality
Data is stored as rows 

in tables to provide the benefits of the
relational model and SQL access
Storage Format
Copyright © 2024, Oracle and/or its affiliates35
Data can be accessed as JSON
documents 

to deliver the application simplicity of
documents
Access Formats
{
"label1" : "String
Anita",
"label2" : 5678
"label3" : ”Physics
201",
}
{
"label1" : ”Student
Jill",
"label2" : 5678
"label3" : ”Science
102",
}
{
"name1" : "String
Value1",
"name2" :
{
"name3" : "14:00",
"name4" : 1234
}
}
TABLE
Column 1 Column 2 Column 3
… … …
… … …
… … …
… … …
TABLE
Column 1 Column 2 Column 3
… … …
… … …
… … …
… … …
TABLE
Column 1 Column 2 Column 3
… … …
… … …
… … …
… … …

36. CREATE JSON DUALITY VIEW attendeeSchedule 

AS attendee
{
_id : aid
name : name

company : company
schedule : attendee_sessions
[ {
session @unnest 

{

code : sid



session : sname


time : time


room : room
speaker @unnest
{
speaker : sname

}
}
} ]
};
{
"_id" : "3245",
"name" : "Jill",
"company" : "ACME Inc",

"schedule" : [
{

"code" : "DB12",
"session" : "SQL",

"time" : "14:00",
"room" : "A102",

"speaker" : "Adam"
},
…
]
}
The structure of the view mirrors the structure of the
desired JSON, making it simple to define
Uses familiar
GraphQL syntax
SCHEDULE FOR: JILL

37. CREATE JSON DUALITY VIEW attendeeSchedule 

AS attendee
{
_id : aid
name : name

company : company
schedule : attendee_sessions
[ {
session 

{

code : sid



session : sname


time : time


room : room
speaker
{
speaker : sname

}
}
} ]
};
The view simply specifies the tables that contain the
data to include in the JSON document
SPEAKER
SESSION
ATTENDEE
ATTENDEE_SESSION

38. CREATE JSON DUALITY VIEW attendeeSchedule 

AS attendee
{
_id : aid
name : name

company : company
schedule : attendee_sessions
[ {
session 

{

code : sid



session : sname


time : time


room : room
speaker
{
speaker : sname

}
}
} ]
};
The view simply specifies the tables that contain the
data to include in the JSON document
ATTENDEE
AIDNAME COMPANY
PHON
E
3245 Jill ACME Inc 650
… … … …
… … … …
… … … …

39. CREATE JSON DUALITY VIEW attendeeSchedule 

AS attendee
{
_id : aid
name : name

company : company
schedule : attendee_sessions @delete @insert @Update
[ {
session 

{

code : sid



session : sname


time : time


room : room
speaker
{
speaker : sname

}
}
} ]
};
And specifies the updatability rules
Attendees can update their schedule, but not speaker, rooms, or speakers

40. CREATE JSON DUALITY VIEW attendeeSchedule 

AS attendee
{
_id : aid
name : name

company : company
schedule : attendee_sessions
[ {
session 

{

code : sid



session : sname


time : time


room : room
speaker @unnest
{
speaker : sname

}
}
} ]
};
Also specifies when properties in a nested object
should be unnested into the parent

41. Copyright © 2024, Oracle and/or its affiliates
Example of Using Duality Views
41
Selecting from the schedule Duality View accesses the
underlying tables and returns Jill’s schedule as a JSON
document
•This document has all the data needed by the use case
•And the IDs needed to update the data
{
"_id" : "3245",
"name" : "Jill",
"company" : "ACME Inc",

"schedule" : [
{

"code" : "DB12",
"session" : "SQL",

"time" : "14:00",
"room" : "A102",

"speaker" : "Adam"
},
{

"code" : "CODE3",
"session" : "NodeJs",

"time" : "16:00",
"room" : "R12",

"speaker" : "Claudia"
}
]
}
SCHEDULE FOR: JILL

42. Copyright © 2024, Oracle and/or its affiliates
Example of Using Duality Views
42
You can access the view using SQL or document APIs
SELECT data
FROM student_schedule s
WHERE s.data.name = 'Jill' ;
student_schedule.find({"name":"Jill
"})
SCHEDULE FOR: JILL
{
"_id" : "3245",
"name" : "Jill",
"company" : "ACME Inc",

"schedule" : [
{

"code" : "DB12",
"session" : "SQL",

"time" : "14:00",
"room" : "A102",

"speaker" : "Adam"
},
{

"code" : "CODE3",
"session" : "NodeJs",

"time" : "16:00",
"room" : "R12",

"speaker" : "Claudia"
}
]
}

43. Copyright © 2024, Oracle and/or its affiliates
Extreme Simplicity for Developers
43
DatabaseApp
GET Doc
PUT Doc
Change Doc
JSON Duality Views are extremely simple to
access:
•GET a document from the View
•Make any changes needed to the document
•PUT the document back into the View

44. Copyright © 2024, Oracle and/or its affiliates
Extreme Simplicity for Developers
44
DatabaseApp
GET Doc
PUT Doc
Change Doc
The database automatically detects the changes
in the new document and modifies the underlying
rows
•All duality views that share the same data
immediately reflect this change
•Developers no longer worry about
inconsistencies

45. Copyright © 2024, Oracle and/or its affiliates
The database automatically detects when the
database data underlying a document has changed
between the initial document read and the
subsequent write
•If a change occurred, the write operation is
automatically rejected and returns an error
•The app can then reissue the write based on
the changed data
Game Changing Lock-Free Concurrency Control
45
DatabaseApp
GET Doc
PUT Doc
Change Doc
Reject if
Stored Data
Changed
Called Optimistic Concurrency Control

46. Copyright © 2024, Oracle and/or its affiliates
•Regardless of the role anyone can work on the same data set whether JSON or relational
and can also build blended applications. Developers can use JSON Relational Duality to
efficiently join relational and JSON (semi-unstructured) data.
•Data consistency and integrity across data models and use cases as data is always
current and with no lagging and staleness.
•Data stored in Duality Views can be accessed via SQL, REST, document APIs (MongoDB
compatible) and many languages and drivers/tool, giving developers broad choices for all
use cases.
JSON Relational Duality Views Benefits I
46

47. Copyright © 2024, Oracle and/or its affiliates
•Better database performance and scalability for mixed workloads (relational +
semi-structured).
•Duality View eliminates the need for complex ORMs outside the database because
the app developers can directly map programming objects to Duality Views.
•Duality Views are programming language independent where as ORMs support
only one programming language. All that makes app development simple and
agile.
JSON Relational Duality Views Benefits II
47

48. Copyright © 2024, Oracle and/or its affiliates48
Architecturally provides
the use-case simplicity of JSON


with the multi-use case 

power of relational

49. Copyright © 2024, Oracle and/or its affiliates49
Database Software

54. https://github.com/gvenzl/oci-oracle-free

55. https://github.com/gvenzl/oci-oracle-xe

56. Testcontainers
How many people have heard about Testcontainers?

58. Copyright © 2024, Oracle and/or its affiliates58
Demo

59. Copyright © 2024, Oracle and/or its affiliates
Try Everything…for FREE
free-oracle.github.iocloud.oracle.com/freeoracle.com/database/
free/
oracle.com/livelabs
59

60. Thank you
@aalmiray
60