SQL for JSON Rationalization Part 7: Restriction – True, False and Null

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s