Using Non-comparable JSON Document Data Types in Comparisons and Sorts

Defines non-comparable data types and their usage.

Non-comparable data types are data types that do not follow a well-defined order. In contrast to comparisons between comparable types, comparisons between fields and values of non-comparable types do not qualify even if you perceive a match in values. This is true whether you have indexed the field you are comparing or not.

Arrays and nested documents also fall into the non-comparable category. Since these entities do not have a defined ordering, only equality comparisons on these types are meaningful. For arrays, the order of the array elements must match; for nested documents, all fields in the nested document must match, but the order of the fields is not relevant.

You cannot order on Container Field Paths. For example, you cannot order on the field a[].b, even if the subfield b has scalar data.

Example

Consider the following example. If your field, docField, has string values and you compare it against a numeric value, none of the string values in the field match the numeric. Likewise, if your field has numeric values and you compare it against a string value, none of the numeric values in the field match the string. Both field and comparison values must be strings or integers to match:

Document Name Value of Field docField Type of Field docField Filter Condition Field Value Qualifies Filter Condition?
DOCUMENT1a 23 STRING docField = 23 No
DOCUMENT1b 23 INT docField = 23 Yes
DOCUMENT2a 45 INT docField = '45' No
DOCUMENT2b 45 STRING docField = '45' Yes
DOCUMENT3 No type due to missing value docField = 23 No
DOCUMENT4 NULL No type due to NULL value docField = '45' No

HPE Ezmeral Data Fabric Database does not define a fixed ordering across non-comparable types. It sorts the values within comparable types and within each non-comparable type, but not across both.

In the previous example, when sorting on docField, you could obtain the following for a sort in ascending order:

Document Name Value of Field docField Type of Field docField
DOCUMENT1b 23 INT
DOCUMENT2a 45 INT
DOCUMENT1a 23 STRING
DOCUMENT2b 45 STRING
DOCUMENT4 NULL No type due to NULL value
DOCUMENT3 No type due to missing value

Note the independent ordering of the integer and string values. Also note that for the rows with NULL and missing field values, the row with NULL appears before the row with a missing value.