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
(id VARCHAR2 (32) NOT NULL PRIMARY KEY,
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 .
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.
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
- Simple Oracle Document Access (SODA)
- Technical Whitepaper : Application Development with SODA.
- Hands-on Application using SODA for REST and Node.js (Oracle Movie Ticketing).
- Oracle Database Documentation .