Oracle Database Speaks JSON Now
By Yuli Vasiliev
Starting with release 12.1, Oracle Database natively supports JavaScript Object Notation (JSON), providing SQL access to JSON data. This article covers what JSON support in Oracle Database 12.1 includes and what it doesn’t, explaining how you can access and manipulate JSON data from within an SQL query.
JSON in Oracle Database in a Nutshell
When support for a data format is added to the database, you might above all be interested in how storage and retrieval of that data are handled. Also, you might want to know how external sources of that data can be accessed from within the database.
Speaking about storage, it is important to emphasize that Oracle Database 12.1 does not offer any data type designed specially for JSON. Instead, you can store JSON using regular SQL data types: VARCHAR2, CLOB, and BLOB. To make sure that a column contains valid JSON data, you’re supposed to use an IS JSON check constraint applied to that column.
Turning to retrieval, it is interesting to note that SQL access to JSON data is based on using Oracle SQL functions such as JSON_TABLE, and conditions for JSON such as IS JSON, as well as a dot notation to drill down into a JSON document. When it comes to accessing an external JSON data source from within a SQL query, you can use the HTTPURITYPE function, provided that the data source is available through HTTP.
The figure below presents a simplified view of how storage and retrieval of JSON are handled in Oracle Database:
Figure 1: A simplified diagram of how SQL access to JSON data works in Oracle Database
Of course, JSON support in Oracle Database provides much more features than those you can see in the above figure. Thus, as you will see later in this article, JSON_TABLE is not the only Oracle SQL function you can use to access JSON data, and Oracle SQL conditions for JSON are not limited only to the IS JSON check constraint shown here. Also, you’ll look at some examples of using the dot notation to query JSON data and learn how to take advantage of Oracle JSON path expression syntax.
Storing and Accessing JSON Data in Oracle Database
Let’s start by creating a relational table with a column for storing JSON data. As mentioned, a JSON column can be either VARCHAR2, CLOB, or BLOB with an IS JSON check constraint applied to it. So, you might create the table as follows:
DROP TABLE json_docs PURGE;
CREATE TABLE json_docs (
id RAW(16) NOT NULL,
jsondoc CLOB,
CONSTRAINT json_chk CHECK (jsondoc IS JSON)
);
As you can see, the only thing that indicates that the jsondoc column of CLOB is supposed to hold JSON data is the IS JSON check constraint added to that column. This constraint ensures that the data being inserted into the column is syntactically correct JSON. Note that there is no schema that defines the data. What this means in practice is that you can insert into the jsondoc column dissimilar JSON documents, and that won’t be prevented, provided they contain well-formed JSON data.
As of practice though, it’s hard to imagine that you might need to hold documents of different structure within the same column. It should be noted however, that although checking against a schema is not provided during data insertion, mapping a kind of schema onto the data that has already been inserted into such a column can be done with the help of Oracle SQL function JSON_TABLE. This function allows you to define a relational view over JSON data, exposing only the data that match the mapping definition of the view. This will be discussed in more detail in the Projecting JSON Data Relationally section later in this article.
Continuing with the example, let’s insert a couple of simple JSON documents into the json_docs table. In the following statement, you insert a JSON representation of an object that describes a department with an array of employee records included:
INSERT INTO json_docs
VALUES (SYS_GUID(),
'{
"deptId": 310,
"deptName": "IT Research",
"employees":
[
{
"firstName": "Maya",
"lastName": "Silver",
"age": 32
},
{
"firstName": "John",
"lastName": "Polonsky",
"age": 25
}
]
}'
);
And then another insertion, so that you have at least two JSON documents to play with:
INSERT INTO json_docs
VALUES (SYS_GUID(),
'{
"deptId": 320,
"deptName": "Advertising",
"employees":
[
{
"firstName": "Tom",
"lastName": "Akopyan",
"age": 38
},
{
"firstName": "Jeff",
"lastName": "Jemison",
"age": 41
}
]
}'
);
There are several ways in which you can access JSON data stored in an Oracle database. In most cases, you will use an Oracle SQL function for JSON, such as JSON_VALUE, JSON_QUERY, or JSON_TABLE. For simple use cases though, you can use a dot-notation syntax: an alias of the table, the name of the column containing JSON data, and one or more names (keys) that identify the path to the JSON value you want to access – where all the names are separated by a dot. Using this syntax allows referencing to particular values in a JSON document directly without using Oracle SQL functions.
Note: As defined by RFC 4627, a JSON value is a string, number, boolean, null, object, or array. With the dot-notation syntax, you can query JSON values of any of these types. It is interesting to note however, the return value of a dot-notation query is always a string (VARCHAR2).
The following query is an example of simple dot-notation access to JSON data:
SELECT d.jsondoc.deptId, d.jsondoc.employees FROM json_docs d;
The above query should produce the following output:
DEPTID EMPLOYEES
------ ---------
310 [{"firstName":"Maya","lastName":"Silver","age":32},{"firstName":"John","lastName":"Polonsky","age":25}]
320 [{"firstName":"Tom","lastName":"Akopyan","age":38},{"firstName":"Jeff","lastName":"Jemison","age":41}]
As you can see, using the dot-notation does not limit you to a single item in the select-list of a query. Moreover, the above example illustrates that with the dot-notation you can query JSON values of different types (both scalar and non-scalar) within the same query.
However, the dot-notation has serious limitations when it comes to using Oracle JSON path expressions. For example, you won’t be able to refer to the particular elements of an array using the dot-notation. So, if you issue the following query:
SELECT d.jsondoc.employees[0].age FROM json_docs d;
It will fail with the following error:
ORA-00923: FROM keyword not found where expected
00923. 00000 - "FROM keyword not found where expected"
This is where Oracle SQL functions for JSON come to the rescue. Turning back to our example, suppose you need to calculate the average age of the employees described by the first element in an employees array over all the departments. This can be done using the JSON_VALUE function with the following query:
SELECT AVG(json_value(jsondoc, '$.employees[0].age' RETURNING NUMBER)) FROM json_docs;
The above query should return the following result:
35
In this example, the Oracle JSON path expression passed to JSON_VALUE as argument is followed by a RETURNING clause. This clause is used to explicitly specify the data type of the value returned by the function, including the ability to optionally specify precision and scale designators for NUMBER and a length for VARCHAR2. Aside from the RETURNING clause used here, some Oracle SQL functions and Oracle SQL conditions for JSON allow you to use the wrapper and error handling clauses. An example of using an error handling clause with the JSON_TABLE function will be provided in the Optional Error Handling section later in this article.
Projecting JSON Data Relationally
The need for projecting JSON data relationally may arise when you want to join it to the data from another source. As mentioned, JSON_TABLE allows you to shred the result of a JSON expression evaluation into relational rows, thus producing a virtual relational table that can be then joined with another table or tables within the same query.
You may find it useful to define a relational view over the JSON data you access frequently. In the following example, you define such a view over JSON_TABLE that queries JSON content stored in the json_docs table.
CREATE OR REPLACE VIEW dept_emps_v AS
SELECT d.*
FROM json_docs dept,
JSON_TABLE(dept.jsondoc, '$'
COLUMNS (deptId NUMBER(3) PATH '$.deptId',
deptName VARCHAR2(50 CHAR) PATH '$.deptName',
NESTED PATH '$.employees[*]'
COLUMNS (
age NUMBER(3) PATH '$.age',
firstName VARCHAR2(50 CHAR) PATH '$.firstName',
lastName VARCHAR2(50 CHAR) PATH '$.lastName'
))) d;
Notice the use of a NESTED PATH clause to project the employees array elements. The point is that the department document discussed here represents data using a master-detail relationship in which a department may have multiple employees. The virtual table defined in JSON_TABLE flattens this hierarchy, producing the rows with additional information in each row – just like a join query does
Once the dept_emps_v view has been created, you can query it like any other relational view or table:
SELECT * FROM dept_emps_v;
DEPTID DEPTNAME AGE FIRSTNAME LASTNAME
-----------------------------------------------
310 IT Research 32 Maya Silver
310 IT Research 25 John Polonsky
320 Advertising 38 Tom Akopyan
320 Advertising 41 Jeff Jemison
Of course, the relational representation of the department JSON document provided by the dept_emps_v view is not the only one you could define here. As mentioned, the department document has a master-detail structure: a department object may contain multiple employee objects within its employees array. Thus, to be more in line with relational modeling, you could define two views here, so that the first one includes only those columns that are defined upon the department object properties of scalar data type, thus except those that relate to the employee object; while the other view includes only the employee-related columns along with the deptId column (as the foreign key) to uniquely identify a parent row in the first view:
CREATE OR REPLACE VIEW dept_v AS
SELECT d.*
FROM json_docs dept,
JSON_TABLE(dept.jsondoc, '$'
COLUMNS (deptId NUMBER(3) PATH '$.deptId',
deptName VARCHAR2(50 CHAR) PATH '$.deptName'
)) d;
CREATE OR REPLACE VIEW emps_v AS
SELECT e.*
FROM json_docs dept,
JSON_TABLE(dept.jsondoc, '$'
COLUMNS (deptId NUMBER(3) PATH '$.deptId',
NESTED PATH '$.employees[*]'
COLUMNS (
age NUMBER(3) PATH '$.age',
firstName VARCHAR2(50 CHAR) PATH '$.firstName',
lastName VARCHAR2(50 CHAR) PATH '$.lastName'
))) e;
Now you can issue a join query against these views:
SELECT emps_v.firstName, emps_v.lastName, emps_v.age, dept_v.deptName
FROM emps_v, dept_v
WHERE emps_v.deptId = dept_v.deptId
AND dept_v.deptId = 310;
The above join should return:
FIRSTNAME LASTNAME AGE DEPTNAME
-----------------------------------------
Maya Silver 32 IT Research
John Polonsky 25 IT Research
Indexing JSON Content
In the preceding example, you query the emps_v view in the context of a deptId, thus joining from the parent (dept_v) to the child (emps_v). If you frequently issue a query like this one, you might want to consider defining an index on deptId, as far as relational modeling is concerned. The good news is Oracle Database allows you to index JSON data. So, you can create an index on property deptId of the JSON object that is in column jsondoc of the json_docs underlying table. In the following statement, you create a bitmap index for a value returned by JSON_VALUE:
CREATE BITMAP INDEX dept_idx ON json_docs (json_value(jsondoc, '$.deptId' RETURNING NUMBER(3) ERROR ON ERROR));
It is important to note that an index created with JSON_VALUE that includes the ERROR ON ERROR clause can be then used in queries with JSON_TABLE, as in the following example. To make sure that an index is picked up, you can look at the execution plan for a query that is supposed to use that index:
EXPLAIN PLAN FOR
SELECT e.*, d.*
FROM json_docs dept,
JSON_TABLE(dept.jsondoc, '$'
COLUMNS (deptId NUMBER(3) PATH '$.deptId',
NESTED PATH '$.employees[*]'
COLUMNS (
age NUMBER(3) PATH '$.age',
firstName VARCHAR2(50 CHAR) PATH '$.firstName',
lastName VARCHAR2(50 CHAR) PATH '$.lastName'
))) e,
JSON_TABLE(dept.jsondoc, '$'
COLUMNS (deptId NUMBER(3) PATH '$.deptId',
deptName VARCHAR2(50 CHAR) PATH '$.deptName'
)) d
WHERE e.deptId=d.deptId AND d.deptId=310;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
The execution plan may look like this, proving the fact that the dept_idx index has been picked up:
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13343 | 12M| 4474 (1)| 00:00:01 |
| 1 | NESTED LOOPS | | 13343 | 12M| 4474 (1)| 00:00:01 |
| 2 | NESTED LOOPS | | 163 | 156K| 31 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| JSON_DOCS | 1 | 974 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | DEPT_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | JSONTABLE EVALUATION | | | | | |
|* 6 | JSONTABLE EVALUATION | | | | | |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(JSON_VALUE("JSONDOC" FORMAT JSON , '$.deptId' RETURNING NUMBER(3,0) ERROR ON
ERROR)=310)
6 - filter("P"."DEPTID"="P"."DEPTID")
Actually, for an index to be picked up, it is crucial to have an exact match between the data type (including precision, scale, or length – if any) specified in the RETURNING clause of the JSON_VALUE used in the CRAETE INDEX statement, and the data type you specify for the indexed property in the query. If, for example, you edit the deptId column definition in the above query – replacing NUMBER(3) with NUMBER, then the dept_idx index will not be picked up, because deptId was defined as NUMBER(3) during creation of the index.
Querying External JSON Sources
The ability to query external JSON sources is surely a key feature of JSON support in the database. Starting with Oracle Database 9i, you can query external data sources that are available through the HTTP protocol with the help of the HTTPURITYPE function. Now, with support for JSON, you can use HTTPURITYPE to access HTTP-callable JSON data sources also.
Being a lightweight data-interchange format, JSON is widely used by Web services and Web applications today. For example, most search engines, including Google, as well as popular social networking services, including Facebook and Twitter, offer APIs that allow you to do searches programmatically via HTTP, giving the search results in the JSON format.
As an example, consider a call to the Google Web Search API that allows you to do programmatic web searches. From within an Oracle database, this can be done as follows:
SELECT httpuritype('http://ajax.googleapis.com/ajax/services/search/web?v=1.0&q=Oracle%20Database%20JSON').getCLOB() "Search Results" FROM DUAL;
The most important thing that you should learn from the results of the above query is the structure of the JSON document returned. Based on this information, you can define a virtual table over the returned JSON content using the JSON_TABLE function, picking up only those pieces of the retrieved data in which you are interested:
SELECT * FROM JSON_TABLE(
httpuritype('http://ajax.googleapis.com/ajax/services/search/web?v=1.0&q=Oracle%20Database%20JSON').getCLOB(), '$.responseData.results[*]'
COLUMNS (title VARCHAR2(100 CHAR) PATH '$.titleNoFormatting',
url VARCHAR2(100 CHAR) PATH '$.url'));
The output might look like this:
ITLE
----------------
URL
------------
JSON in Oracle Database - Oracle Documentation
https://docs.oracle.com/database/121/ADXDB/json.htm
JSON_TABLE - Oracle Documentation
https://docs.oracle.com/database/121/SQLRF/functions091.htm
Did You Say "JSON Support" in Oracle 12.1.0.2? (OTN DBA/DEV
https://blogs.oracle.com/OTN-DBA-DEV-Watercooler/entry/did_you_say_json_support
ORACLE-BASE - JSON Support in Oracle Database 12c Release 1
http://oracle-base.com/articles/12c/json-support-in-oracle-database-12cr1.php
Optional Error Handling
One drawback of dealing with external JSON data sources is that you may not know for sure the structure of a document being returned. The structure of a returned document may change, for example, depending on the parameters passed in within a query URL. As another example, consider the Twitter Search API that requires a search request to be authenticated. If authentication fails, you’ll get an error JSON document whose structure, of course, differs from the one you normally expect to deal with.
The following example illustrates that even a simple misspelling (highlighted in bold) in a query URL may lead to an error caused by changing the structure of the returned document:
SELECT * FROM JSON_TABLE(
httpuritype('http://ajax.googleapis.com/ajax/services/search/web?v=1.0&q=Oracle%20Database%30JSON').getCLOB(), '$.responseData.cursor'
COLUMNS (resultCount VARCHAR2(20 CHAR) PATH '$.resultCount'));
The above query should return nothing. The point is that the resultCount property is omitted in the cursor object when a search query ends up with an error. And the default behavior for handling errors in the Oracle SQL functions and conditions is NULL ON ERROR. That is why the above query returns NULL instead of raising the error. If you want otherwise, you have to explicitly include the ERROR ON ERROR clause in the function, as in the following example:
SELECT * FROM JSON_TABLE(
httpuritype('http://ajax.googleapis.com/ajax/services/search/web?v=1.0&q=Oracle%20Database%30JSON').getCLOB(), '$.responseData.cursor'
ERROR ON ERROR
COLUMNS (resultCount VARCHAR2(20 CHAR) PATH '$.resultCount'));
This time you should see the following error message:
ORA-40462: JSON_VALUE evaluated to no value
40462. 00000 - "JSON_VALUE evaluated to no value"
*Cause: The provided JavaScript Object Notation (JSON) path expression
did not select a value.
*Action: Correct the JSON path expression.
It is interesting to note that your options are not limited to ERROR ON ERROR and NULL ON ERROR only when it comes to specifying the optional error clause in an Oracle SQL function or condition. For the entire list, check out the Error Clause for Oracle SQL Functions for JSON section in the JSON in Oracle Database chapter of the XML DB Developer's Guide.
Conclusion
The article illustrated how to make use of the JSON support in Oracle Database 12c, explaining the key features by example. In particular, you looked at how to access JSON data – wherever it is found – from within a SQL query issued against an Oracle database. Also, you learned how to project JSON data relationally, thus making it possible to join that data to the data from another source. You also looked at how you might take advantage of indexing JSON content, and how optional error handling works in Oracle SQL functions for JSON.
See Also
- JSON Tutorial: W3Schools Home
- XML DB Developer's Guide: JSON in Oracle Database
- Database SQL Language Reference: JSON_TABLE
- Database SQL Language Reference: JSON_VALUE
- Did You Say "JSON Support" in Oracle 12.1.0.2?
About the Author
Yuli Vasiliev is a software developer, freelance author, and consultant currently specializing in open source development, Java technologies, business intelligence (BI), databases, service-oriented architecture (SOA), and more recently virtualization. He is the author of a series of books on the Oracle technology, including Oracle Business Intelligence: An introduction to Business Analysis and Reporting (Packt) and PHP Oracle Web Development: Data processing, Security, Caching, XML, Web Services, and Ajax (Packt).