Oracle JSON Functionality
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose This page is a single-page collection of links to the new JavaScript Object Notation (JSON) functionality added as new features to Database 12.1.0.2.0.
Dependencies
STANDARD    
 
JSON Table and Column
Create table and constraint CREATE TABLE j_purchase_order (
doc_id RAW (16) NOT NULL,
date_loaded TIMESTAMP(6) WITH TIME ZONE,
po_document CLOB);

SELECT DISTINCT defer, condition
FROM cdef$;

ALTER TABLE j_purchase_order
ADD CONSTRAINT ensure_json
CHECK (po_document IS JSON);

SQL> SELECT owner, object_name, object_type
  2  FROM dba_objects
  3  WHERE object_name like '%JSON%'
  4* ORDER BY 2,1
SQL> /

OWNER   OBJECT_NAME           OBJECT_TYPE
------- --------------------- ------------
PUBLIC  ALL_JSON_COLUMNS      SYNONYM
SYS     ALL_JSON_COLUMNS      VIEW
PUBLIC  CDB_JSON_COLUMNS      SYNONYM
SYS     CDB_JSON_COLUMNS      VIEW
PUBLIC  DBA_JSON_COLUMNS      SYNONYM
SYS     DBA_JSON_COLUMNS      VIEW
SYS     DBMS_FEATURE_JSON     PROCEDURE
SYS     INT$DBA_JSON_COLUMNS  VIEW
PUBLIC  USER_JSON_COLUMNS     SYNONYM
SYS     USER_JSON_COLUMNS     VIEW

desc int$dba_json_columns

desc dba_json_columns

SELECT owner, table_name, column_name, format, data_type
FROM dba_json_columns;

ALTER TABLE j_purchase_order DROP CONSTRAINT ensure_json;

-- once a json document is inserted this constraint will fail to create
ALTER TABLE j_purchase_order
ADD CONSTRAINT ensure_json
CHECK (po_document IS JSON STRICT);

ALTER TABLE j_purchase_order
ADD CONSTRAINT ensure_json_strict
CHECK (po_document IS JSON STRICT);

SELECT DISTINCT defer, condition
FROM cdef$;
 
JSON Document DML
Insert

Note the DBNonsense entry intentionally put in to prove to the NOSentience crowd that we can do schemaless too. Now let's see them do LDAP and Point-in-Time recovery
INSERT INTO j_purchase_order VALUES (SYS_GUID(), SYSTIMESTAMP,
  '{"PONumber" : 1600,
    "Reference" : "SCATZ-20140421",
    "Requestor" : "Safra Catz",
    "User" : "dmorgan",
    "CostCenter" : "A01",
    "ShippingInstructions" : {"name" : "Lawrence Ellison",
                              "address": {"street" : "500 Marine Dr.",
                              "city" : "Redwood Shores",
                              "state" : "CA",
                              "zipCode" : 95608,
                              "country" : "United States of America"},
                              "phones" : [{"type" : "Office", "number" : "650-506-7000"},
                                         {"type" : "Mobile", "number" : "415-555-1234"}]},
    "Special Instructions" : null,
    "AllowPartialShipment" : true,
    "LineItems" : [{"ItemNumber" : 1,
                    "Part" : {"Description" : "Hawaiian Island: Lanai",
                              "UnitPrice" : 99.95,
                              "UPCCode" : 13131092899},
                              "Quantity" : 1.0},
                   {"ItemNumber" : 2,
                    "Part" : {"Description" : "Hotels on Boardwalk",
                              "UnitPrice" : 19.95,
                              "UPCCode" : 85391628927},
                              "Quantity" : 4.0}]}');

col date_loaded format a36

SELECT * FROM j_purchase_order;
 
Header
  SELECT doc_id, date_loaded
FROM j_purchase_order
WHERE JSON_EXISTS (po_document, '$[*].PONumber' TRUE ON ERROR);
 
 
Header
   
 

Related Topics
JSON Object Expressions
JSON_QUERY
JSON_TABLE
JSON_TEXTCONTAINS
JSON_VALUE

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved