@OracleIMC Partner Resources & Training: Discover your Modernization options + Reach new potential through Innovation

Brand new ways to consider the World #1 Oracle Database as Document store .

Hany Mohamed
EMEA Cloud Transformation Consultant

Oracle Database supports JavaScript Object Notation (JSON) data natively with relational database features, including transactions, indexing, declarative querying, and views.

JSON data has often been stored in NoSQL databases such as Oracle NoSQL Database and Oracle Berkeley DB. These allow for storage and retrieval of data that is not based on any schema, but they do not offer the attentive consistency models of relational databases . 

In Oracle Database, JSON data is stored using the common SQL data types VARCHAR2, CLOB, and BLOB (unlike XML data, which is stored using abstract SQL data type XMLType). Oracle recommends that you always use an is_json check constraint to ensure that column values are valid JSON instances

CREATE TABLE j_purchaseorder


   date_loaded TIMESTAMP (6) WITH TIME ZONE,

 po_document VARCHAR2 (32767)  CONSTRAINT ensure_json CHECK (po_document IS JSON));

Developers can now work with JSON documents managed by oracle database natively by using SODA API’s which supported across multiple platforms and languages including REST , Java , Node.js , Python , Pl/SQL and Oracle cloud infrastructure . 

  • GeoJSON is JSON objects which generally represent longitude ,altitude , coordinates and geometry . 
Developer can use SQL/JSON function json_Value , json_query , spatial_index , SDO_Geometry and SDO_Distance in PL/SQL package i.e.  compute/calculate the minimum distance between two geometry objects .


  • Text - Availability of Updated documents in index :In the earlier releases, when documents were updated, they could not be searched on until the index was synchronized. Starting Oracle Database 12c Release 2 (12.2), you have the option of keeping the old (possibly, outdated) index entries, so that the document can still be found from its original contents

Database developer can use automatic background index maintenance as moving index data from the update staging area to the main index, defragmenting as it goes while leveraging the mechanism for handling DML requests (inserts, updates, and deletes) on a table with a text index has been updated.Text Wildcard search or a New Wordlist preference (WILDCARD_INDEX) is more efficient, faster and simpler to understand than the previous options. Indexing and storage overhead are less compared to previous options.

  • Property Graph Query Language A new SQL-like declarative language for querying property graph data has been added to the property graph feature of Oracle Spatial and Graph. Property Graph Query Language (PGQL) includes a rich set of graph pattern matching capabilities, WHERE, SELECT, ORDER BY, GROUP BY clauses with support for arithmetic expressions, and MIN, MAX, and COUNT aggregates.

Now with PGQL, developers have a choice of using a declarative language or Java API's to formulate property graph pattern matching and path queries. The SQL-like nature of PGQL increases query expressivity, reduces developers' learning time, facilitates faster and easier query writing, and makes queries simpler for others to read

  • LOB support with In-memory and BigData SQL : Large Objects (Binary and Text) can now be used with Oracle Database In-Memory and Big Data SQL.Users will experience marginal boost in performance while performing LOB operations and using LOB columns in their DML and SQL operations.

All features available now through Oracle Database 18c , 19c ,Oracle Cloud database service and Oracle Autonomous Database for more details check out 

- Simple Oracle Document Access (SODA)

- Technical Whitepaper : Application Development with SODA.

- Working with JSON Whitepaper.

- Hands-on Application using SODA for REST and Node.js (Oracle Movie Ticketing).

- Oracle Database Documentation .

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.