Function CASE projects one or multiple aliases to a new alias. Starting from the first WHEN, this function checks a data row against the condition claimed in each WHEN, once a condition is met the projection result in the correspondent THEN will be returned, and the rest of WHEN and ELSE will be skipped.

When projecting multiple aliases to a table, please make sure the values in the same table column have same data type; all keywords involved in this function are case insensitive.

Syntax 1

case
  when <condition1> then <projection1>
  when <condition2> then <projection2>
  ...
  else <other>
end

Syntax 2:

case <expression>
  when <value1> then <projection1>
  when <value2> then <projection2>
  ...
  else <other>
end

• <projectionN> is the expression to compute when <conditionN> is met in Syntax 1, or when <expression> equals <valueN> in Syntax 2
• <other> is the expression to compute when none of the conditions is met; a 0 or empty string will be generated if ELSE is omitted
• Projecting to NODE/EDGE will be processed as projecting to the UUID of NODE/EDGE, while projecting to PATH will be processed as projecting to the row number of PATH

If multiple aliases are non-homologous, Cartesian Product will be applied when the function is called in a WITH clause, or aliases will be trimmed when called in a RETURN clause.

Constant

Example: divide @customer nodes into 4 age groups based on birth years: 1980s, 1990s, and 2000s, count the number of people in each age group

find().nodes({@customer}) as nodes
with CASE
when year(nodes.birthday) <=> [1980, 1989] then "1980s"
when year(nodes.birthday) <=> [1990, 1999] then "1990s"
when year(nodes.birthday) <=> [2000, 2009] then "2000s"
else "others" END as ageGroup
group by ageGroup
return ageGroup, count(ageGroup)

Example: abbreviate the example above

find().nodes({@customer}) as nodes
with CASE floor(year(nodes.birthday)/10)
when 198 then "1980s"
when 199 then "1990s"
when 200 then "2000s"
else "others" END as ageGroup
group by ageGroup
return ageGroup, count(ageGroup)

Function

Example: calculate the actual monthly paydays in 2022, with 15th each month as planned payday and payday will be put off to the nearest Monday if it is a non-workday.

uncollect ["2022-1-15 0:0:0","2022-2-15 0:0:0","2022-3-15 0:0:0","2022-4-15 0:0:0","2022-5-15 0:0:0","2022-6-15 0:0:0","2022-7-15 0:0:0","2022-8-15 0:0:0","2022-9-15 0:0:0","2022-10-15 0:0:0","2022-11-15 0:0:0","2022-12-15 0:0:0"] as plan
return CASE dayOfWeek(plan)
when 1 then dateAdd(plan, 1, "day")
when 7 then dateAdd(plan, 2, "day")
else dateAdd(plan, 0, "day")
END

Analysis: the first condition in case adds 1 day to a planned payday that happens to be a Sunday, the second condition adds 2 days to a planned payday on Saturday , ELSE keeps the planned payday as it is by adding 0 day. Note: ELSE can not be followed by plan, for plan's structure type is string, dateAdd() can only be filled with time, so to keep data structure type consistent plan should be transformed to a time format.

Alias

Example: find one 1-step path from each card CA001, CA002, and CA005, count each path as [<transferCardID>, <result>,<receivercardID>]; if a path do not exist, count it as [<CardID>, "0", "none"]

find().nodes({_id in ["CA001","CA002","CA005"]}) as cards
optional n(cards).le({@transfer} as e).n({@card} as n).limit(1)
return CASE n
when 0 then [cards._id, "0", "none"]
else [cards._id, e.result, n._id] END

Analysis: 1) when using optional, if one of the template query does not return result, then conduct a psyeudo-path 0-0-0; 2）use 'CASE' to find the psyeudo-path 0-0-0 and map it as [<CardID>, "0", "none"], map the rest of paths as a table [<TransferorCardID>,<result>,<ReceiverCardID>], note that data type should be consistent in each column.