X

All Things Database: Education, Best Practices,
Use Cases & More

Introducing Oracle Autonomous JSON Database for application developers

Beda Hammerschmidt
Architect
This is a syndicated post, view the original post here

Today, Oracle announced the availability of Autonomous JSON Database—a new cloud service built for developers who are looking for an easy to use, cost-effective JSON database with simple NoSQL API's. Autonomous JSON Database provides all the core capabilities of MongoDB along with high performance, simple elasticity, full ACID support and complete SQL functionality.

{WE KNOW JSON}
JSON is extremely popular: what started as a serialization format for JavaScript objects and moved on to the de-facto messaging format for web applications has become the main data model for many new applications—including the database tier.

 
  {
      "name":"San Jose",
      "population":1021795,
      "county":"Santa Clara"    
  }
 
 
  {
      "name":"Atlanta",
      "population":506811,
      "county":["Fulton","DeKalb"]    
  }
 


Developers love JSON because it supports dynamic schemas and hence makes schema changes easy. Instead of normalizing data into a fixed relational schema with tables and columns, developers can use JSON documents to also gain agility on the data tier when making application changes.

{WE MAKE IT EASY TO USE}
Oracle identified the benefits and requirements of JSON very early: in 2014, Oracle Database delivered the first enterprise-class implementation of SQL/JSON - an open standard that Oracle initiated and that has since been adopted by many other commercial and open-source database products.

 

https://cdn.app.compendium.com/uploads/user/e7c690e8-6ff9-102a-ac6d-e4aebca50425/3cf3e953-ae46-4d55-b48c-0f9981b017ef/Image/aab121e2f389f381c3d5b2cc97be2e2f/json_table_combined.png

 

While SQL is a great language for analytics or complex reporting, many developers prefer a simpler and more flexible way to interact with JSON data. Consequently, Oracle added a native, open-source document store API called SODA (Simple Oracle Document Access) for common programming languages including Java, JavaScript and Python. Developing applications with JSON and SODA is as easy with Oracle as it is with NoSQL databases like MongoDB.

 

   soda create cities;
   soda insert cities {"name":"San Jose","population":1021795,"county":"Santa Clara"}
   soda insert cities {"name":"Atlanta","population":506811,"county":["Fulton", "DeKalb"]}

   soda get cities -f {"county":"Fulton"}
   soda get cities -f {"population":{"$gte":1000000}}

Oracle continues to deliver database innovations for JSON with today’s announcement of Autonomous JSON Database, bringing all of the autonomous benefits to JSON application developers.

{WE LEAD AUTONOMOUS}
One really cool thing is that Oracle didn’t create a JSON cloud service from scratch. Autonomous JSON Database is built on the Oracle Autonomous Database foundation. This service provisions new databases in minutes, scales up and down with no downtime for the application, patches databases online, takes automatic backups with point-in-time recovery, provides disaster-recovery capabilities, and has advanced security features. The goal of an autonomous database is zero administration, so that developers can spend more time on their application and less on setting up and managing a database.

{WE GIVE YOU AN AUTONOMOUS CLOUD SERVICE}
Autonomous JSON Database stores JSON documents in a native tree-oriented binary format. This native JSON format is highly optimized for fast reads (avoiding linear scans) and partial updates (reducing redo/undo log sizes). The result is a no-compromise document database providing low latency CRUD operations *and* full ACID consistency (including multi-document transactions); native document API for application development *and* full SQL support for applications; native JSON storage *and* scalable, parallel, in-memory query optimizations. 

 

Autonomous JSON Database provides a wealth of application features not found in most NoSQL databases:

  • built-in machine learning algorithms, spatial queries
  • advanced security features like fine-grained access control
  • a mature server-side procedural language
  • a complete low-code development environment
  • ACID transactions with no time or transaction size limits
  • simple and fast cross-collection joins and/or aggregations
  • intelligent search indexes over entire JSON documents
    And, the list goes on

 

{IT’S AFFORDABLE ...}
Autonomous JSON Database is surprisingly low-cost. This service is designed for application developers to build JSON applications on Oracle, and a real opportunity to tap into all of the features of its Autonomous Database at a very competitive price. Autonomous JSON Database costs 30% less than comparable MongoDB Atlas configurations: $2.74/hr versus $3.95/hr (Dedicated Cluster at M60 tier compared to 8 OCPU's). In practice, Developers can even further lower their cost compared to MongoDB Atlas because Autonomous JSON database is elastic and does not rely on fixed hardware shapes—you can choose any number of CPUs for your Autonomous JSON Database. Autonomous JSON Database's cost includes backup and simple connectivity to BI tools -- both are additonal cost items with MongoDB Atlas.

 

{... YET SCALABLE AND FAST}

The added features of Autonomous JSON database do not come with a performance penalty - on the contrary, when compared with MongoDB Atlas (same setup as used above for pricing) Autonomous JSON database gives you 2x throughput consistently across different workload types and collection sizes. The MongoDB Atlas results were run by MongoDB and published here using the industry-standard YCSB benchmark.

Autonomous JSON Database with 8 OCPUs compared to MongoDB Atlas on M60
Industry-standard Yahoo Cloud Serving Benchmark (YCSB)
Source of MongoDB results: https://www.mongodb.com/atlas-vs-amazon-documentdb/performance as of 8/12/2020

{TRY ONE NOW !}
You can try the new Autonomous JSON Database with 'Always Free' Oracle Cloud account: sign up here

 

In your Oracle Cloud Free Tier, you should first start with creating Autonomous Transaction Processing instance. When you need to add more resources to your instance or production capabilites such as service SLAs, extended backups etc, you can move your Autonomous Transaction Processing instance on free tier directly to the paid version of Autonomous JSON Database with a single-click.

 

If you are wondering whether there are any features in the free version of Autonomous Transaction Processing that might prevent you from moving to Autonomous JSON Database, the answer is 'no'. The data size limit on the free tier is 20GB, and Autonomous JSON Database similarly supports 20GB of non-JSON data.

 

Similary, moving an Autonomous JSON Database (AJD) to a full Autonomous Transaction Processing (ATP) can be done with one single-click and minimal downtime (to restart the database with no 20 GB non-JSON data restrictions). There is no need for any application changes , even the connect strings stay the same.

 

{WHY YOU SHOULD CONSIDER AUTONOMOUS JSON OVER MONGODB ATLAS}

Not only is Autonomous JSON Database cheaper and faster than MongoDB Atlas but it also comes with much more broader enterprise capabilities:

  Autonomous JSON Database MongoDB Atlas
Max Document Size 32 MB 16 MB
Max nested depth of documents 1024 levels 100 levels
Indexes per collection unlimited 64
Compound index fields unlimited 32
Full document index JSON Search Index X
Server-side functions Functions, procedures, triggers Not recommended*
Multi-document transactions Always ACID ACID only upon request via explicit API calls
Transaction duration unlimited 60 seconds default
Transaction size unlimited maximum of 1000 documents*
Aggregation data size unlimited 100 MB RAM + explicit allowDiskUse param
Serverless auto-scaling  X
SQL access over JSON documents X
Comprehensive security
(e.g. Virtual Private Database,
Data Redaction, Custom Database Roles)
X
Low Price $2.74 / hour $3.95 / hour

* recommendations as per MongoDB documentation: link1, link2 

 

{HOW TO GET STARTED WITH AJD: Step by Step}

After login into Oracle Cloud select 'Autonomous JSON Database' in the left menu:

https://cdn.app.compendium.com/uploads/user/e7c690e8-6ff9-102a-ac6d-e4aebca50425/3cf3e953-ae46-4d55-b48c-0f9981b017ef/Image/eed74f3b9c01458f8bdbec253e21aad7/step1.png

This brings you to this screen, press the blue button to create a database

https://cdn.app.compendium.com/uploads/user/e7c690e8-6ff9-102a-ac6d-e4aebca50425/3cf3e953-ae46-4d55-b48c-0f9981b017ef/Image/38f8a93a9f190d6ceb1f63d4bcd63981/step2.png

Give your database a name (and Displayname), make sure 'JSON' in selected.

https://cdn.app.compendium.com/uploads/user/e7c690e8-6ff9-102a-ac6d-e4aebca50425/3cf3e953-ae46-4d55-b48c-0f9981b017ef/Image/cc13edc27cbbfcdbe94c0ca46be1763c/step3.png

On the same screen you also need to provide an 'admin' password. Remember it, you'll need it.

https://cdn.app.compendium.com/uploads/user/e7c690e8-6ff9-102a-ac6d-e4aebca50425/3cf3e953-ae46-4d55-b48c-0f9981b017ef/Image/1b1ab2a1252b6939f7f775f628b24f70/step4.png

Click 'Create Autonomous Database' and you'll see that a new instance is provisioning. 

https://cdn.app.compendium.com/uploads/user/e7c690e8-6ff9-102a-ac6d-e4aebca50425/3cf3e953-ae46-4d55-b48c-0f9981b017ef/Image/06148531dd97ba4357bcaf25bf8b6085/step5.png

This should not take longer than a few minutes. The screen refreshes and you see a green logo - the service is available

https://cdn.app.compendium.com/uploads/user/e7c690e8-6ff9-102a-ac6d-e4aebca50425/3cf3e953-ae46-4d55-b48c-0f9981b017ef/Image/3f3651edfe9371f1fb0db66c276f6e68/step6.png

Click on Tools and chose 'SQL Developer Web'

https://cdn.app.compendium.com/uploads/user/e7c690e8-6ff9-102a-ac6d-e4aebca50425/3cf3e953-ae46-4d55-b48c-0f9981b017ef/Image/3479b57c0a93c66bbbac75e0160b8b68/step7.png

Here you need the 'admin' password. 

https://cdn.app.compendium.com/uploads/user/e7c690e8-6ff9-102a-ac6d-e4aebca50425/3cf3e953-ae46-4d55-b48c-0f9981b017ef/Image/b9acbc50341dfe578b346738b13ddecf/step8.png

 

You now have a web console where you can enter SQL and SODA commands. SODA stands for 'Simple Oracle Document Access' and gives you a simple document-store interface to store JSON documents in collection.  'soda help' gives you an overview of the soda commands.

https://cdn.app.compendium.com/uploads/user/e7c690e8-6ff9-102a-ac6d-e4aebca50425/3cf3e953-ae46-4d55-b48c-0f9981b017ef/Image/9fed4c2b9aa010bf884f7ca54ea7b426/step9.png

 

Type the following to create a collection 'cities' and insert two JSON documents. Note that they are a little different. The first record assumes that a city belongs to one county. But some cities belong to multiple counties. This is why the second document uses an array. Run the following commands to create the collection and insert two documents:

   soda create cities;
   soda insert cities {"name":"San Jose","population":1021795,"county":"Santa Clara"}
   soda insert cities {"name":"Atlanta","population":506811,"county":["Fulton", "DeKalb"]}

We can now query the collection to find documents matching a search/filter criteria. We call this 'Query By Example' or short QBE. The first QBE looks for cities in the county of 'Fulton':

   soda get cities -f {"county":"Fulton"}  

The second QBE selects all cities with a population greater than 250000. It selects both documents.

   soda get cities -f {"population":{"$gt":250000}}

In these examples we used a console to enter SODA commands. Typically, you would use SODA directly from a programming language. We do have SODA drivers for Java, JavaScript (nodeJS), Python, REST, Pl/Sql and ODPI-C.

With the JSON data stored in an Oracle Database it is also possible to use SQL to access the very same data. First, let's describe the collection

   describe cities;

As one can see the JSON collection is backed by a regular table. The JSON data is stored in a binary representation optimized for fast reads and piece-wise updates. In order to convert it to a JSON string we use JSON_SERIALIZE.

   select JSON_Serialize(JSON_Document) from cities;

With JSON_Table it is possible to unnest the JSON data and project it to relational columns and rows. Please note that the two JSON documents generate 3 rows as one city has two counties.

   select j.* from cities NESTED json_document 
            COLUMNS (name, population number, 
              NESTED county[*] 
              COLUMNS(countyName PATH '$')) j;

Going from a relational representation back to JSON is similarly easy. All we do is add one (or more) JSON generation functions to a query. In the following we are generating an array of all city name. 

   select JSON_ArrayAgg(c.json_document.name) from cities c;

Autonomous JSON Database is part of the Oracle Autonomous Database family. Autonomous JSON Database shares all of the core features for automation, lifecycle management, security, availability, scalability and elasticity with all other Autonomous Database services and is now extending all of the autonomous benefits to JSON application developers.

Please visit Oracle Autonomous JSON Database for more information.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.