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

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:

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:

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.