Using Oracle Database for JSON-based application development

This article is“JSON-based Application Development with Oracle Database (and MongoDB compatibility)”Translation + Read notes.

Subtitles: Versions 19c and 21c, on-premises and cloud, autonomous JSON databases, and Oracle Database API for MongoDB, version 1.1, February 2022.

Purpose

This document provides an overview of the features and enhancements included in Oracle Database 19c and 21c releases, as well as related Oracle technologies. Designed to help you understand why modern application development often uses JSON as the data persistence format, and why the JSON functionality in Oracle Database is ideal for today's developers looking for document storage to persist, query and process application data.

Flexible application development

Modern application development is underway in a changing environment. Users expect applications to adapt to rapidly changing business needs and to deliver updates instantly. All of this means developers need a flexible data persistence mechanism to minimize downtime or DBA participation during application development.Relational models lack this flexibility: Tables have static "shapes" and application changes require modifications (such as adding new columns), which usually requires a database administrator (DBA) to do. Additionally, existing data may need to be modified to accommodate new patterns. More importantly, the relationship method requiresPre-design pattern: The application's object (such as "customer order") is normalized to the table and columns that store object values. An application object is usually normalized to multiple tables. This means that a simple put or get operation now requires inserting and querying all participating tables and satisfying the correct join conditions. Developers must understand this mapping and express it using SQL.

The lacks flexibility and upfront schema design mentioned here are both facts, but they do not mean bad. JSON document storage provides another possibility, when to use relational models and when to use document storage depends on the specific situation.

This approach, while proven to last for decades, is often considered too rigid, formal and slow for modern application development. Furthermore, since changes in applications and databases often have to be synchronized, downtime is more likely and operational costs are higher.

Document storage(also known as document database) works differently, itNo need to pre-defined architecture（schema last). Instead, application data is modeled in document form,Usually in JSON format. Every document isSelf-describe(composed of named key/value pairs), so no external schema is needed to understand these values. Furthermore, different documents can have different key/value pairs, which makes it easy for the application to evolve by dynamically adding new key/value pairs without modifying existing data/documents. Therefore, using documents for data persistence can provideThe flexibility required by developersStorage mechanism.

Since JSON-based APIs are everywhere, another requirement for handling JSON comes with it: the REST service uses JSON as input and output. If a third-party API changes and no longer matches the table, mapping these JSON values ​​to the table can cause the application to crash. Instead, JSON data is best "as is" (as is, unchanged) Stored in a database that supports JSON data queries.

Limitations of NoSQL Document Storage

Developers tend to use NoSQL products because they are considered easier to use than relational databases. A typical NoSQL document store organizes JSON documents in a collection. Because the data model is simple and consists of only collections and documents, these systems provideIt is also relatively simple and is particularly limited in reporting or analyzing use cases.（Join and Aggregation are not what Document Store is good at）。

If such demand arises, developers usually deploy a second (relational) database and store data twice; this usually requires an ETL process (extract, convert, load) to convert the data into a relational format.

Additionally, NoSQL document storage is usuallyComplex transaction and reference integrity constraints are not supported,thereforeData consistency is now a problem that developers need to solve. The required "workarounds" increase system complexity, reduce security, lead to inconsistencies, and create new problems such as recovery across different databases.

Due to this additional complexity, the total cost of ownership is often high and no longer fulfills the promise of a simple NoSQL product.

Use Oracle databases as document storage

Oracle Database provides the same application development experience as a dedicated NoSQL document storage: it can store, manage, and index JSON documents and provides a NoSQL document storage API similar to common NoSQL products. It even supports APIs that are compatible with MongoDB, one of the most popular document stores.

Additionally (unlike NoSQL products), Oracle Database provides complex SQL queries, reporting, analytics, and machine learning capabilities for JSON documents. This allows you toIntegrate JSON and relational data and connect them to the same query。

Because JSON features are integrated into Oracle databases, all of its enterprise-level features, such as availability, security, scalability, performance, and manageability, fully support JSON data.

Store and manage JSON documents in Oracle databases

Oracle Database Version 21c has added the SQL data type "JSON", which is in a binary format optimized for fast queries and segmented updates.

Earlier versions (such as 19c) allow the storage of JSON documents using VARCHAR2, CLOB, or BLOB columns. The "IS JSON" SQL check constraint ensures that the column contains only valid JSON documents, allowing the database to recognize that the column is being used as a container for the JSON document. Oracle's JSON capabilities focus on providing comprehensive support for schema flexible development and document-based storage.

Therefore, although the Oracle database knows that a given column contains JSON documents, the database does not know its internal structure (key/value pair) when storing, indexing, and querying these documents. Developers can freely change the structure of JSON documents as needed.

Oracle Database provides comprehensive JSON support for all its advanced features, including disaster recovery, replication, compression, and encryption. In addition, products that support Oracle databases, such as Oracle GoldenGate and Oracle Data Integrator, as well as third-party tools, also seamlessly support JSON documents stored in the database.

Autonomous JSON database

Oracle database supports JSON since version 12.1.0.2 and has added many new JSON features.

The managed database cloud service called "Automatic JSON Database" (AJD) provides the features outlined in this technical report and is much cheaper than other members of the Autonomous Database family.

In addition to supporting the document storage API, AJD is fully capable of running arbitrary SQL statements and storing non-JSON data in relational tables.

Because AJD is for JSON developers, the size of non-JSON data is limited to 20GB; if more data is needed, you can upgrade to the Autonomous Transaction Processing (ATP) service with a click of the mouse.

Therefore, AJD is not an independent development environment that requires different skills or APIs.

As part of the autonomous database platform, AJD users can benefit fully from the autonomous databaseAutonomous drive, autonomous security and autonomous repairFunction. Maximize database uptime, automatic scaling (up to three times the configuration CPU limit) features provide maximum performance at the lowest cost.
For more information about the Autonomous JSON database service, please visit:
/autonomous-database/autonomous-json-database/

Oracle Database API for MongoDB (for autonomous databases)

All Oracle Autonomous Databases, including Autonomous JSON Databases, are compatible with MongoDB: Tools, drivers, and applications written for MongoDB can connect to Oracle Autonomous Database using the MongoDB native API that transparently converts MongoDB database operations into equivalent SQL/JSON operations and then executes on the Oracle database.

MongoDB applications communicate through the MongoDB API as if they were still connected to the MongoDB server.

The developers canContinue to use their MongoDB skills and tools while also running SQL statements on JSON data in MongoDB collections. This makes real-time SQL analytics and machine learning possible based on JSON data.

You can also generate JSON from relational data and expose the results as a MongoDB-compatible collection so that MongoDB applications can easily access query results or relational data.

Oracle Database API for MongoDB also supports MongoDB tools such as Compass, mongosh (mongo shell), and mongoimport/mongorestore, so thatSimplifies migration to Oracle。

As of now (February 2022), Oracle Database API for MongoDB was initially only available on Shared Autonomous Database (ADDB). For details, please visit:
/en/database/oracle/mongodb-api/mgapi

Simple Oracle Document Access API (SODA)

Since MongoDB's Oracle Database API is currently limited to shared autonomous databases, Oracle provides anotherCommon document storage API—Available in the cloud (all Oracle cloud databases) and on-premises: Simple Oracle Document Access (SODA) API.

The original intention of this API isSupport flexible application development, very similar to the common NoSQL document storage APIs such as MongoDB.

With SODA, developers can process JSON documents and collections without learning SQL. Instead, database operations for collections and documents can be called directly through a simple API - this API not only supports REST, but also supports common programming languages ​​such as Java, Python, JavaScript (), C, and PL/SQL.

SODA for REST is part of the Oracle REST Data Service (ORDS) and can be called through any language that supports REST/HTTP calls. Java, Python, and C drivers are all open source.

The conceptual model of SODA is very similar to MongoDB: Application objects are stored in a collection as JSON documents. Documents are identified with keys. The collection is identified by a name. Heterogeneous collections allow storage of non-JSON objects, such as images. Multiple collections reside in the database connected to the client program.

Documents can be accessed using the SODA command, often used for simple CRUD operations (create, read + find, update, delete), or use SQL: it is easy to report, analyze, or machine learning the same JSON data.

We illustrate the SODA API through REST and Java examples. Links to the SODA documentation and drivers and tutorials can be found here:
/database/technologies/appdev/

SODA Example

The following Java code creates a collection "orders" and inserts a JSON document. It then retrieves the unique key (id) assigned by SODA to the document. SODA can also accept user-generated keys.

OracleRDBMSClient client = new OracleRDBMSClient(); 
OracleDatabase db = (conn); 
OracleCollection orders = ().createCollection("orders"); 
OracleDocument doc = (('{…}')); 
String id = ();

As you can see, databases, collections, and documents are mapped to Java classes that have functions that expose their functions.
In SODA for REST, HTTP verbs (such as PUT, POST, GET, and DELETE) are mapped to SODA operations on the document. The URL contains the name of the key or collection of the document, as well as the database host name and authorization credentials. SODA for REST is an Oracle REST data service that relies on ORDS for authentication and authorization. This example omits this section for space reasons. Creating a collection and inserting a document requires a REST call respectively. The second call returns an HTTP response with the specified key (id):

curl -X PUT http://<authUrlToOrds>/soda/latest/orders
curl -X POST -H "Content-type: application/json"
--upload-file  http://<urlToORDS>/soda/latest/orders
{ "items": [
	{ 	"id": "A450557094D04957B36346F630CDDF9A", 
		"etag":"C13578001CBBC84022DCF5F7209DBF0E6DFFCC626E3B0400C3", 
		"lastModified": "2021-02-09T01:03:48.291462",
		"created": "2021-02-09T01:03:48.291462" 
	} 
	], 
	"hasMore": false, "count": 1
}

The above example shows the difference between document storage and traditional SQL databases: New documents are added to the collection as JSON objects. The database has no restrictions on the keys contained in these documents. API calls are also simpler for developers accustomed to object-oriented programming environments. Note: The difference between SODA for REST and other language drivers (such as Java) is thatREST is stateless, so all REST operations are committed immediately, while the language driver relies on a database connection that supports transactions (multiple operations can be set as atomic operations).

Now, let's use SODA to retrieve the document. SODA obviously supports getting documents by keys, but a more interesting way to query data is to find all documents that meet the search criteria (represented in JSON documents), which we call QueryByExample (QBE). This is a very basic QBE that selects the value of all fields "region" to "north" and the value of the second field "quantity" to 10 or larger:

{"region":"north", "quantity":{"$gte":10}}

(Skip and limit can be used to paginate a large number of results.)
The following Java code snippet performs a QBE search, limiting the results to the first 100 documents and printing all document keys. The value of the variable "qbe" is the above QBE.

OracleCollection coll = ("orders");
OracleCursor results = ().filter(qbe).limit(100).getCursor();
while (()) {
	OracleDocument doc = ();
	(()); 
}

REST parameter action=query means that POST contains a QBE request.

curl -X POST -H "Content-type: application/json" --data
'{"region":"north", "quantity":{"$gte":10}}' 
http://<urlToORDS>/ords/SCOTT/soda/latest/orders?action=query

Analyze and report JSON content stored in Oracle databases

As mentioned above, Oracle Database provides many advantages of NoSQL document storage for application development. One of the advantages of using Oracle databases is that it also applies the full functionality of SQL to the same JSON documents. This is thanks to the support of JSON collections by regular tables created automatically. They contain a JSON column for storing the document, and additional columns for unique keys (IDs) and metadata such as creation dates. Order collections are supported by the following tables:

SQL> describe "orders"
Name            Null?     Type
----------------------------------------
ID              NOT NULL  VARCHAR2(255)
CREATED_ON      NOT NULL  TIMESTAMP(6)
LAST_MODIFIED   NOT NULL  TIMESTAMP(6)
VERSION         NOT NULL  VARCHAR2(255)
JSON_DOCUMENT   		  JSON

Oracle databases support multiple SQL operators to handle JSON:

Many operators depend onPath expressionto navigate in JSON data and optionallyUse path predicate filtering. A detailed description of operators and path expressions can be found in the JSON Developer Guide:
/en/database/oracle/oracle-database/21/adjsn/

For the following example, we assume that this collection/table contains a purchase order file:

{
  "PONumber": 1600,
  "Reference": "ABULL-20140421",
  "Requestor": "Alexis Bull",
  "User": "ABULL",
  "CostCenter": "A50",
  "Instructions": {
    "name": "Alexis Bull",
    "Address": {
      "street": "200 Sporting Green",
      "city": "South San Francisco",
      "state": "CA",
      "zipCode": 99236,
      "country": "United States of America"
    },
    "Phone": [
      {
        "type": "Office",
        "number": "823-555-9969"
      }
    ]
  },
  "Special Instructions": "Counter to Counter",
  "LineItems": [...]
}

The easiest way to query JSON data using SQL is to be simpleDot symbol, which allows navigation of JSON structures and selecting values.

SQL> select j.PO_DOCUMENT.Reference,
           j.PO_DOCUMENT.Requestor,
           j.PO_DOCUMENT.CostCenter,
           j.PO_DOCUMENT.
    from J_PURCHASEORDER j
    where j.PO_DOCUMENT.PONumber = 1600;
REFERENCE            REQUESTOR     COSTCENTER   SHIPPINGINSTRUCTIONS
-------------------- -------------- ------------ ---------------------
ABULL-20140421       Alexis Bull   A50          South San Francisco

JSON_TABLE is a commonly usedMap JSON data to relational modelstable function so that it can be accessed. This has many benefits:

• Relational models are very suitable for analytical queries, especially Counter queries where dimensions and facts are stored in different sets. Using materialized views can even "pre-calculate" these connections.
• Tools that operate relationship models can be used to handle JSON, such as report generators, dashboards, and machine learning, which can be applied directly to JSON data.
• Data analysts can use SQL language and tuning skills without manually mapping JSON data to tables or writing custom code.

JSON_TABLE Uses a set of JSON path expressions to project content in a JSON document into relational columns in a virtual table. You can use the JSON_TABLE expression in the FROM clause of a SQL query, just like using a relational table. The following example projects a set of columns from a JSON document collection. Each JSON path expression returns a scalar value from the document, so a row in the virtual table is generated for each document.

SQL> select jt.*
    from J_PURCHASEORDER p,
         JSON_TABLE(p.PO_DOCUMENT, '$' columns
           PO_NUMBER NUMBER(10) path '$.PONumber',
           REFERENCE VARCHAR2(30 CHAR) path '$.Reference',
           REQUESTOR VARCHAR2(32 CHAR) path '$.Requestor',
           USERID VARCHAR2(10 CHAR) path '$.User',
           COSTCENTER VARCHAR2(16 CHAR) path '$.CostCenter',
           TELEPHONE VARCHAR2(16 CHAR) path '$.[0].number'
         ) jt
    where PO_NUMBER > 1599 and PO_NUMBER < 1602;
PO_NUMBER   REFERENCE        REQUESTOR    USERID  COSTCENTER  TELEPHONE
----------- --------------- ------------- ------- ----------- --------------
1600        ABULL-20140421   Alexis Bull  ABULL  A50         909-555-7307
1601        ABULL-20140423   Alexis Bull  ABULL  A50         909-555-9119
2 rows selected.

JSON_TABLE also supports JSON documentation with nested arrays: NESTED PATH Iterates over nested "LineItems" arrays: Values ​​outside of nested arrays are repeated (PO_NUMBER) because they are suitable for the entire nested array.

SQL> select jt.* 
    from J_PURCHASEORDER p,
         JSON_TABLE(p.PO_DOCUMENT, '$' columns(
           PO_NUMBER NUMBER(10) path '$.PONumber',
           REFERENCE VARCHAR2(30 CHAR) path '$.Reference',
           NESTED PATH '$.LineItems[*]' columns(
             ITEMNO NUMBER(16) path '$.ItemNumber',
             DESCRIPTION VARCHAR2(32) path '$.',
             UPCCODE VARCHAR2(14) path '$.',
             QUANTITY NUMBER(5,4) path '$.Quantity',
             UNITPRICE NUMBER(5,2) path '$.'
           )
         ) jt
    where PO_NUMBER > 1599 and PO_NUMBER < 1602;
PO_NUMBER   REFERENCE        ITEMNO DESCRIPTION                UPCCODE   QUANTITY  UNITPRICE
----------- ---------------  ------ -------------------------- --------  --------  ----------
1600        ABULL-20140421   1      One Magic Christmas        13131092  9         19.95
1600        ABULL-20140421   2      Lethal Weapon              8539162   5         19.95
1601        ABULL-20140423   1      Star Trek 34               9736600   1         19.95
1601        ABULL-20140423   2      New Blood                  4339605   8         19.95
1601        ABULL-20140423   3      The Bat                    1313111   3         19.95
1601        ABULL-20140423   4      Standard Deviants          6318650   7         27.95
1601        ABULL-20140423   5      Darkman 2                  2519203   7         19.95
7 rows selected.

Using JSON_TABLE, you can project any complex JSON structure into a relational model. The JSON_TABLE query can be exposed as a view—For any consumer of the view, JSON data is accessed like a regular table of rows and columns that contain scalar values. This also makes it possible to use relational tools that do not support the JSON data model.

JSON Data Guide

A common use of JSON_TABLE is to create relationship views that allow users and tools who do not know JSON to process documents. JSON_Dataguide can automatically create views by sampling all JSON documents in a collection and identifying field names and data types. The following example shows how to automatically create a view "order_view". The view definition contains a JSON_Table expression similar to the one mentioned above.

DECLARE
    dg CLOB;  -- This variable stores the derived JSON schema
BEGIN
    -- JSON_Dataguide samples all documents and builds a JSON schema
    SELECT JSON_Dataguide(json_document, dbms_json.FORMAT_HIERARCHICAL) 
    INTO dg 
    FROM orders;
    -- Using this JSON schema, a JSON_TABLE view can be automatically created
    dbms_json.create_view('order_view', 'orders', 'json_document', dg);
END

JSON Generation

Oracle databases can also generate new JSON data from relationships and JSON data. For example, this can generate reports in JSON format.
The following example shows how to connect data in the sample table Employees and Departments and return the results as a new JSON document.

SELECT JSON_ObjectAgg(
            VALUE (
               SELECT JSON_ArrayAgg(
                          JSON_Object()
                      )
               FROM employees e
               WHERE e.department_no = d.department_no
           )
       )
FROM departments d;
-----------------------------------------------
{
  "ACCOUNTING": [
    {"name": "CLARK"},
    {"name": "KING"},
    {"name": "MILLER"}
  ],
  "RESEARCH": [
    {"name": "SMITH"},
    {"name": "JONES"},
    ...
  ]
}

Note that the SQL/JSON generation operator is only added to regular SQL queries, which again demonstrates the good combination of JSON and tables in Oracle databases. You can also insert the generated document into the collection for access by SODA or MongoDB API.

Conclusion: Why use Oracle databases as document storage?

This technical report describes the features of Oracle databases that support schema flexible development using JSON documents stored in collections. Many NoSQL systems today support this development paradigm. Why should organizations choose Oracle databases instead of NoSQL systems?

Oracle databases are built for enterprise-level applications.Many modern enterprise-level relational databases take for granted functionality, but typical NoSQL document storage cannot provide：

• Complex indexing, query optimization and parallel execution
• Transactions that are fully ACID-compliant and are not subject to size/duration limits.
• Advanced security features such as data masking and key management
• Data management features such as compression and data archiving
• Powerful backup function, supports object-level point-in-time recovery
• Built-in procedural language and server-side functions

NoSQL systems often lack the ability to report and analyze operations. As the number and value of JSON documents continues to grow, the demand for cross-document reporting and analysis capabilities is also growing. Previously, developers had to export data from NoSQL and apply complex ETL (extract, transform, and load) processes to use it for data storage that supports flexible reporting. While many NoSQL systems now recognize the need to access data using a table structured format, and some even introduce basic SQL-like languages, Oracle Database now provides full functionality of the mature ISO standard SQL to JSON document storage with its advanced SQL analytics capabilities and scalable parallel SQL infrastructure.

Organizations using NoSQL document storage must also face the potential for data to be orphaned (Data island) The problem of getting up: their relational data is managed by one database, while JSON documents are managed by another database. Using a separate JSON document data store means that even the most basic tasks are the most basic tasks when it is necessary to combine information stored in JSON format with other types of data managed by the organization, usually including relational data.Special application code is also required to be developed and maintained（Need integration）。

Oracle FusionDatabases provide document storage capabilities designed for application developers while allowing these application developers to leverage OracleMatureAll other advantages of the database platform.

This is the end of this article about using Oracle database for JSON-based application development. For more related Oracle database JSON application content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!