Natively supporting JSON duality view in a database management system

Patent metadata

Field	Value
Publication number	US-12298946-B2
Application number	US-202217966736-A
Country	US
Kind code	B2
Filing date	Oct 14, 2022
Priority date	Oct 14, 2022
Publication date	May 13, 2025
Grant date	May 13, 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

The invention claimed is:

1. A method comprising:
receiving, by a database management system (DBMS), a request to return a JavaScript Object Notation (JSON) object from a JSON Duality View, said JSON Duality View defining an object schema for said JSON object, wherein said JSON Duality View maps base attributes of a plurality of base tables to a plurality of fields of said object schema; and
in response to receiving said request, generating, by said DBMS, said JSON object,
wherein said JSON object includes:
said plurality of fields of said object schema;
an object identifier field; and
a version signature field;

wherein generating said JSON object includes:
retrieving a set of base records for said JSON object based on said JSON Duality View;
populating said plurality of fields with values based on respective attribute values of said base attributes of said set of base records; and
deterministically generating a version signature for said version signature field object based on said respective attribute values of said base attributes of said set of base records.

2. The method of claim 1 ,
wherein said object schema defines an object hierarchy comprising:
a root level having an object field and one or more root fields that are scalar fields, wherein a root table includes one or more root attributes, each root attribute of said one or more root attributes being a base attribute of a respective root field of said one or more root fields; and
a first level having a plurality of child fields of said object field, wherein a child table of said root table includes a first plurality of first attributes, each first attribute of said first plurality of first attributes being a base attribute of a respective child field of said plurality of child fields.

3. The method of claim 2 ,
wherein a root record from said root table is a base record for said JSON object;
wherein a first record from said child table is a base record for said object field;
wherein generating said JSON object includes generating a version signature for said version signature field by at least:
generating a particular signature by at least invoking a base signature function using, as input, a respective value of at least one or more of said first plurality of first attributes; and
generating said version signature by at least invoking a base signature function using, as input, at least: said particular signature and a value of at least one root attribute of said one or more root attributes.

4. The method of claim 2 , further including:
in response to receiving a Data Definition Language (DDL) statement, said DBMS generating and storing in a database dictionary metadata that defines said JSON Duality View;
wherein said DDL statement includes a view query expression, wherein said view query expression:
projects a first json_object function expression in an outer query expression that maps said one or more root fields to said one or more root attributes of said root table;
wherein said first json_object function expression includes a subquery expression that projects a second json_object expression that maps said plurality of child fields of said object field to said first plurality of first attributes included in said child table; and
wherein said view query expression correlates of a foreign key of said root table to a primary key of said child table.

5. The method of claim 2 , further comprising:
receiving a Data Definition Language (DDL) statement, wherein said DDL statement identifies said root table, said child table of said root table, and a name for said object field;
wherein a defined primary-foreign key relationship is based on a foreign key of said root table and a primary key of said child table;
where said DDL statement does not explicitly reference a correlation between said foreign key and said primary key;
based on said defined primary-foreign key relationship that is based on said foreign key and said primary key, said DBMS generating and storing in a database dictionary metadata that defines said JSON Duality View.

6. The method of claim 5 , wherein said DDL statement includes an expression conforming to GraphQL.

7. The method of claim 5 , wherein said database command includes braces to indicate said child table is a base table for said object field.

8. The method of claim 5 , wherein said DBMS generating and storing in a database dictionary metadata that defines said JSON Duality View includes leveraging Structured Query Language (SQL) token and parsing constructs.

9. The method of claim 2 , further including:
in response to receiving a Data Definition Language (DDL) statement, said DBMS generating and storing in a database dictionary metadata that defines said JSON Duality View;
wherein said DDL statement includes a view query expression, wherein said view query expression:
projects a first json_object function expression in an outer query expression that maps said one or more root fields to said one or more root attributes of said root table;
wherein said first json_object function expression includes a subquery expression that projects a second json_object expression that maps said plurality of child fields of an array field to said first plurality of first attributes included in said child table; and
wherein said view query expression correlates of primary key of said root table to a foreign key of said child table.

10. The method of claim 2 , further comprising:
receiving a Data Definition Language (DDL) statement, wherein said DDL statement identifies said root table, said child table of said root table, and a name for an array field;
wherein a defined primary-foreign key relationship is based on a foreign key of said root table and a primary key of said child table;
where said DDL statement does not explicitly reference a correlation between said foreign key and said primary key;
based on said defined primary-foreign key relationship that is based on said foreign key and said primary key, said DBMS generating and storing in a database dictionary metadata that defines said JSON Duality View.

11. The method of claim 10 , wherein said DDL statement includes an expression conforming to GraphQL.

12. The method of claim 2 , the method including:
receiving a request for a JSON object from said JSON Duality View having an object identifier field equal to an object identifier;
in response to said request, invoking an object identifier function to convert said object identifier to a primary key value;
selecting a root record from said root table having said primary key value for a primary key of said root table;
generating said JSON object based on said root table.

13. The method of claim 2 , the method including:
receiving a request for a JSON object from said JSON Duality View having an object identifier field equal to an object identifier;
in response to said request, invoking an object identifier function to convert said object identifier to a first primary key value and a second primary key value;
selecting a root record from said root table having said first primary key value for a first primary key of said root table and having said second primary key value for a second primary key value; and
generating said JSON object based on said root table.

14. The method of claim 2 , the method including:
receiving a request for a JSON object from said JSON Duality View having a root field equal to a particular value;
in response to said request, filtering a plurality of records from said root table by comparing to said particular value to a root attribute corresponding to said root field to se

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/24534

  Query rewriting; Transformation · CPC title

• G06F16/86

  Mapping to a database · CPC title

• G06F16/21

  Design, administration or maintenance of databases · CPC title

• G06F16/25

  Integrating or interfacing systems involving database management systems · CPC title

• G06F16/212 Primary

  with details for data modelling support · CPC title