In part 14 of this blog series Cartesian Product queries were discussed that did have an Asterisk projection; this blog discusses specific paths as projection (non-Asterisk).
Example Data Set
As always, the sample data sets that are being used for queries in this blog are introduced first.
select {*} from ying
results in
{"a":3,"c":20} {"a":4,"c":21}
and
select {*} from yang
results in
{"a":1,"b":10} {"a":2,"b":11}
Projection
To recap, JSON SQL supports JSON projection as well as relational projection. JSON projection is specified by enclosing paths within a set of curly brackets: {}. This will cause the query result represented as JSON objects.
For example, the following query returns JSON objects.
select {a, b} from yang
results in
{"a":1,"b":10} {"a":2,"b":11}
JSON SQL returns relational results when the set of curly brackets is omitted; the following query returns the result as table.
select a, b from yang
results in
|a |b | +-------------------------+-------------------------+ |1 |10 | |2 |11 |
Projection without AS in Joins
The following is a projection of a join resulting in JSON objects.
select {yi.a, ya.b} from ying as yi, yang as ya
results in
{"ya":{"b":10},"yi":{"a":3}} {"ya":{"b":11},"yi":{"a":3}} {"ya":{"b":10},"yi":{"a":4}} {"ya":{"b":11},"yi":{"a":4}}
The same query with results represented as relation is specified as follows.
select yi.a, ya.b from ying as yi, yang as ya
results in
|yi_a |ya_b | +-------------------------+-------------------------+ |3 |10 | |3 |11 | |4 |10 | |4 |11 |
Observe that the results include the table correlation specifiers “yi” or “ya”. This is necessary since different collections might have documents with the same paths. The following query highlights this case.
select {yi.a, ya.a} from ying as yi, yang as ya
results in
{"ya":{"a":1},"yi":{"a":3}} {"ya":{"a":2},"yi":{"a":3}} {"ya":{"a":1},"yi":{"a":4}} {"ya":{"a":2},"yi":{"a":4}}
This automatic result qualification using correlation specifications ensures that path duplicates are automatically resolved in the results.
Projection with AS in Joins
In many cases the automatic duplicate resolution is sufficient for clients. However, in some cases this is not desired. In those cases the AS clause allows the placement of result values into any place of JSON documents using the AS clause. In the relational result case the columns can be named as desired.
select {yi.a as b, ya.a as c} from ying as yi, yang as ya
results in
{"b":3,"c":1} {"b":3,"c":2} {"b":4,"c":1} {"b":4,"c":2}
The above shows a simple renaming of the paths.
select {yi.a as x.b, ya.a as y.[0]} from ying as yi, yang as ya
results in
{"x":{"b":3},"y":[1]} {"x":{"b":3},"y":[2]} {"x":{"b":4},"y":[1]} {"x":{"b":4},"y":[2]}
This query shows a more complex result object creation and goes beyond simple renaming of paths.
The following query shows how specific column names are specified.
select yi.a as x, ya.a as y from ying as yi, yang as ya
results in
|x |y | +-------------------------+-------------------------+ |3 |1 | |3 |2 | |4 |1 | |4 |2 |
Summary
In summary, defining projection in context of SQL JSON joins is straightforward and supports flexible renaming of columns in context of relational results as well as expressive result value positioning as paths in JSON object results.
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 