SQL for JSON Rationalization Part 13: Comparison Operators for JSON Object and JSON Array

As promised in a previous blog the discussion of comparison operators in context of JSON object and JSON array is following in this blog.

Comparison Operators = and <>

Equality and inequality are very straightforward comparison operators and are discussed first. Both are defined on the paths to properties as well as the JSON types of properties.

JSON array equality is (recursively) defined as follows. Two JSON arrays are equal if

  • They have the same number of array indexes starting at index 0
  • The value of each array element is equal for the same index in each of the two JSON arrays

Implicitly this means that order matters in the sense that array elements are compared according to their index position.

JSON object equality is (recursively) defined as follows. Two JSON objects are equal if

  • They have the exact same set of paths
  • The same path in each document leads to the same value and the same JSON type

Implicitly this means that the order of properties in JSON objects does not matter. It is “only” necessary that both objects have the exact same set of paths in any order.

There is no implicit type conversion supported in JSON SQL. The JSON string “15” is considered different from the JSON number 15 as both are of different JSON type.

Sample Data Set

To illustrate equality the following collection compColl is introduced:

{"a":[15,true,{"p":"q"}],"b":[15,true,{"p":"q"}]}
{"a":[15,true,{"p":"q"}],"b":[15,true,{"p":"q"},null]}
{"a":[15,true,{"p":"q"}],"b":["15",true,{"p":"q"}]}
{"x":{"r":15,"s":[true,false]},"z":{"r":15,"s":[true,false]}}
{"x":{"r":15,"s":[true,false]},"z":{"r":15,"s":[[true,false]]}}
{"x":{"r":15,"s":[true,false]},"z":{"r":"15","s":[true,false]}}
{"e":15,"f":[14,15,16]}
{"e":15,"f":[16,15]}
{"e":15}

Sample Queries

An example query for equal JSON arrays is as follows.

select {*} from compColl where a = b

returns

{"a":[15,true,{"p":"q"}],"b":[15,true,{"p":"q"}]}

An example query for equal JSON objects is as follows.

select {*} from compColl where x = z

returns

{"x":{"r":15,"s":[true,false]},"z":{"r":15,"s":[true,false]}}

Inequality is defined as negation of equality. The following queries demonstrate this:

select {*} from compColl where a <> b

returns

{"a":[15,true,{"p":"q"}],"b":[15,true,{"p":"q"},null]}
{"a":[15,true,{"p":"q"}],"b":["15",true,{"p":"q"}]}
select {*} from compColl where x <> z

returns

{"x":{"r":15,"s":[true,false]},"z":{"r":15,"s":[[true,false]]}}
{"x":{"r":15,"s":[true,false]},"z":{"r":"15","s":[true,false]}}

Undefined Comparison Operators <, >, <= and >=

Several comparison operators are undefined for JSON array and JSON object: <, >, <= and >=. If during query processing these comparison operators are used in combination with JSON array and/or JSON object, then the JSON documents will not participate in the comparison and will not add any result document to the result set.

The following query demonstrates that only like JSON types are compared:

select {*} from compColl where a.[0] <= b.[0]

returns

{"a":[15,true,{"p":"q"}],"b":[15,true,{"p":"q"}]}
{"a":[15,true,{"p":"q"}],"b":[15,true,{"p":"q"},null]}

The following query demonstrates that the <= comparison on JSON array is not defined:

select {*} from compColl where a <= b

returns the empty result.

The reason that those four comparison operators are not implemented is that not all JSON types can be compared with each other. For example, a JSON Boolean and a JSON number cannot be compared and consequently the comparison of JSON array or JSON object might fail and return an undefined result. In fact, across all JSON types, only JSON string can be compared to JSON string and JSON number to JSON number by >, <, <= and >=; all other JSON type cannot be compared with each other or other JSON types with these operators.

In context of query processing a failing comparison operator would not be desirable as the query would fail. As a consequence, JSON SQL does not implement the four comparison operations <, >, <= and >= on JSON array and JSON object (actually, on any JSON type except JSON number and JSON string).

However, a user can compare JSON arrays and JSON objects by comparing their array elements or properties individually where applicable or necessary. This is called user-defined comparison and is based on individual restrictions.

User-defined Comparison

A user defines comparison by means of predicates. This supports the user in comparing only those JSON array elements or JSON object properties that need to be compared for the use case at hand and make sense in this context: a user is not forced to compare all JSON array elements or all JSON object properties by can do so selectively.

select {*} from compColl where a.[2].p >= b.[2].p

returns

{"a":[15,true,{"p":"q"}],"b":[15,true,{"p":"q"}]}
{"a":[15,true,{"p":"q"}],"b":[15,true,{"p":"q"},null]}
{"a":[15,true,{"p":"q"}],"b":["15",true,{"p":"q"}]}

As the example shows, there are JSON array elements or JSON object properties that cannot be compared, e.g., a.[2] or b.[2] (except for equal and not-equal).

Since JSON SQL supports JSON documents with varying schema, a user can ensure the presence and JSON type of certain properties that are relevant for comparison with the predicates exists_path and is_of_type. The former ensures the presence, the latter type compatibility.

The following query shows all documents where the property f does not have a second array index but has a property e. If a query compares e with f.[1] then this query shows which documents will not participate in the query.

select {*} 
from   compColl 
where  exists_path e 
       and not exists_path f.[1]

returns

{"e":15}

The analogous is possible with the is_of_type predicate that would show which documents are excluded because of type incompatibility.

Missing Paths

As the example shows, only those JSON documents are participating in the comparison that fulfill the constraints wrt. existence and type compatibility.

A missing path does not falsify the result, as this example shows: the document is simply not participating in the comparison:

The query

select {*} from compColl where e = f.[1]

returns

{"e":15,"f":[14,15,16]}
{"e":15,"f":[16,15]}

Total Order

A total order across all document in a collection is only possible if each document can be compared with every other document in the same collection. With the predicates exists_path and is_of_type it is possible to determine if any documents will be left out of a comparison and hence the documents of a collection cannot be totally ordered with the given predicates.

Summary

Even though the operators >, <, >= and <= cannot be implemented for several JSON types, clients can implement partial comparison of documents with combinations of individual restrictions. The predicates exists_path and is_of_type allow to determine the set of documents included in (or excluded from) the query.

Go [ JSON | Relational ] SQL!

Disclaimer

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

SQL for JSON Rationalization Part 11: JSON Specific Predicates

JSON SQL cannot assume collections containing only homogeneous documents; instead, it must support schemaless documents. This requires JSON specific predicates that are discussed in this blog.

Example Collection

The documents of the following collection “predColl” are used in this blog as examples.

{"a":"b"}
{"a":{"c":1,"d":2},"e":[77,{"x":"eightyeight"}]}
{"a":{"c":1,"d":2},"e":["seventyseven",{"x":88}]}

Definition: Path

A (complete) path in a JSON document is the list of property names and array indexes from the root all the way to a leaf separated by “.”. For example, a complete path is

e.[1].x

A partial path in a JSON document is the list of property names and array indexes from the root to an intermediary property (that is not a leaf property) separated by “.”. For example, a partial path is

e.[1]

It is possible that a path is a complete path in one document and a partial path in another document. For example, a path that is complete and partial at the same time is

a

Each path leads up to a property that is of a specific JSON type. For example,

  • the type of e.[1].x is JSON_STRING in one of the example documents and JSON_NUMBER in another one.
  • the type of e.[1] is JSON_OBJECT in both cases.

Document: Set of Paths

Based on the above definition, a JSON document can be represented as the set of its paths.

The document

{"a":{"c":1,"d":2},"e":["seventyseven",{"x":88}]}

can be represented as

a
a.c
a.d
e.[0]
e.[1]
e.[1].x

The corresponding types are

JSON_OBJECT
JSON_NUMBER
JSON_NUMBER
JSON_STRING
JSON_OBJECT
JSON_NUMBER

Schemaless Documents / Schema-per-Document

Traditionally, databases enforced the definition of a schema and many continue to do so. This means that data can only be stored successfully in a database if the data complies with the schema at that point in time.

Some database systems do not enforce a schema and as a consequence the data is not constrained by a schema. In context of JSON documents, if a document is not constrained by a schema, then it can have any valid structure (in terms of the rules the JSON standard defines).

These documents are termed “schemaless” documents. In reality there is a schema, an implicit  (intentional) one, for each document, termed “schema-per-document”.

In concrete terms “schemaless” in context of a database system means:

  • Any pair of documents within a collection of a database can potentially have different sets of paths
  • The same (complete or partial) path in two different documents can lead to a value of a different JSON type

As a consequence, queries must be able to test for the existence of paths as well as for the existence of a specific JSON type as these cannot be assumed (in contrast to a database that enforces schemas).

JSON Specific Predicates: exists_path and is_of_type

Two predicates are needed in order to check for the existence of paths or JSON types of values.

  • exists_path <path>
  • <path> is_of_type <JSON type>

The query

select {*} from predColl where exists_path a.d

returns

{"a":{"c":1,"d":2},"e":[77,{"x":"eightyeight"}]}
{"a":{"c":1,"d":2},"e":["seventyseven",{"x":88}]}

The query

select {*} from predColl where e.[1].x is_of_type JSON_number

returns

{"a":{"c":1,"d":2},"e":["seventyseven",{"x":88}]}

Negation

Negation of the predicates is useful to determine if there are document that do not have specific paths or paths do not refer to properties of specific JSON types.

select {*} from predColl where not exists_path e.[1].x

returns

{"a":"b"}

The query

select {*} from predColl where not a is_of_type JSON_object

returns

{"a":"b"}

Definition: (Partial) Homogeneity

Queries using the predicates exists_path or is_of_type can be used to determine if all documents have the required or expected paths or if there are documents that are missing specific paths or have paths leading to unexpected JSON types.

With these predicates it is possible to determine if the documents in a collection are homogeneous wrt. given set of paths.

Dynamic Schema Check

A developer can now implement first checking path existence or JSON type compliance before executing business logic related queries. If the required paths or JSON types are missing in specific documents, appropriate error handling can be implemented for these.

From a different viewpoint, a developer now has the tools to dynamically check or select all documents complying to a schema that is “imagined” at the type of querying.

Summary

While schemaless documents are convenient from a development perspective, this requires in general the ability to check for the existence or absence of paths as well as JSON type compliance.

The predicates exists_path and is_of_type provide the necessary querying tools in order to test for the variations in schemaless JSON documents.

Go [ JSON | Relational ] SQL!

Disclaimer

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.