The previous blog outlined an initial glimpse at a JSON SQL query language and how it works when applied to JSON documents. In the following, a demo shows a concrete implementation.
Command Line Interface
The command line interface provides a few basic commands as follows:
JQDR> help JQDR - JSON Query Done Right Commands: help exit executequery <JSON SQL query> load <table name> <file name> deleteload <table name> <file name> createtable <table name> droptable <table name> deletetable <table name> existstable <table name> listtables <JSON SQL query> JQDR>
Document Collection
The following very small collection “tinycoll” contains two documents:
{"a": 5, "b": {"c": 10, "d": 11}, "c": [101, 102, {"d": 103}, {"e": 104}] }
{"a": 5, "b2": [10, 11], "c": [101, 102, {"d": 103}, {"e": 104}] }
Loading those into the database is accomplished by adding the documents into a file and then load the documents from the file into the database after the table “tinycoll” has been created:
JQDR> load tinycoll src/test/resources/blog/tinycoll.txt JQDR>
Currently the language does not support an insert statement, however, this is in the plans.
select {*}
A first JSON query is to select all the documents and output those as documents (requested by the { and } in the projection clause):
JQDR> select {*} from tinycoll {"a":5,"b":{"c":10,"d":11},"c":[101,102,{"d":103},{"e":104}]} {"a":5,"b2":[10,11],"c":[101,102,{"d":103},{"e":104}]} JQDR>
select {a, b.c, c.[3].e}
A more interesting query is a projection that reaches into the documents:
JQDR> select {a, b.c, c.[3].e} from tinycoll {"a":5,"b":{"c":10},"c":["<>","<>","<>",{"e":104}]} {"a":5,"c":["<>","<>","<>",{"e":104}]} JQDR>
A few observations are:
- Full paths are inserted into the result documents. This allows to access the result documents using the same paths that were used in the projection (aka, a, b.c and c.[3].e).
- JSON does not have a “value does not exist” representation. Therefore, the JSON query processor inserts “<>” for array values that do not exist, but need to be present in order to provide correct array indexes.
For example, only the 4th array element was projected, so the first three must be part of the result, but represented as “values does not exist” as they were not requested in the projection. In a Relational SQL world SQL NULL would have been used in order to represent “values does not exist” (or is unknown).
select *
This query selects all documents, but the result is in relational table format, not JSON documents (as the { and } are omitted in the projection). Each path into any of the documents of the collection is represented as a separate column. The following shows the resulting 14 columns:
JQDR> select * from tinycoll |a |b_c |b_d |b |b2_[0] |b2_[1] |b2 |c_[0] |c_[1] |c_[2]_d |c_[2] |c_[3]_e |c_[3] |c | +-----+-----+-----+----------------+-------+-------+--------+------+------+--------+----------+--------+----------+------------------------------+ |5 |10 |11 |{"c":10,"d":11} |<> |<> |<> |101 |102 |103 |{"d":103} |104 |{"e":104} |[101,102,{"d":103},{"e":104}] | |5 |<> |<> |<> |10 |11 |[10,11] |101 |102 |103 |{"d":103} |104 |{"e":104} |[101,102,{"d":103},{"e":104}] | JQDR>
It is important to note that the column names are default names generated by the JSON SQL query processor and they actually represent the paths (however, instead of a “.”, a “_” is used for the representation).
select a, b.c, c.[3].e
A projection looks as follows
JQDR> select a, b.c, c.[3].e from tinycoll |a |b_c |c_[3]_e | +-----+-----+--------+ |5 |10 |104 | |5 |<> |104 | JQDR>
In this case only three columns are returned as the projection projects only three paths.
Summary
This demo has provided a first glimpse at a JSON SQL language that supports querying JSON documents and provides the option of returning results as JSON objects or in table form.
The next blog will focus more on projection and the various relevant details of the projection semantics.
Go [ JSON | Relational ] SQL!
Disclaimer
The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
r e a l - p r o g r a m m e r 