Techniques for comprehensively supporting JSON schema in a RDBMS
JSON schemas are implemented efficiently within a DBMS. Through these techniques, the power and benefit of schema-based paradigm are realized in a more cost-effective manner in terms of computer system performance. JSON schema-based techniques described herein improve execution efficiency of database statements that access JSON objects and improve software development productivity.
1 . A method, comprising:
a database management system (DBMS) receiving one or more data definition language (DDL) statements defining a JavaScript Object Notation (JSON) schema for a JSON column of a database table, said JSON schema defining a plurality of JSON fields for a JSON object, and said JSON schema declaring a first field of said plurality of JSON fields as a native database data type of said DBMS, wherein said native database data type is assignable to columns of database tables of said DBMS, and said native database data type is recognized as a valid data type of the DBMS without the native database data type being defined in a database dictionary of the DBMS;
said DBMS receiving a first data manipulation language (DML) statement specifying to insert a particular JSON object into said JSON column; and
in response to receiving the first DML statement:
determining that a field value for said first field in said particular JSON object conforms to said native database data type; and
inserting said particular JSON object in said JSON column.
2 . The method of claim 1 , the method further including storing said field value in said particular JSON object in a data type storage format of said native database data type.
3 . The method of claim 2 , wherein said JSON schema specifies to store said field value in the data type storage format of said native database data type.
4 . The method of claim 1 , wherein the method further includes rewriting said first DML statement based on said JSON schema.
5 . The method of claim 4 , wherein rewriting said first DML statement includes replacing an existing JSON operator with said JSON operator.
6 . The method of claim 1 , wherein the method further includes compiling said first DML statement.
7 . The method of claim 1 , wherein compiling said first DML statement includes detecting that the JSON schema is defined for the JSON column.
8 . The method of claim 1 , wherein compiling said first DML statement includes determining that said first DML statement specifies changes that comply with said JSON schema.
9 . The method of claim 1 , wherein the method further includes compiling said first DML statement, wherein compiling said first DML statement includes detecting schema-determinable errors either in a path expression or dot notation.
10 . The method of claim 1 , wherein said one or more DDL statements include no key value-pairs specifying properties of any fields of said JSON schema.
11 . The method of claim 1 , wherein said JSON schema declares a field of said JSON schema as a SQL domain type, wherein the method further includes receiving a DDL statement defining said SQL domain type based on a native data type.
12 . The method of claim 1 , the method further including:
said DBMS receiving an invocation of a function that requests a description of said JSON schema for said JSON column; and
in response to said DBMS receiving an invocation of a function, generating output describing said JSON schema.
13 . One or more non-transitory storage media storing instructions that, when executed by one or more computing devices, cause performance of:
a database management system (DBMS) receiving one or more data definition language (DDL) statements defining a JavaScript Object Notation (JSON) schema for a JSON column of a database table, said JSON schema defining a plurality of JSON fields for a JSON object, and said JSON schema declaring a first field of said plurality of JSON fields as a native database data type of said DBMS, wherein said native database data type is assignable to columns of database tables of said DBMS, and said native database data type is recognized as a valid data type of the DBMS without the native database data type being defined in a database dictionary of the DBMS;
said DBMS receiving a first data manipulation language (DML) statement specifying to insert a particular JSON object into said JSON column; and
in response to receiving the first DML statement:
determining that a field value for said first field in said particular JSON object conforms to said native database data type; and
inserting said particular JSON object in said JSON column.
14 . The one or more non-transitory storage media of claim 13 , the instructions further including instructions that, when executed by one or more computing devices, cause performance of storing said field value in said particular JSON object in a data type storage format of said native database data type.
15 . The one or more non-transitory storage media of claim 14 , wherein said JSON schema specifies to store said field value in the data type storage format of said native database data type.
16 . The one or more non-transitory storage media of claim 13 , wherein the instructions further including instructions that, when executed by one or more computing devices, cause performance of rewriting said first DML statement based on said JSON schema.
17 . The one or more non-transitory storage media of claim 16 , wherein rewriting said first DML statement includes replacing an existing JSON operator with said JSON operator.
18 . The one or more non-transitory storage media of claim 13 , the instructions further including instructions that, when executed by one or more computing devices, cause performance of compiling said first DML statement.
19 . The one or more non-transitory storage media of claim 13 , wherein compiling said first DML statement includes detecting that the JSON schema is defined for the JSON column.
20 . The one or more non-transitory storage media of claim 13 , wherein compiling said first DML statement includes determining that said first DML statement specifies changes that comply with said JSON schema.
21 . The one or more non-transitory storage media of claim 13 , the instructions further including instructions that, when executed by one or more computing devices, cause performance of compiling said first DML statement, wherein compiling said first DML statement includes detecting schema-determinable errors either in a path expression or dot notation.
22 . The one or more non-transitory storage media of claim 13 , wherein said one or more DDL statements include no key value-pairs specifying properties of any fields of said JSON schema.
23 . The one or more non-transitory storage media of claim 13 , wherein said JSON schema declares a field of said JSON schema as a SQL domain type, wherein the instructions further include instructions that, when executed by one or more computing devices, cause performance of receiving a DDL statement defining said SQL domain type based on a native data type.
24 . The one or more non-transitory storage media of claim 13 , the instructions further including instructions that, when executed by one or more computing devices, cause performance of:
said DBMS receiving an invocation of a function that requests a description of said JSON schema for said JSON column; and
in response to said DBMS receiving an invocation of a function, generating output describing said JSON schema.