The last blog (Part 6) introduced the general notion of restriction and focused on JSON String and JSON Number. This blog will extend the discussion covering JSON true, JSON false and JSON null.
Sample Data Set
The following set of documents stored in the collection “boolcoll” is used in this blog:
select {*} from boolcoll
returns
{"a":true} {"a":false} {"true":false} {"true":"null"}
Textual Representation
Even though the JSON standard defines that JSON true, JSON false and JSON null are all lowercase, in context of JSON SQL all combinations of upper and lower case characters are permitted:
select {*} from boolcoll where a = TruE
returns
{"a":true}
Operators
Not all of the default operators are defined on JSON true, JSON false or JSON null. Defined are = and <>, undefined are <, >, <= and >=. If one of the undefined operators is used in conjunction with JSON true, JSON false or JSON null, a semantic query analysis error is returned before the query is executed.
Restriction Syntax
As in the last blog, the property name can be on either side of the operator. The following two queries return the same result:
select {*} from boolcoll where a <> false
and
select {*} from boolcoll where false <> a
return
{"a":true}
Execution Semantics
If the specified property is present, the restriction is evaluated and the document is added to the result set if it fulfills the restriction. If the property is not present, no evaluation and consequently no inclusion into the result set takes place.
select {*} from boolcoll where null = null
returns all documents as null = null is always true.
Disambiguation
As shown in the example data set for this blog, it is possible that a property name is “true”, “false” or “null”. So far only the short form of property names was used with JSON SQL queries, i.e., property names without being enclosed in double quotes (contrary to the JSON standard definition).
However, as soon as property names can be the same as keywords like JSON true, JSON false or JSON null, disambiguation has to take place. This is accomplished by following the JSON standard: enclosing the property name in double quotes. For example,
select {*} from boolcoll where "true" = false
returns all documents with a property name of “true” that has the value JSON false.
{"true":false}
Since Relational SQL does not use double quotes, there cannot be any confusion:
Along the same lines,
select {*} from boolcoll where "true" = 'null'
returns all documents where the property “true” has the value ‘null’ (string).
{"true":"null"}
This is not ambiguous, either, as Relational SQL uses single quotes to denote String literals.
Needless to say that double quotes can be used outside a disambiguation. For example, one of the above queries could be specified as
select {*} from boolcoll where "a" = TruE
returning
{"a":true}
Double quotes can be used in the projection clause as well.
select "a" from boolcoll where "a" = tRUe
returns
|a | +-------------------------+ |true |
Summary
JSON true, JSON false and JSON null can be used in JSON SQL queries without restriction and in a well-defined way. Disambiguation is not interfering with either the syntax as defined by the JSON standard, or the regular Relational SQL syntax. Great!
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 