This blog provides a small tour of basic SQL queries that operate on JSON in context of the Oracle Database 12c Release 1 (12.1.0.2.0).
Sample Data Set
The following very basic data set is used as an example in this blog. It is kept simple in order to be able to focus on the queries without having to deal with complex JSON objects at the same time.
First, a simple table is created that contains a JSON column. Next, some rows containing JSON objects are inserted.
DROP TABLE demo;
CREATE TABLE demo ( id NUMBER, player CLOB CONSTRAINT player_ensure_json CHECK (player IS JSON (STRICT WITH UNIQUE KEYS)));
INSERT INTO demo VALUES (1, '{"person": "Bob", "score": 10}');
INSERT INTO demo VALUES (2, '{"person": "Bob", "score": 20}');
INSERT INTO demo VALUES (3, '{"person": "Jake", "score": 100}');
INSERT INTO demo VALUES (4, '{"person": "Jake", "score": 200}');
INSERT INTO demo VALUES (5, '{"person": "Alice", "score": 1000}');
With the sample data set in place, we can now construct a complex query in several steps.
Selection and Projection
The most basic query selecting the complete data set is
SELECT * FROM demo d;
A basic projection extracting only the person from the JSON objects is
SELECT d.player.person FROM demo d;
A basic selection restricting the JSON objects is
SELECT d.player.person FROM demo d WHERE d.player.person IN ('Jake', 'Bob');
The syntax for accessing properties in JSON objects is in principle
<table alias>.<JSON column>.<path to JSON object key>
with variations on JSON array index references if required (http://docs.oracle.com/database/121/ADXDB/json.htm#ADXDB6246).
A more complex selection with an additional restriction is
SELECT d.player.person FROM demo d WHERE d.player.score > 0 AND d.player.person IN ('Jake', 'Bob');
Ordering
Results can be ordered, for example, in the following way
SELECT d.player.person FROM demo d WHERE d.player.score > 0 AND d.player.person IN ('Jake', 'Bob'); ORDER BY d.player.person DESC;
Grouping
Results can be grouped also as a preparation for aggregation
SELECT d.player.person FROM demo d WHERE d.player.score > 0 AND d.player.person IN ('Jake', 'Bob'); GROUP BY d.player.person ORDER BY d.player.person DESC;
Aggregation
Different aggregation functions can be used to do some basic analysis
SELECT d.player.person, SUM(d.player.score), AVG(d.player.score), MIN(d.player.score), COUNT(*) FROM demo d WHERE d.player.score > 0 AND d.player.person IN ('Jake', 'Bob'); GROUP BY d.player.person ORDER BY d.player.person DESC;
Final Result
The final result is show here in table representation (copied from SQLDeveloper)
Inspiration
This example was inspired, in fact, by http://www.querymongo.com. There, the MySQL Query
is translated to one of MongoDB’s query interfaces to
(web site accessed on 10/21/2014).
Summary
In summary, SQL functionality is available not only for the relational model in the Oracle Database 12c, but also for JSON-based data.
This makes the Oracle database a quite powerful JSON processing environment as querying JSON data is possible through the declarative SQL language.
Disclaimer
The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.