JSON Is Part of Oracle 12c
One of the more-exciting features (at least to me) of Oracle 12c is the support for JSON now built into the database.
What is JSON?
JSON (JavaScript Object Notation) is a language-independent open data format (see www.json.org for more). JSON stores text in name-value pairs. Originally usedin JavaScript, JSON is now also in: Java, R, .NET, PHP, Python, Node.js, and Oracle. JSON is most-often used for data interchange but is frequently used to pass data to and from REST-style web services. Increasingly JSON is becoming favored for big-data persistence.
12c (12.1.0.2) and JSON
The Oracle 12c patch-set 2 (12.1.0.2) added JSON. JSON documents are stored as VARCHAR2, CLOB, or BLOB data type. JSON data works with all existing Oracle features including SQL and Analytics. Oracle 12c supports path-based queries of JSON data stored in the database using JSON Path Language and JSON Path Expressions. In addition, JSON may be used in SQL via SQL/JSON views and JSON documents may be indexed.
JSON-XML Similarities
JSON is text only, just like XML and thus is an excellent vehicle for data interchange. JSON and XML are “human readable” and “self-describing” (sort of…). JSON and XML are both hierarchical (data sets nested within data sets). Finally, JSON and XML offer validation capability; XML’s is more mature and capable today.
JSON-XML Dissimilarities
XML is verbose, JSON is shorter (but only slightly). The reduced size is largely because JSON has no end tags; end tags are required in XML. So, JSON is quicker to read and write since it is simpler and briefer. Reading XML documents requires “walking the DOM” – JSON does not. In addition, JSON works more easily and is faster than XML when working with AJAX. Finally, XML documents must be tested for “well-formed”-ness before processing (the reason often cited for XML performance issues).
JSON Syntax
Data is stored using comma-delimited name/value pairs as follows:
Field name/key (surrounded by double quotes)
Colon “:”,
Value (double quotes surround strings, numeric values unquoted, boolean true/false/null
Objects are included inside curly braces “{“ & “}”
{"lastName":"King"}
Arrays of objects use brackets “[“ & “]” and commas
[ {"lastName": "King"},
{"lastName": "Manzo"} ]
XML File
Here is an example of a simple XML file:
<?xml version="1.0"?>Learning XML Eric T. Ray O'Reilly XML Bible Elliotte Rusty Harold IDG Books XML by Example Sean McGrath
JSON File
Here is an example of a JSON file containing the same data as the XML file above. Note, does not seem much simpler…
{"myBooks":
[ {"book": {
"name":"Learning XML",
"author":"Eric T. Ray",
"publisher":"O'Reilly" }
},
{"book": {
"name":"XML Bible",
"author":"Elliotte Rusty Harold",
"publisher":"IDG Books" }
},
{"book": {
"name":"XML by Example",
"author":"Sean McGrath",
"publisher":"Prentice-Hall" }
}
]}
Oracle as JSON Data Store
JSON documents are stored in the database using existing data types VARCHAR2, CLOB and BLOB for character mode JSON (rather than creating a new datatype like Oracle used for XML) . In addition, external JSON data sources (including those in HDFS file system) are accessible through external tables. Using JSON queries JSON data is available to SQL via relational views based upon JSON_TABLE. To aid performance Oracle JSON documents may be indexed; JSON paths may use functional indexes or CTXX indexes.
JSON SQL
JSON content is accessible from SQL and may be treated as JSON using:
JSON conditional operators
JSON functions
JSON operators and functions use JSON Path language to navigate JSON objects
JSON Conditionals
JSON content is accessible from SQL via new condition operators:
IS JSON
Validates JSON, often in used in CHECK constraint and/or WHERE (IS / IS NOT)
JSON_EXISTS
Returns true if JSON path exists in document.
JSON_TEXTCONTAINS
Returns true if text string is found in JSON property values (requires Oracle Text).
JSON Functions
JSON content is also accessed via new functions:
JSON_VALUE
Used to query a scalar value from a JSON document.
JSON_QUERY
Used to query all or part of a JSON document.
JSON_TABLE
Used to query JSON document and create relational-style columns.
IS JSON – IS NOT JSON
IS JSON returns TRUE if specified expression is a valid JSON document.
**expr** IS JSON | IS NOT JSON FORMAT JSON STRICT|LAX WITH|WITHOUT UNIQUE KEYS
- IS true if valid JSON, IS NOT true if not JSON
- FORMAT JSON (only required for BLOB data)
- STRICT / LAX strictness level required for true
- WITH / WITHOUT if unique keys are required
IS JSON Check Constraint
create table deptj (id raw(16) not null, dept_info clob constraint deptjson check (dept_info is json) ); create table deptj (id raw(16) not null, dept_info clob constraint deptjson check (dept_info is json strict));
IS JSON in WHERE
select id,dept_info from deptj where dept_info is json select id,dept_info from deptj where dept_info is json strict; select id,dept_info from deptj where dept_info is json format json strict;
JSON and DML
Inserting (or updating) data into JSON columns is accomplished using standard SQL, it’s just that the text being inserted/updated is JSON.
insert into deptj values
(sys_guid(),
'{"departments":{
"DEPTNO": 10, "DNAME": "ACCOUNTING", "LOC": "NEW YORK",
"deptemps": [
{ "EMPNO": 7782,
"ENAME": "CLARK",
"JOB": "MANAGER",
"MGR": 7839,
"HIREDATE": "09-JUN-81",
"pay":{
"SAL": 2450,
"COMM": null},
"DEPTNO": "10" },
/* more */
Simple JSON Query
When JSON data is queried using regular SQL, the JSON data is returned as is:
select dept_info
from deptj;
DEPT_INFO
---------------------------------------------
{"departments":{
"DEPTNO": 10,
"DNAME": "ACCOUNTING",
"LOC": "NEW YORK",
"deptemps": [
{
"EMPNO": 7782,
"ENAME": "CLARK",
**** more ****
JSON Path
SQL using the new JSON functions do so using a special JSON path syntax.
$.jsonpath Identifies path in JSON document
$. (required)
jsonpath (describes part of JSON document to be searched, if omitted entire document returned
JSON path is used by JSON_QUERY, JSON_VALUE, JSON_TABLE, JSON_EXISTS, JSON_TEXTCONTAINS.
JSON_VALUE The JSON_VALUE function finds a value in a JSON document and returns it as VARCHAR2 or NUMBER to SQL. JSON_VALUE (expr | expr FORMAT JSON,
'$.jsonpath’
RETURNING VARCHAR2(n) | NUMBER(n,n)
ERROR | NULL | DEFAULT xxx ON ERROR )
Query with JSON_VALUE
select json_value(dept_info, '$.departments.DNAME') from deptj; DNAME ----------------- ACCOUNTING RESEARCH SALES OPERATIONS
JSON_VALUE returns scalar values from JSON data
select json_value(dept_info , '$.departments.DNAME') from deptj; select json_value(dept_info , '$.departments.deptemps[0].ENAME') from deptj; select dept_info from deptj where json_value(dept_info,
'$[0].departments.DEPTNO') = '10'
JSON_QUERY
JSON_QUERY finds values in a JSON document and returns a character string.
JSON_QUERY(expr | expr FORMAT JSON, ‘$.jsonpath’ RETURNING VARCHAR2(n) PRETTY ASCII
WITH | WITH CONDITIONAL | WITH UNCONDITIONAL WRAPPER | ARRAY WRAPPER ERROR | NULL | DEFAULT xxx ON ERROR )
Use PRETTY to pretty-print output
Use ASCII to escape non-ASCII characters
WRAPPER needed if multiple values or scalar returned
Query examples using JSON_QUERY:
select json_query(dept_info, '$.departments.DEPTEMPS.ENAME' with unconditional wrapper) from deptj dj; select json_query(dept_info, '$.departments.DEPTEMPS.ENAME' pretty with wrapper) from deptj dj; select json_query(dept_info, '$.departments.DEPTEMPS[0].ENAME' pretty with conditional wrapper) from deptj dj;
JSON_TABLE
JSON_TABLE maps JSON data into relational rows and columns. JSON_TABLE is frequently used in dynamic queries (in FROM clause) and when used in a view allow “normal SQL” access to JSON data.
JSON_TABLE ( expr | expr FORMAT JSON,'$.jsonpath’
ERROR | NULL | DEFAULT xxx ON ERROR
COLUMNS (
colname1 datatype
EXISTS PATH '$.jsonpath’
ERROR|NULL|DEFAULT xxx ON ERROR,
colname2 datatype FORMAT JSON
jsonquerywrapper PATH '$.jsonpath’
ERROR|NULL|DEFAULT xxx ON ERROR,
colname3 datatype PATH '$.jsonpath’
ERROR|NULL|DEFAULT xxx ON ERROR,
NESTED PATH '$.jsonpath' COLUMNS (...),
colname4 FOR ORDINALITY )
Here is an example query using a JSON_TABLE:
select dname,ename,job,sal from deptj, json_table(dept_info,'$.departments' columns (dname varchar2(15) path '$.DNAME' ,nested path '$.deptemps[*]' columns (ename varchar2(20) path '$.ENAME' ,job varchar2(20) path '$.JOB' ,nested path '$.pay' columns (sal number path '$.SAL') ) )); DNAME ENAME JOB SAL ------------ ------- -------- ---------- ACCOUNTING CLARK MANAGER 2450 ACCOUNTING KING PRESIDENT 5000 **** more ****
JSON and Indexing JSON function/expression based indexes may be “b-tree” (normal) or bitmap. JSON full-search context indexes may also be created Function/Expression-based indexes are created as follows: create unique index deptj_ix on deptj (json_value (dept_info,'$.departments.DEPTNO')); create bitmap index deptj_emp_ix on deptj (json_value(dept_info, '$.departments.DEPTEMPS.EMPNO'));
Full-search context index are created as follows:
create index deptj_ctx_ix
on deptj (dept_info)
indextype is ctxsys.context parameters ('section group CTXSYS.JSON_SECTION_GROUP sync (on commit)’);
Context indexes are not just for TEXTCONTAINS they may also be used for JSON_VALUE and JSON_EXISTS tests.
Wrapping it all Up
Oracle 12c support for JSON is timely and useful. Furthermore, JSON is coming to an application near you soon and you need to be ready.
JSON is the most-common mechanism for interacting with AJAX, becoming the most-common mechanism for mobile/web service data (especially REST/HTTP API based services), and JSON is the cornerstone of several “big data” data stores.
Take the time to learn JSON, JSON syntax, and Oracle’s implementation.
