OCI Autonomous JSON Database stores JSON natively, provides the SODA API for schema-free document operations, supports the MongoDB wire protocol so existing MongoDB drivers connect without modification, and allows SQL queries against JSON when you need relational-style aggregations. This post covers Terraform provisioning, SODA access patterns, JSON Duality Views, and the MongoDB migration path.

Step 1: Terraform Provisioning

resource "oci_database_autonomous_database" "orders_json_db" {
  compartment_id           = var.compartment_id
  db_name                  = "ORDERJSON"
  display_name             = "orders-json-database"
  db_workload              = "AJD"
  db_version               = "23ai"
  compute_model            = "ECPU"
  compute_count            = 4
  data_storage_size_in_tbs = 1

  is_auto_scaling_enabled     = true
  is_access_control_enabled   = true
  whitelisted_ips             = [var.app_subnet_cidr]
  subnet_id                   = var.private_subnet_id
  nsg_ids                     = [var.adb_nsg_id]
  is_mtls_connection_required = true
  is_mongodb_api_enabled      = true

  admin_password = var.adb_admin_password
  kms_key_id     = var.vault_key_id
  vault_id       = var.vault_id

  defined_tags = { "Operations.Environment" = "production", "Operations.ManagedBy" = "terraform" }
}

output "ajd_ocid"        { value = oci_database_autonomous_database.orders_json_db.id }
output "ajd_mongodb_url" { value = oci_database_autonomous_database.orders_json_db.connection_urls[0].mongo_db_url }

Step 2: NSG Rule for Application Access

resource "oci_core_network_security_group_security_rule" "app_to_ajd" {
  network_security_group_id = var.adb_nsg_id
  direction                 = "INGRESS"
  protocol                  = "6"
  source_type               = "CIDR_BLOCK"
  source                    = var.app_subnet_cidr

  tcp_options {
    destination_port_range { min = 1522; max = 1522 }
  }

  description = "Allow app tier mTLS connections to Autonomous JSON Database"
}

# MongoDB wire protocol listens on port 27017
resource "oci_core_network_security_group_security_rule" "app_to_ajd_mongodb" {
  network_security_group_id = var.adb_nsg_id
  direction                 = "INGRESS"
  protocol                  = "6"
  source_type               = "CIDR_BLOCK"
  source                    = var.app_subnet_cidr

  tcp_options {
    destination_port_range { min = 27017; max = 27017 }
  }

  description = "Allow MongoDB driver connections to AJD MongoDB API endpoint"
}

Step 3: Performance Alarm

resource "oci_monitoring_alarm" "ajd_cpu_high" {
  compartment_id        = var.compartment_id
  display_name          = "ajd-cpu-utilization-high"
  is_enabled            = true
  metric_compartment_id = var.compartment_id
  namespace             = "oci_autonomous_database"
  query                 = "CpuUtilization[5m]{databaseId = 'AJD_OCID'}.mean() > 80"
  severity              = "WARNING"
  pending_duration      = "PT10M"
  destinations          = [var.ops_notification_topic_id]
  body                  = "Autonomous JSON Database CPU above 80% for 10 minutes."
}

resource "oci_monitoring_alarm" "ajd_storage_high" {
  compartment_id        = var.compartment_id
  display_name          = "ajd-storage-utilization-high"
  is_enabled            = true
  metric_compartment_id = var.compartment_id
  namespace             = "oci_autonomous_database"
  query                 = "StorageUtilization[1h]{databaseId = 'AJD_OCID'}.mean() > 75"
  severity              = "WARNING"
  pending_duration      = "PT1H"
  destinations          = [var.ops_notification_topic_id]
  body                  = "AJD storage above 75%. Document collections are growing faster than expected."
}

SODA API Access Patterns

SODA collections are schema-free: you insert JSON documents with any structure and Oracle stores them in an internal table with an auto-generated key. The SODA API is available through SQL/PL/SQL, REST via ORDS, and native drivers for Java, Node.js, and Python. A collection in SODA corresponds to a table in the underlying Oracle schema, which means you can query SODA collections with SQL/JSON functions alongside relational tables in the same query.

JSON Duality Views

JSON Duality Views are the bridge between document and relational access. They expose existing relational tables as JSON documents. Insert a document through the view, and Oracle transactionally updates the underlying normalized tables. Query the view, and Oracle returns a JSON document assembled from multiple tables. This is the recommended approach for teams moving from MongoDB who have analytics requirements that SQL handles better than document queries. Both access patterns see the same data without any transformation pipeline.

MongoDB Wire Protocol

The MongoDB API endpoint on port 27017 accepts connections from any MongoDB 5.0 or later driver. Migrating a MongoDB application means changing only the connection string to the AJD MongoDB API URL from the connection_urls output. Collections map to SODA collections. Aggregation pipelines, find queries, insert and update operations all work without code changes. Oracle translates the MongoDB wire protocol operations to SQL internally.

Operational Notes

Enable the MongoDB API in Terraform with is_mongodb_api_enabled = true. This enables the MongoDB wire protocol listener and exposes a MongoDB-format connection URL in the database outputs. The endpoint uses TLS and requires authentication. The MongoDB connection URL from the AJD output already includes the correct hostname and port format for MongoDB drivers.

JSON documents in SODA collections are indexed automatically with an Oracle full-text search index. For selective queries on specific JSON fields, create functional indexes on the JSON path expressions using JSON_VALUE. Without field-level indexes, queries that filter on specific JSON attributes trigger a full collection scan, which is acceptable for small collections but becomes expensive as collections grow to millions of documents.

Regards,
Osama

#OCI #OracleCloud #AutonomousJSONDatabase #JSON #SODA #MongoDB #Terraform #IaC #TechBlog #Oracle #CloudDatabase #DocumentStore #OracleCloudInfrastructure #JSONDualityView #NoSQL #23ai #DataModeling #DocumentDB #CloudNative #DatabaseMigration