Pattern Matching

Results of simple expressions as well as of entire subqueries can be stored in variables. To declare a variable, use the LET keyword followed by the variable name, an equal sign and the expression. The code must be in parentheses if the expression is a subquery.

LET h = FLOOR(f.DepTime / 100)
LET m = f.DepTime % 100
LET formatted = CONCAT(h, ':', m)
RETURN { hours: h, minutes: m, formatted }

The time format is like 1245. Hours and minutes can be separated with some math: divide by 100 and round off the decimal places for the hours part and modulo 100 for the division remainder to get the minutes part.

The calculated values are stored in two different variables h and m. These variables are then used to create another variable formatted like „12:45“ and all three are returned as result.

RETURN { myVariable } is a short form of RETURN { myVariable: myVariable } if you wondered.

In below example, the hours and minutes of the departure time are pre-calculated and stored in the variables h and m. They are used further down in the RETURN statement to create an ISO timestamp (DATE_ISO8601() documentation) together with the date attributes of the data – ignoring time zones and daylight saving time; we will later use the included UTC timestamps for time calculations:

FOR f IN flights
  FILTER f._from == 'airports/BIS'
  LIMIT 100
  LET h = FLOOR(f.DepTime / 100)
  LET m = f.DepTime % 100
  RETURN {
    year: f.Year,
    month: f.Month,
    day: f.Day,
    time: f.DepTime,
    iso: DATE_ISO8601(f.Year, f.Month, f.Day, h, m)
  }