This installment reviews restriction in JSON SQL based on JSON object literals (all other JSON types except JSON array have been discussed in previous blogs).
JSON Object Notation
JSON SQL follows the JSON object notation as defined in the JSON standard. An empty JSON object is denoted as {} and a non-empty JSON object has one or more comma separated pairs (a pair is a tuple of string and JSON type separated by a colon – also referred to as property).
A JSON object literal is either an empty JSON object or a non-empty JSON object. A JSON object literal is not enclosed in quotes. The only JSON literal enclosed in quotes is JSON string. If a JSON object is enclosed in quotes then it is not a JSON object, but a JSON string.
Sample Document Set
The following document set is used in this blog and the documents are stored in a collection called “objectColl”.
select {*} from objectColl
results in
{"one": {"a": 1}} {"one": "{\"a\": 1}"} {"three": {"b": {"c": null}}} {"four": {"x": 8, "y": 9}} {"five": {}}
Restriction based on JSON Object Literal
Starting with the empty JSON object literal, the following two queries product the same result.
select {*} from objectColl where five = {}
and
select {*} from objectColl where {} = five
result in
{"five": {}}
In the following, queries show the JSON object literal on the right side of the operator, however, it can be on either side.
Operators = And <>
The operators = and <> are defined for a JSON object literal. JSON SQL regards two JSON objects as equal if both have the same pairs (recursively), in any order; and not equal otherwise.
The query (restriction using JSON object literal)
select {*} from objectColl where one = {"a": 1}
returns
{"one": {"a": 1}}
The query (restriction using JSON string literal(!))
select {*} from objectColl where one = '{"a": 1}'
returns
{"one": "{\"a\": 1}"}
A restriction can reach into the JSON object as well using the path notation. The query
select {*} from object Coll where three.b = {"c": null}
returns
{"three": {"b": {"c": null}}}
Operators <, >, <= And >=
The operators <, >, <= and >= could be defined recursively for convenience with some restrictions. For example, a JSON object could be considered less than another JSON object if both have the same pairs and if the values of the corresponding pairs are less than another.
However, JSON true, JSON false and JSON null would not be able to participate in the operator <, >, <= or >=, only JSON object, JSON array, JSON number and JSON string.
Those four operators are currently not directly implemented in JSON SQL since it is possible to achieve the same by writing a complex conjunctive restriction (details on this approach will be discussed in a subsequent blog as well as strategies of what to do if any of JSON true, JSON false or JSON null are present).
Canonical Interpretation
The order of the pairs inside a JSON object is not significant (according to the JSON standard). The query
select {*} from objectColl where four = {"y": 9, "x": 8}
therefore results in
{"four": {"x": 8, "y": 9}}
Summary
Restriction by JSON object is provided by JSON SQL without problem and the syntax extends the Relational SQL syntax naturally.
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 