Using Noncomparable JSON Document Data Types in Comparisons and Sorts

Noncomparable 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 noncomparable 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 noncomparable category. Because they 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.

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

MapR-DB does not define a fixed ordering across noncomparable types. It sorts the values within comparable types and within each noncomparable 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.