Using Comparable JSON Document Data Types in Comparisons and Sorts

Data types that have a well defined order amongst the types are comparable data types. In a filter condition, if a document's field value and the comparison value are of comparable types, the document qualifies if the condition returns true. This applies regardless of whether you have created secondary indexes on the comparison fields.

Based on the above definition, numeric types are comparable. This includes the following types:
  • INT
  • SHORT
  • LONG
  • FLOAT
  • DOUBLE
Note: FLOAT and DOUBLE are approximate representations of decimal values. They may not return true in equality comparisons against their equivalent decimal values.

Example

Consider the following example where you have four documents, each with a field, AccountBalance. The types of the field differ, as noted in the table, but they are all comparable numeric types:

Document Name AccountBalance Field Value AccountBalance Field Type
DOCUMENT1 1900.12 FLOAT
DOCUMENT2 10000 INT
DOCUMENT3 10 LONG
DOCUMENT4 27.88 DOUBLE

If you specify a sort on the field AccountBalance, MapR Database sorts the field in the following order:

Document Name AccountBalance Field Value
DOCUMENT3 10
DOCUMENT4 27.88
DOCUMENT1 1900.12
DOCUMENT2 10000

Secondary indexes sort and store data based on the values of the indexed fields. When reading through the index, MapR Database returns the documents in the order of index.

For example, suppose you have an index where AccountBalance is the indexed field. A query with the condition, "AccountBalance > 20", returns the documents in the following order if MapR Database processes the query using the index:

  • DOCUMENT4
  • DOCUMENT1
  • DOCUMENT2