SQL for JSON Rationalization Part 10: Complex Restriction

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.