Use JSON Documents with Autonomous Database on Dedicated Exadata Infrastructure

Autonomous Database on Dedicated Exadata Infrastructure supports JavaScript Object Notation (JSON) data natively with relational database features, including transactions, indexing, declarative querying, and views. In Autonomous Databases, JSON documents can coexist with relational data.

Unlike relational data, JSON data can be stored in the database, indexed, and queried without any need for a schema that defines the data. In general, you will perform the following tasks when working with JSON data in Autonomous Database:
  1. Create a JSON column with an is json format oson check constraint
  2. Insert JSON data into the column, and
  3. Query the JSON data
See Work with JSON Documents in Autonomous Database for detailed instructions with code examples.

Note:

If you use SODA to access collections in Oracle Database 19c, Oracle recommends that you use the instant client for Oracle Database 21c or later, in order to smooth migration to the use of JSON data type when your database is upgraded to release 21 or greater.

SQL and PL/SQL APIs

When you use an Autonomous Database to store JSON data, you can take advantage of all the features available in your Autonomous Database. You can combine your JSON data with non-JSON data. You can access JSON data stored in the database the same way you access other database data, including using Oracle Call Interface (OCI), Microsoft .NET Framework, and Java Database Connectivity (JDBC). See JSON in Oracle Database to learn using SQL and PL/SQL APIs to work with JSON documents.

Oracle API for MongoDB Support

Oracle Database API for MongoDB translates the MongoDB wire protocol into SQL statements that are executed by Oracle Database. It lets developers who have MongoDB skill sets write JSON document-store applications for Oracle Database that use drivers and tools that understand the MongoDB protocol. See Overview of Oracle Database API for MongoDB in Oracle Database API for MongoDB for a detailed overview of this API.

To use the MongoDB API with an Autonomous Database, you must install and configure customer managed Oracle REST Data Services (ORDS) separately, and the version of ORDS must be 22.3 or later. See Oracle API for MongoDB Support in Oracle REST Data Services Installation and Configuration Guide for the architectural concepts of this API.

Perform the below listed tasks to enable Oracle Database API for MongoDB using a customer managed ORDS on an Autonomous Database.

  1. Install and configure ORDS using the ords install adb command.

    Refer to Installing and Configuring Customer Managed ORDS on Autonomous Database in Oracle REST Data Services Installation and Configuration Guide for more details.

  2. Create an ORDS-enabled user.

  3. Connect to ORDS using the MongoDB shell.

For a demonstration of the above steps, refer to Getting Started in Oracle REST Data Services Installation and Configuration Guide.

Simple Oracle Document Access (SODA)

Oracle also provides a family of Simple Oracle Document Access (SODA) APIs for access to JSON data stored in the database. SODA is designed for schemaless application development without knowledge of relational database features or languages such as SQL and PL/SQL. It lets you create and store collections of documents in Oracle Database, retrieve them, and query them, without needing to know how the documents are stored in the database. For more information about SODA see Oracle as a Document Store and Overview of SODA.

There are several implementations of SODA:
  • SODA for REST: Representational state transfer (REST) requests perform collection and document operations, using any language capable of making HTTP calls.
  • SODA for Java: Java classes and interfaces represent databases, collections, and documents.
  • SODA for PL/SQL: PL/SQL object types represent collections and documents.
  • SODA for C: Oracle Call Interface (OCI) handles represent collections and documents.
  • SODA for Node.js: NoSQL style SODA APIs are used to insert, query, and retrieve SODA documents. Objects such as SodaDatabase, SodaCollection, SodaDocument, SodaDocumentCursor, and SodaOperation represent the databases, collections, documents, document cursor, and read-write operations respectively.
  • SODA for Python: NoSQL-style cx_Oracle methods are used to insert, query, and retrieve SODA documents. Objects such as SodaDatabase, SodaCollection, SodaDocument, SodaDocumentCursor, and SodaOperation represent the databases, collections, documents, document cursor, and read-write operations respectively.
You can download the SODA drivers from the Details page of an Autonomous Database.
  • Go to the Details page of the Autonomous Database. See View Details of a Dedicated Autonomous Database.
  • Open the Tools tab.
  • Locate the SODA Drivers tile, and click Download SODA Drivers.
  • Download the SODA drivers of your choice from the displayed list of drivers.

SODA Collection Metadata

A SODA collection is a set of documents that is backed by an Autonomous Database table or view. By default, creating a SODA document collection creates the following in Oracle Database:
  • Persistent default collection metadata.
  • A table for storing the collection in the database schema to which your SODA client is connected.
You can customize collection metadata to obtain different behavior from those provided by default. The only metadata you can customize in Autonomous Database is the document-key generation method and the content media type. In particular, you cannot change the SQL data type of the column used to store JSON content (the content column). See SODA Collection Metadata on Autonomous Database to learn how to use the default and customized collection metadata.

Limitations on the Use of SODA and JSON

Autonomous Database supports SODA and JSON, with the following restrictions:
  • Automatic indexing is not supported for SQL and PL/SQL code that uses the SQL/JSON function json_exists. See SQL/JSON Condition JSON_EXISTS for more information.
  • Automatic indexing is not supported for SODA query-by-example (QBE).