# US 2021/0081389 A1 · Technique of efficiently, comprehensively and autonomously support native json datatype in rdbms for both oltp & olap

> Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt

- Number: US 2021/0081389 A1
- Document ID: US20210081389A1
- Status: Published application
- Kind: A1
- Application number: 16/904,417
- Filed: 2020-06-17
- Published: 2021-03-18
- Earliest claimed: 2019-09-13
- Assignees: Oracle International Corporation
- Applicants: Oracle International Corporation
- Inventors: Zhen Hua Liu; Sriram Krishnamurthy; Beda C. Hammerschmidt; Douglas J. McMahon; Hui Joe Chang; Ying Lu; Joshua Spiegel; Srikrishnan Suresh; Vikas Arora; Geeta Arora; Sundeep Abraham; Hui Zhang; Alfonso Colunga Sosa
- Web page: https://patentgrep.com/patent/US20210081389A1

## CPC

- G06F 16/2282: {Tablespace storage structures; Management thereof}
- G06F 11/3409: {for performance assessment}
- G06F 16/221: {Column-oriented storage; Management thereof}
- G06F 16/244: {Grouping and aggregation}
- G06F 16/24552: {Database cache management}
- G06F 16/24556: {Aggregation; Duplicate elimination}
- G06F 16/248: Presentation of query results
- G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N)

## Abstract

Herein is a self-tuning database management system (DBMS) storing JavaScript object notation (JSON) documents and operating a JSON datatype as native to the DBMS. In an embodiment, a computer hosts a DBMS that executes a data definition language (DDL) statement that defines, in a database dictionary of the DBMS, a JSON document column of a database table that stores JSON documents as instances of the JSON datatype that is native in the DBMS. The DBMS may autonomously set or adjust configuration settings that control behaviors such as a default width of a JSON document column, in lining or not of the JSON document column, kind and scope and duration of indexing of the JSON document column, and/or caching of the JSON document column such as in an in memory columnar unit (IMCU). The DBMS may use the various configuration settings to control how JSON documents and the native JSON datatype are stored and/or processed.

## Claims

### Claim 1

A method comprising:
configuring, automatically by a database management system (DBMS), an adjustable setting of a JavaScript object notation (JSON) datatype that is native in the DBMS;
executing a data definition language (DDL) statement that defines, in a database dictionary of the DBMS, a JSON document column of a database table that stores a plurality of JSON documents as instances of the JSON datatype that is native in the DBMS;
receiving a structured query language (SQL) statement that references the JSON document column;
executing the SQL statement by:
accessing a scalar column of the database table that is defined in the database dictionary, and
accessing at least a portion of at least one JSON document of the plurality of JSON documents by using: the JSON datatype that is native in the DBMS, and the adjustable setting of the JSON datatype.

### Claim 2 (depends on 1)

The method of claim 1 further comprising using a virtual column of the database table to access an index for at least one JSON document of the plurality of JSON documents.

### Claim 3 (depends on 1)

The method of claim 1 further comprising driving a lifecycle of an index for at least one JSON document of the plurality of JSON documents by:
generating the index without receiving a query,
generating the index in response to receiving a query,
generating the index in response to loading an in memory columnar unit (IMCU),
generating the index as a path and value index for an in memory expression unit (IMEU),
generating the index based on access statistics for the plurality of JSON documents,
generating the index when available memory exceeds a threshold, and/or
discarding the index based on dynamic conditions.

### Claim 4 (depends on 1)

The method of claim 1 further comprising generating an index for at least one JSON document of the plurality of JSON documents, wherein the index comprises: a bitmap, an inverted index, a path and value index, a text index, and/or an index of a subset of the plurality of JSON documents that are loaded into memory.

### Claim 5 (depends on 1)

The method of claim 1 further comprising operating an index for at least one JSON document of the plurality of JSON documents, including:
returning sorted values,
invoking a SQL operator that uses the JSON datatype that is native in the DBMS,
invoking a user defined function (UDF), and/or
evaluating an expression that: is compound and/or references multiple fields of the plurality of JSON documents.

### Claim 6 (depends on 1)

The method of claim 1 wherein each document of the plurality of JSON documents is individually persisted with: compression, encryption, and/or i-nodes that do not store multiple documents.

### Claim 7 (depends on 1)

The method of claim 1 further comprising the DBMS modifying a portion of a JSON document of the plurality of JSON documents without entirely replacing the JSON document.

### Claim 8 (depends on 7)

The method of claim 7 wherein said modifying the portion of the JSON document comprises: generating redo data only for said portion of the JSON document, and/or copying-on-write.

### Claim 9 (depends on 1)

The method of claim 1 further comprising generating a database view that contains a table column that is based on a field that is common to the plurality of JSON documents.

### Claim 10 (depends on 1)

The method of claim 1 wherein:
said SQL statement contains a dot notation;
said accessing at least the portion of the at least one JSON document comprises executing the dot notation.

### Claim 11 (depends on 1)

The method of claim 1 further comprising:
receiving a JSON document as an instance of the JSON datatype that is native in the DBMS;
verifying that said instance of the JSON datatype does not contain:
an amount of nesting levels that exceeds a threshold,
a cycle, and/or
a dangling indirection.

### Claim 12 (depends on 1)

The method of claim 1 wherein:
the SQL statement contains a field name that is not defined in the database dictionary;
said accessing at least the portion of the at least one JSON document is based on the field name.

### Claim 13

A method comprising:
receiving, by a database management system (DBMS) that stores a plurality of JavaScript object notation (JSON) documents, a structured query language (SQL) statement that specifies an aggregation function;
identifying a subset of the plurality of JSON documents that satisfy the SQL statement;
applying the aggregation function individually to each JSON document of the subset of the plurality of JSON documents to calculate a respective aggregate value of a set of aggregate values for the subset of the plurality of JSON documents;
generating a result set that contains said set of aggregate values for the subset of the plurality of JSON documents.

### Claim 14 (depends on 13)

The method of claim 13 wherein said applying the aggregation function comprises using a path and value index or an inverted index.

### Claim 15

A method comprising:
sending, to a database management system (DBMS), a structured query language (SQL) statement that references: a) a scalar column that is defined in a database dictionary of the DBMS for a database table, and b) a document column in same said database table that contains binary JavaScript object notation (JSON) documents;
receiving, by a client, a binary JSON document that satisfies the SQL statement from the DBMS;
using, by the client after said receiving the binary JSON document, dot notation to access a field in the binary JSON document without decoding the binary JSON document into text.

### Claim 16 (depends on 15)

The method of claim 15 further comprising sending, by the client, an indication of how the DBMS should cache the binary JSON document.

### Claim 17

One or more non-transitory computer-readable media storing instructions that, when executed by one or more processors, cause:
configuring, automatically by a database management system (DBMS), an adjustable setting of a JavaScript object notation (JSON) datatype that is native in the DBMS;
executing a data definition language (DDL) statement that defines, in a database dictionary of the DBMS, a JSON document column of a database table that stores a plurality of JSON documents as instances of the JSON datatype that is native in the DBMS;
receiving a structured query language (SQL) statement that contains a field name that is not defined in the database dictionary;
executing the SQL statement by:
accessing a scalar column of the database table that is defined in the database dictionary, and
accessing at least a portion of at least one JSON document of the plurality of JSON documents by using: the field name, the JSON datatype that is native in the DBMS, and the adjustable setting of the JSON datatype.

### Claim 18 (depends on 17)

The one or more non-transitory computer-readable media of claim 17 wherein the instructions further cause generating a database view that contains a table column that is based on a field that is common to the plurality of JSON documents.

### Claim 19

One or more non-transitory computer-readable media storing instructions that, when executed by one or more processors, cause:
receiving, by a database management system (DBMS) that stores a plurality of JavaScript object notation (JSON) documents, a structured query language (SQL) statement that specifies an aggregation function;
identifying a subset of the plurality of JSON documents that satisfy the SQL statement;
applying the aggregation function individually to each JSON document of the subset of the plurality of JSON documents to calculate a respective aggregate value of a set of aggregate values for the subset of the plurality of JSON documents;
generating a result set that contains said set of aggregate values for the subset of the plurality of JSON documents.

### Claim 20

One or more non-transitory computer-readable media storing instructions that, when executed by one or more processors, cause:
sending, to a database management system (DBMS), a structured query language (SQL) statement that references: a) a scalar column that is defined in a database dictionary of the DBMS for a database table, and b) a document column in same said database table that contains binary JavaScript object notation (JSON) documents;
receiving, by a client, a binary JSON document that satisfies the SQL statement from the DBMS;
using, by the client after said receiving the binary JSON document, dot notation to access a field in the binary JSON document without decoding the binary JSON document into text.
