Case Expressions
Syntax
case_expression ::= CASE
WHEN expression
THEN expression
(WHEN expression THEN expression)*
[ELSE expression]
END
Semantics
The searched CASE expression is similar to the if-then-else statements of traditional programming languages. It consists of a number of WHEN-THEN pairs, followed by an optional ELSE clause at the end. Each WHEN expression is a condition, i.e., it must return BOOLEAN?. The THEN expressions as well as the ELSE expression may return any sequence of items. The CASE expression is evaluated by first evaluating the WHEN expressions from top to bottom until the first one that returns true. If it is the i-th WHEN expression that returns true, then the i-th THEN expression is evaluated and its result is the result of the whole CASE expression. If no WHEN expression returns true, then if there is an ELSE, its expression is evaluated and its result is the result of the whole CASE expression; Otherwise, the result of the CASE expression is the empty sequence.
Example 6-88 For each user create a map with 3 fields recording the user's last name, their phone information, and the expense categories in which more than $5000 was spent
SELECT
{
"last_name" : u.lastName,
"phones" : CASE
WHEN exists u.address.phones
THEN u.address.phones
ELSE "Phone info absent or not at the expected place"
END,
"high_expenses" : [ u.expenses.keys($value > 5000) ]
}
FROM users u
The query is very similar to the one from array and map constructor. The only difference is in the use of a case expression to compute the value of the phones
field. This guarantees that the phones field will always be present, even if the path expression u.address.phones
return empty or NULL. Notice that wrapping the path expression with an explicit array constructor (as we did for the high_expenses field) would not be a good solution here, because in most cases u.address.phones
will return an array, and we don't want to have construct an extra array containing just another array.
JSON collection table:
{"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 6-89 Display promotional messages to shoppers from San Jose who have wallet or handbag items in their carts
SELECT concat("Hi ",s.firstName) AS Message,
CASE
WHEN s.cart.item =any "wallet"
THEN "The prices on Wallets have dropped"
WHEN s.cart.item =any "handbag"
THEN "The prices on handbags have dropped"
ELSE "Exciting offers on wallets and handbags"
END AS Offer
FROM storeAcct s
WHERE s.address.city =any "San Jose";
Explanation: You can use CASE statement to display a promotional message to the shoppers regarding the reduction in the prices if the shoppers have the items wallet
or handbag
in their cart. As the offers are only for shoppers from San Jose
, you specify the city in the WHERE clause.
{"Message":"Hi Sharon","Offer":"The prices on Wallets have
dropped"}