Use JSON Documents with Autonomous AI Database on Dedicated Exadata Infrastructure

Autonomous AI 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 AI 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 AI 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 inAutonomous AI Database in Oracle Database 19c JSON Developer’s Guide or Oracle Database 26ai JSON Developer’s Guide 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 AI Database to store JSON data, you can take advantage of all the features available in your Autonomous AI 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 19c or Oracle Database 26ai 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.

See Use MongoDB API with Oracle Autonomous AI Database for instructions on enabling and using the MongoDB API with Autonomous AI Database.

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 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 AI Database.

• Go to the Details page of the Autonomous AI Database. See View Details of a Dedicated Autonomous AI 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 AI 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 AI 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 AI Database to learn how to use the default and customized collection metadata.

Limitations on the Use of SODA and JSON

Autonomous AI 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 in Oracle Database 19c or Oracle Database 26ai for more information.

• Automatic indexing is not supported for SODA query-by-example (QBE).

Related Content

Use MongoDB API with Oracle Autonomous AI Database