Search code examples
jsoncouchbasesql++coercion

How can I avoid autocoercion in Couchbase queries?

An N1QL query has a filter WHERE myField < $value.

From experimenting, I see that Couchbase orders the types as follows: boolean < integer < string < JsonArray, even though from my perspective they should not be comparable.

For example, any boolean evaluates as less than any integer; any integer is less than all strings. (9223372036854775807 (Long.MAX_VALUE) evaluates as less than "" (empty string).)

I want to avoid this type-coercion. I want "A" < 1 and "A" > 1 to be false and not to return such values from the filter. (And also, it seems that in Javascript, both these expressions are false, as they should be.)

What are the coercion rules? How do I prevent this?

Solution

  • You have discovered the collation order of N1QL. Here is a fuller explanation:

    https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/datatypes.html

    If you want to avoid this comparison across types, you can add a clause using the TYPE() function to verify that the two elements being compared are of the same type.

    https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/typefun.html

    So rather than having $A > 3 you would have ($A > 3) AND (TYPE($A) = TYPE(3)).