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.
- Create a JSON column with an
is json format oson
check constraint - Insert JSON data into the column, and
- Query the JSON data
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.
-
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.
-
Create an ORDS-enabled user.
-
Connect to ORDS using the MongoDB shell.
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.
- 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.
- 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
- Persistent default collection metadata.
- A table for storing the collection in the database schema to which your SODA client is connected.
Limitations on the Use of SODA and JSON
- 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).
Parent topic: Features for Experienced Database Users