Natively supporting JSON duality view in a database management system

Patent metadata

Field	Value
Publication number	US-12287777-B2
Application number	US-202217966730-A
Country	US
Kind code	B2
Filing date	Oct 14, 2022
Priority date	Oct 14, 2022
Publication date	Apr 29, 2025
Grant date	Apr 29, 2025

Abstract

JSON Duality Views are object views that return JDV objects. JDV objects are virtual because they are not stored in a database as JSON objects. Rather, JDV objects are stored in shredded form across tables and table attributes (e.g. columns) and returned by a DBMS in response to database commands that request a JDV object from a JSON Duality View. Through JSON Duality Views, changes to the state of a JDV object may be specified at the level of a JDV object. JDV objects are updated in a database using optimistic lock.

First claim

What is claimed is:

1. A method comprising:
executing a database transaction, wherein executing a database transaction includes:
a database management system (DBMS) receiving a request to change a JavaScript Object Notation (JSON) object in a JSON object view comprising a JSON DUALITY VIEW, said JSON DUALITY VIEW defining an object schema for a JSON object in said JSON DUALITY VIEW, said DUALITY VIEW mapping base attributes of a plurality of base tables to fields of said object schema, said request specifying a change to at least one field of said JSON object;
generating a derived set of derived records derived from said JSON object according to said JSON DUALITY VIEW mapping said base attributes of said plurality of base tables to said fields of said object schema;
comparing said derived set of derived records to one or more base records stored in said plurality of base tables of a database;
based on said comparing of said derived set of derived records to said one or more base records, determining that one or more changes to said JSON object represent an update to one or more attributes of said one or more base records stored in said plurality of base tables of said database;
executing one or more change operations to make said one or more changes to said one or more base records; and
committing said one or more changes.

2. The method of claim 1 , wherein determining that one or more changes to said JSON object represent an update includes:
retrieving from said plurality of base tables a base set of base records, each base record of said base record being a version of a respective derived record from said derived set.

3. The method of claim 2 , wherein determining that one or more changes to said JSON object represent an update includes comparing said derived set to said base set to determine that a plurality of derived records represents a change to a respective base record of said base set of base records.

4. The method of claim 1 , wherein said plurality of base tab les include a parent table and child table that have 1-to-N relationship based on a primary key in said parent table and a foreign key in said child table, wherein said one or more change operations include an update to said child table.

5. The method of claim 1 ,
wherein said object schema defines an object hierarchy comprising a first level and a second level;
wherein said object schema defines an object field that corresponds to said first level and a plurality of child fields of said object field that correspond to said second level;
wherein said plurality of base tables include a parent table and a child table of said parent table;
wherein said JSON DUALITY VIEW maps said child table as a base table for said child fields based on a primary key of said child table and a foreign key of said parent table;
wherein said one or more changes update a record in said child table.

6. The method of claim 1 ,
wherein said object schema defines an object hierarchy comprising a first level and a second level;
wherein said object schema defines an array field that corresponds to said first level and a plurality of child fields that correspond to said second level;
wherein said plurality of base tables include a parent table and a child table of said parent table;
wherein said JSON DUALITY VIEW maps said child table as a base table for said child fields based on a foreign key of said child table and a primary key of said parent table;
wherein said one or more changes update a record in said child table.

7. The method of claim 1 ,
wherein said JSON DUALITY VIEW identifies a change permission to update a base table of said plurality of base tables;
wherein said one or more updates include a particular update to an attribute of a record in said base table;
wherein executing the database transaction further includes determining that said change permission permits said particular update.

8. The method of claim 1 ,
wherein said JSON DUALITY VIEW identifies a change permission to update a particular attribute of a base table of said plurality of base tables;
wherein said one or more changes include a particular update to an attribute of a record in said base table; and
wherein executing the database transaction further includes determining that said change permission permits said particular update.

9. The method of claim 1 ,
wherein said one or more attributes are one or more columns stored in a relational DBMS, or
wherein said one or more attributes are one or more fields of JSON objects stored in document storage system.

10. The method of claim 1 , further comprising:
generating a delta record set based at least in part on said comparing of said derived set of derived records to said one or more base records, said delta record set representing one or more differences between the derived set of derived records,
wherein said determining that said one or more changes to said JSON object represent said update to said one or more attributes of said one or more base records is further based at least in part on said delta record set.

11. One or more non-transitory computer-readable media storing one or more sequences of instructions that, when executed by computing devices, cause:
executing a database transaction, wherein executing a database transaction includes:
a database management system (DBMS) receiving a request to change a JavaScript Object Notation (JSON) object in a JSON object view comprising a JSON DUALITY VIEW, said JSON Duality View defining an object schema for a JSON object in said JSON DUALITY VIEW, said JSON DUALITY VIEW mapping base attributes of a plurality of base tables to fields of said object schema, said request specifying a change to at least one field of said JSON object;
generating a derived set of derived records derived from said JSON object according to said JSON DUALITY VIEW mapping said base attributes of said plurality of base tables to said fields of said object schema;
comparing said derived set of derived records to one or more base records stored in said plurality of base tables of a database;
based on said comparing of said derived set of derived records to said one or more base records, determining that one or more changes to said JSON object represent an update to one or more attributes of said one or more base records stored in said plurality of base tables of said database;
executing one or more change operations to make said one or more changes to said one or more base records; and
committing said one or more changes.

12. The one or more non-transitory computer-readable media of claim 11 , wherein determining that one or more changes to said JSON object represent an update includes:
retrieving from said plurality of base tables a base set of base records, each base record of said base record being a version of a respective derived record from said derived set.

13. The one or more non-transitory computer-readable media of claim 12 , wherein determining that one or more changes to said JSON object represent an update includes comparing said derived set to said base set to determine that a plurality of derived records represents a change to a respective base record of said base set of base records.

14. The one or more non-transitory computer-readable media of claim 11 , wherein said plurality of base tables include a parent table and child table that have 11-to-N relationship based on a primary key in said parent table and a foreign key in said child table, wherein said one or more change operations include an update to said child table.

15. The one or more non-transitory computer-re

Assignees

• Oracle Int Corp

Inventors

• Liu Zhen Hua
• Loaiza Juan R
• Abraham Sundeep
• Bose Shubha
• Chang Hui Joe
• Gugnani Shashank
• Hammerschmidt Beda Christoph
• Lahiri Tirthankar
• Lu Ying
• Mcmahon Douglas James
• Mishra Aurosish
• Mylavarapu Ajit
• Pendse Sukhada
• Raghavan Ananth

Classifications

• G06F16/24568

  Data stream processing; Continuous queries · CPC title

• G06F16/2379 Primary

  Updates performed during online database operations; commit processing · CPC title

• G06F16/86 Primary

  Mapping to a database · CPC title