Value Comparison Operators

Syntax

comparison_expression ::= concatenate_expression
   [(value_comparison_operator | any_comparison_operator) add_expression]

value_comparison_operator ::= "=" | "!=" | ">" | ">=" | "<" | "<="

For definitions of syntax components referenced in this statement, see:

Semantics

Value comparison operators are primarily used to compare 2 values, one produced by the left operand and another from the right operand (this is in contrast to the sequence comparisons, defined in the following section which compare two sequences of values). If any operand returns more than one item, an error is raised. If both operands return the empty sequence, the operands are considered equal (so true will be returned if the operator is =, <=, or >=). If only one of the operands returns empty, the result of the comparison is false unless the operator is !=.

For the remainder of this section, we assume that each operand returns exactly one item. If an operand returns NULL, the result of the comparison expression is also NULL. Otherwise, the result is a boolean value that is computed as follows.

Among atomic items, if the types of the items are not comparable, false is returned. The following rules defined what atomic types are comparable and how the comparison is done in each case.

A numeric item is comparable with any other numeric item. If an integer or long value is compared to a float or double value, the integer/long will first be cast to float/double. If one of the operands is a number value, the other operand will first be cast to number (if not a number already).
A string item is comparable to another string item (using the java String.compareTo() method). A string item is also comparable to an enum item. In this case, before the comparison, the string is cast to an enum item in the type of the other enum item. Such a cast is possible only if the enum type contains a token whose string value is equal to the source string. If the cast is successful, the two enum items are then compared as explained below; otherwise, the two items are incomparable and false is returned.
Two enum items are comparable only if they belong to the same type. If so, the comparison is done on the ordinal numbers of the two enums (not their string values). As mentioned above, an enum item is also comparable to a string item, by casting the string to an enum item.
Binary and fixed binary items are comparable with each other for equality only. The 2 values are equal if their byte sequences have the same length and are equal byte-per-byte.
A boolean item is comparable with another boolean item, using the java Boolean.compareTo() method.
A timestamp item is comparable to another timestamp item, even if their precisions are different.
JNULL (json null) is comparable with JNULL. If the comparison operator is !=, JNULL is also comparable with every other kind of item, and the result of such a comparison is always true, except when the other item is also JNULL.

The semantics of comparisons among complex items are defined in a recursive fashion. Specifically:

A record is comparable with another record for equality only and only if they contain comparable values. More specifically, to be equal, the 2 records must have equal sizes (number of fields) and for each field in the first record, there must exist a field in the other record such that the two fields are at the same position within their containing records, have equal field names, and equal values.
A map is comparable with another map for equality only and only if they contain comparable values. Remember that json documents are modelled as maps, so 2 json documents can be compared for equality. More specifically, to be equal, the 2 maps must have equal sizes (number of fields) and for each field in the first map, there must exist a field in the other map such that the two fields have equal names and equal values.
An array is comparable to another array if the elements of the 2 arrays are comparable pair-wise. Comparison between 2 arrays is done lexicographically, that is, the arrays are compared like strings, with the array elements playing the role of the “characters” to compare.

As with atomic items, if two complex items are not comparable according to the above rules, false is returned. Furthermore, comparisons between atomic and complex items return false always.

The reason for returning false for incomparable items, instead of raising an error, is to handle truly schemaless applications, where different table rows may contain very different data or differently shaped data. As a result, even the writer of the query may not know what kind of items an operand may return and an operand may indeed return different kinds of items from different rows. Nevertheless, when the query writer compares “something” with, say, an integer, they expect that the “something” will be an integer and they would like to see results from the table rows that fulfill that expectation, instead of the whole query being rejected because some rows do not fulfill the expectation.

Example 1 - Fetch the passenger details from the airline baggage tracking application who have more than two transits and did not board from the SFO station

SELECT
fullname,
s.bagInfo.routing
FROM BaggageInfo s
WHERE (size(s.bagInfo[0].flightLegs) >= 3)
AND s.bagInfo[0].flightLegs[0].fltRouteSrc !=any "SFO"

Explanation: In the airline baggage tracking application, you can fetch the list of passengers who have more than two transits in their journey. The bagInfo array in the BaggageInfo table contains the information on the checked bags for passengers. The flightLegs array in the bagInfo JSON includes the source and transit details with each record corresponding to a travel leg. The first record of the flightLegs array, that is, flightLegs[0] has the details of the source location. In the above query, you fetch the details of the passengers who have more than two transits, which means there must be at least three records in the flightLegs array including the source location. You compare the size of the flightLegs array using the ‘>=’ operator. You also want to filter out the passengers who did not originate from SFO airport, so you use a != operator here. It is possible that the passengers have more than one checked bags, in which case there will be more than one element in the bagInfo array. You must consider only the first element of the bagInfo array, that is, bagInfo[0] during value comparison to avoid duplication of results.

Since the flightLegs is an array, the left operand of the comparison operator != is a sequence with more than one item. Hence, use the sequence comparison operator any in addition to the value comparison operator.

Output:

{"fullname":"Fallon Clements","routing":"MXP/CDG/SLC/BZN"}
{"fullname":"Elane Lemons","routing":"MXP/CDG/SLC/BZN"}
{"fullname":"Doris Martin","routing":"BZN/SEA/CDG/MXP"}

JSON collection table:

The following example applies a value comparison operator on a JSON collection table. Consider a sample row from the JSON collection table created for a shopping application:

{"contactPhone":"1917113999","address":{"city":"San Jose","number":501,"state":"San Francisco","street":"Maine","zip":95095},"cart":[{"item":"wallet","priceperunit":950,"quantity":2},{"item":"wall art","priceperunit":9500,"quantity":1}],"firstName":"Sharon","gender":"F","lastName":"Willard","notify":"yes","wishlist":[{"item":"Tshirt","priceperunit":500},{"item":"Jenga","priceperunit":850}]}

Example 2 - Fetch from the storeAcct table the details of shoppers who have wishlisted the item Jenga.

SELECT contactPhone, firstName
FROM storeAcct s
WHERE EXISTS s.wishlist[$element.item ="Jenga"]

Explanation: In the storeAcct table, the items wishlisted by the shoppers are stored in the JSON array wishlist. To fetch the details of the shoppers with Jenga as a wishlisted item, you verify whether or not an item by the specified name exists in the wishlist field. Use the operator ‘=’ to match the item.

Output:

{
  "contactPhone" : "1917113999",
  "firstName" : "Sharon"
}