After discussing projection, selection is up next in the blog series on SQL for JSON. This first blog on selection focuses on the scalar JSON types Number and String.
Demo Data
As usual, we start with demo data. The collection for this blog is called “selcoll” (for SelectionCollection) and contains the following documents:
select {*} from selcoll
returns
{"a":{"b":25},"c":["foo","foobar","ba'r"],"d":{"e":"foo"}} {"a":{"b":"25"},"c":["foo1","foobar","bar"],"d":{"e":"foo"}} {"a":{"b":25},"c":["foo2","foo2bar2","ba\"r"],"d":{"e":"foo2"}}
Selection based on Literals
Selection is following the regular Relational SQL syntax and is straightforward. For this discussion only single predicates are shown, not (complex) Boolean expressions of predicates. Boolean expression of predicates follow the usual semantics and do not require a lot of discussion.
select {*} from selcoll where a.b = 25
This JSON SQL statement selects all documents from selcoll that have a property “a” and a property “b” within a sub-document of “a” with the value of 25.
The result is
{"a":{"b":25},"c":["foo","foobar","ba'r"],"d":{"e":"foo"}} {"a":{"b":25},"c":["foo2","foo2bar2","ba\"r"],"d":{"e":"foo2"}}
The following selection has the same semantics and returns the same result:
select {*} from selcoll where 25 = a.b
A selection based on a String literal follows the same syntax:
select {*} from selcoll where c.[1] = 'foobar'
This returns
{"a":{"b":25},"c":["foo","foobar","ba'r"],"d":{"e":"foo"}} {"a":{"b":"25"},"c":["foo1","foobar","bar"],"d":{"e":"foo"}}
And the following selection returns the same result:
select {*} from selcoll where 'foobar' = c.[1]
In this context a note is in order. JSON uses double quotes as string delimiter, not single quotes, as SQL does. In order to stay as near as possible to Relational SQL, single quotes are used and transformed into double quotes by the underlying implementation.
Selection based on Value Comparison
It is possible to relate two different values within a document as well (aka, not a self-join that would related values of different documents – this will be discussed in a later blog).
select d.e as de, c.[0] as c0 from selcoll where d.e = c.[0]
This query selects all documents that have the same value in d.e and c.[0]. As added benefit the query projects to those two values as well.
The result is
|de |c0 | +-------------------------+-------------------------+ |"foo" |"foo" | |"foo2" |"foo2" |
Any path can be related to any other path without restriction.
While in this blog only numbers and strings are discussed, the above discussed types of restrictions will work for all JSON data types in general, including true, false, null, objects and arrays (discussed in subsequent blogs).
Operations
The usual operators are defined: <, >, <>, =, >=, and <=. The semantics of these is defined for Number and String (Relational SQL semantics is taken). For the other JSON types they will have to be defined as the other JSON types do not have a corresponding Relational SQL domain.
Beyond these operators more “interesting” operations are required. For example
select {*} from selcoll where c contains 'foobar'
whereby “c” refers to a JSON array (and possibly a JSON object). This predicate would be true if there is an element in “c” that is of type String and the value of that element is “foobar”. There is a whole set of interesting operations that will be discussed at some point later as well.
Semantics
As implicitly demonstrated above, a JSON document is only in the result set if (a) the path to the value as specified in the JSON SQL query is present and (b) the value is the value as indicated in the selection clause in JSON SQL.
If the path does not exist or the value does not have a matching value, no result is returned for that document (and not the empty document itself).
There is no implicit type transformation implemented. This means that a Number literal only matches number values, and a String literal only matches string values.
Syntax Twists
Syntax has always a twist, especially if different languages are combined. In this case one of the twists is the single quote. A single quote within a string is represented as two single quotes in Relational SQL. JSON SQL does not have that requirement since strings are delimited by double quotes in JSON and a single quote is treated as regular character. The reverse situation exists also: double quotes have to be escaped within a string in JSON, but not in Relational SQL.
The query (double quote, not escaped in JSON SQL)
select {*} from selcoll where c.[2] = 'ba"r'
returns
{"a":{"b":25},"c":["foo2","foo2bar2","ba\"r"],"d":{"e":"foo2"}}
And the query (two single quotes, escaped in JSON SQL)
select {*} from selcoll where c.[2] = 'ba''r'
returns
{"a":{"b":25},"c":["foo","foobar","ba'r"],"d":{"e":"foo"}}
Summary
Restriction (or selection) is almost straightforward for the types Number and String in context of JSON SQL. The only twist is the way Relational SQL and JSON SQL differ in denoting String literals as well as encode special characters.
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 