Until this blog only single restrictions were used; here is the discussion of complex restrictions in JSON SQL.
Example Data Set
In the following the collection “comrescoll” (for complex restriction collection) is used and it contains the following JSON documents:
{"a":{"c":"foo"},"b":[true,false,null]} {"a":{"c":"foo"}} {"b":[true,false,null]}
Single Restriction Semantics
As defined in a previous blog, a document only participates in a single restriction if the referenced properties are present.
For example,
select {*}
from comrescoll
where a.c = 'foo'
returns
{"a":{"c":"foo"},"b":[true,false,null]} {"a":{"c":"foo"}}
as only those two object have the property “a.c” defined.
As the blog will show, a complex restriction applies to a document if all properties requiring evaluation are present.
Note that this previous sentence was really carefully worded. The following would not be correct: a complex restriction applies to a document if all properties that are referenced in the complex restriction are present.
The reason will be discussed later in detail.
AND
In an ANDed complex restriction, all properties that require evaluation have to be present in the document. A document will be included into the result set if the complex restriction evaluates to true for the document. The following query shows this case:
select {*}
from comrescoll
where a.c = 'foo' and b.[1] = false
returns
{"a":{"c":"foo"},"b":[true,false,null]}
… WHERE true, … WHERE false
A restriction always resulting in true can be implemented like this:
select {*}
from comrescoll
where 5 = 5
No complex restriction is required to accomplish this.
A restriction always resulting in false can be implemented like this:
select {*}
from comrescoll
where a.c = ‘foo’ and a.c = ‘bar’
However, an easier way is
select {*}
from comrescoll
where 5 = 6
This case does not require a complex restriction either.
OR
The OR operator behaves as expected:
select {*}
from comrescoll
where a.c = 'foo' or b.[1] = false
returns
{"a":{"c":"foo"},"b":[true,false,null]} {"a":{"c":"foo"}} {"b":[true,false,null]}
In case of a disjunction not all paths have to actually be present for a correct execution:
select {*}
from comrescoll
where d.[1] = false or a.c = 'foo'
returns
{"a":{"c":"foo"},"b":[true,false,null]} {"a":{"c":"foo"}}
Even though the path “d.[1]” is not present, the evaluation takes place and is correct as the absence of a path does not necessarily fail the execution in a disjunction if this part of the complex restriction is unnecessary.
To force the presence of the path “d.[1]”, a predicate like “exists_path” would have to be added like this:
select {*}
from comrescoll
where d.[1] = false or a.c = 'foo' and exists_path d.[1]
This is not implemented yet, but it will be down the road at some point in time.
NOT
The operator NOT behaves as expected. The query
select {*}
from comrescoll
where not a.c = 'foo' and b.[1] = false
results in
{"b":[true,false,null]}
This might be surprising initially as the result contains a document that does not contain the path “a.c”. However, the absence of the path “a.c” means that “a.c” does not have the value ‘foo’.
Like above, the presence of “a.c” would be enforced by a conjunction of exists_path “a.c”.
Combination of AND, OR, NOT
The operators AND, OR and NOT can be combined as expected according to the SQL 92 standard. For example, the query
select {*}
from comrescoll
where not a.c = '' and not b.[0] = 0 or not b.[1] = 1
results in
{"a":{"c":"foo"},"b":[true,false,null]} {"a":{"c":"foo"}} {"b":[true,false,null]}
Caveat
Remember the “careful wording” from earlier? Consider the following query:
select {*}
from comrescoll
where a.c = 'foo' and b.[1] = false or d = 6 or d <> 6
On the surface of it, the paths “a.c”, “b.[1]” and “d” need to be present for the complex restriction to be evaluated. However, this query returns the following result:
{"a":{"c":"foo"},"b":[true,false,null]}
Upon closer inspection, the two restrictions involving the path “d” are insignificant. The path “d” is not needed for the evaluation even though the path “d” is referred to in the complex restriction.
Another example is
select {*}
from comrescoll
where a.c = 'foo' and b.[1] = false or (d = 6 and d <> 6)
resulting in
{"a":{"c":"foo"},"b":[true,false,null]}
What if the path “d” should be present? In this case a predicate like exists_path is necessary (to be discussed in one of the future blogs). With the additional predicate exists_path only those documents are included in the evaluation that actually contain that path.
Summary
Complex restrictions in JSON SQL are very similar to Relational SQL in their syntax and semantics except for the difference that paths that are not required for the evaluation of a complex restriction do not have to be present in the documents even though they are mentioned in the complex restriction.
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 