Formula Language Reference

This is a complete reference for SwitchBase's formula expression language: syntax, data types, operators, and all built-in functions.

For an introduction to formulas and common use cases, see the Formulas Overview.

Syntax Overview

• Fields: Use @ to access data, e.g., @user.name, @scores[0], @obj["key"]
• Strings: Single or double quoted, e.g., "hello", 'world'
• Numbers: Integers or floats, e.g., 42, 3.14
• Booleans: true, false
• Arrays: [1, 2, 3]
• Objects: { "key": "value" }
• Parentheses for grouping: (1 + 2) * 3

Operators

Built-in Functions

Arguments are evaluated left-to-right. Unless noted, functions return errors on wrong arity or incompatible types.

Conditional Functions

IF(condition, trueValue, falseValue)

Conditional evaluation. Condition coerced with standard truthiness rules.

IF(@age > 18, 'Adult', 'Minor')

SWITCH(expression, case1, value1, case2, value2, ...[, default])

Tests an expression against a list of cases and returns the value for the first match. If no case matches and a default is provided (odd trailing argument), it is returned. Otherwise, returns an error.

SWITCH(@status, 'active', 'green', 'pending', 'yellow', 'gray')

Returns 'gray' as default if no match.

SWITCH(@code, 1, 'one', 2, 'two', 3, 'three')

Numeric matching; errors if no match.

IFS(condition1, value1, condition2, value2, ...)

Evaluates conditions in order and returns the value for the first truthy condition. Use true as the last condition for a default/fallback. Returns an error if no condition is true.

IFS(@score > 90, 'A', @score > 80, 'B', @score > 70, 'C', true, 'F')

IFS(@age >= 65, 'senior', @age >= 18, 'adult', true, 'minor')

String Functions

UPPERCASE(string)

Returns the uppercase form of the input string.

UPPERCASE(@name)

LOWERCASE(string)

Returns the lowercase form of the input string.

LOWERCASE(@name)

REPLACE(string, old, new[, count])

Replaces occurrences of old with new in string. When count is omitted, replaces all occurrences. When count is provided, replaces at most that many (from left to right).

REPLACE(@name, ' ', '-')          // replace all spaces with dashes
REPLACE('aaa', 'a', 'b', 1)      // "baa"

CONTAINS(string, substring)

Returns true if string contains substring, false otherwise. Case-sensitive.

CONTAINS(@email, '@')
IF(CONTAINS(@status, 'error'), 'bad', 'ok')

IN(value, array) or IN(value, val1, val2, ...)

Returns true if value is equal to any element in the array or any of the subsequent arguments. Supports strings, numbers, and booleans.

IN(@status, "active", "pending", "trial")   // variadic form
IN(@role, @allowed_roles)                    // array form
IN(90, ARRAY(80, 90, 100))                  // with ARRAY function

SUBSTRING(string, start[, length])

Extracts a portion of string starting at zero-based index start. When length is omitted, returns the rest of the string. Out-of-bounds values are clamped.

SUBSTRING('hello world', 6)       // "world"
SUBSTRING('hello world', 0, 5)    // "hello"

LEN(value)

For strings and arrays, returns their length as a number.

LEN(@scores)
LEN(@message)

CONCAT(value1, value2, ...)

Concatenates all arguments into a single string.

CONCAT('Hello, ', @user.name, '!')

TRIM(string[, cutset])

Without cutset, trims Unicode whitespace from both ends. With cutset, trims any of its characters from both ends.

TRIM('  hello  ')              // 'hello'
TRIM('--hello--', '-')         // 'hello'

SPLIT(string, sep[, limit])

Splits string by sep into an array of strings. When limit is provided, splits into at most that many parts.

SPLIT('a,b,c', ',')            // ['a','b','c']
SPLIT('a,b,c', ',', 2)         // ['a','b,c']

JOIN(array, separator)

Joins array elements into a single string with separator between each element.

JOIN(ARRAY("a", "b", "c"), ", ")       // "a, b, c"
JOIN(MAP(@users, $.name), " & ")       // "Alice & Bob & Carol"

REGEX_MATCH(string, pattern[, count])

Matches a regular expression pattern against string. Uses RE2 syntax (lookahead/lookbehind are not supported).

Single match mode (without count): returns an array of the first match's capture groups. Index [0] is the full match, [1] is the first group, etc. Returns an empty array if no match.

Multi-match mode (with count): returns an array of arrays, one per match. Use 0 for all matches.

REGEX_MATCH("hello world", "world")                     // ["world"]
REGEX_MATCH("john@example.com", "(\w+)@(\w+)")          // ["john@example", "john", "example"]
REGEX_MATCH(@email, "(\w+)@(\w+)")[1]                    // "john"
REGEX_MATCH("cat bat hat", "(\w)at", 0)                  // [["cat","c"], ["bat","b"], ["hat","h"]]
LEN(REGEX_MATCH(@input, "error")) > 0                    // boolean test for pattern presence

Data Structure Functions

OBJECT(key1, value1, key2, value2, ...)

Builds an object from key/value pairs. Keys must be strings.

OBJECT('name', @user.name, 'tags', ARRAY('a','b'))

ARRAY(value1, value2, ...)

Returns an array of the provided arguments.

ARRAY(1, 2, 3)

MERGE(obj1, obj2, ...)

Shallow merge of objects into a new object. Later keys override earlier ones.

MERGE(@defaults, @overrides)

PARSE_CSV(csv_string[, delimiter])

Parses a CSV string into an array of objects. The first row is used as headers. Each subsequent row becomes an object with header names as keys. Numeric values are automatically converted to numbers. Supports quoted fields. Default delimiter is comma (,).

PARSE_CSV("name,age\nAlice,30\nBob,25")
// [{"name": "Alice", "age": 30}, {"name": "Bob", "age": 25}]

PARSE_CSV("name;age\nAlice;30", ";")
// [{"name": "Alice", "age": 30}]

PARSE_CSV(@csv)[0]["First Name"]
// access field with spaces in name

Array Functions

FILTER(array, condition)

Returns a new array containing only elements where condition evaluates to truthy. Use $ to reference the current item, and $.field to access fields on objects.

FILTER(@numbers, $ > 5)
FILTER(@users, $.active)
FILTER(@users, $.age >= 18 && $.active)
FILTER(@items, $.category.type == "electronics")

Nested FILTER:

FILTER(@teams, LEN(FILTER($.members, $.active)) > 0)

Chaining:

FILTER(@users, $.age >= 18)[0].name     // name of first adult
LEN(FILTER(@users, $.active))           // count active users

MAP(array, expression)

Returns a new array with each element transformed by expression. Use $ to reference the current item.

MAP(@users, $.name)                                          // extract names
MAP(@prices, $ * 1.1)                                        // increase all by 10%
MAP(@users, UPPERCASE($.name))                               // transform with functions
MAP(@users, OBJECT("label", $.name, "adult", $.age >= 18))   // transform to new objects

Composing with FILTER and JOIN:

MAP(FILTER(@users, $.active), $.name)    // names of active users
JOIN(MAP(@users, $.name), ", ")          // comma-separated name list

Date & Time Functions

NOW()

Returns the current timestamp as an ISO 8601 / RFC 3339 formatted string in UTC.

TODAY()

Returns today's date at midnight UTC as an ISO 8601 / RFC 3339 formatted string.

DATE(value[, layout[, timezone]])

Parses a date from a string, number (Unix seconds), or time value and returns an ISO 8601 / RFC 3339 formatted string. When a custom layout is provided, it uses the same format syntax as FORMAT_DATE (e.g., YYYY-MM-DD). When timezone is provided, the input is interpreted as being in that timezone (IANA names, case-insensitive).

DATE("2024-01-15")
DATE("2024-01-15 13:00:00", "YYYY-MM-DD HH:mm:ss", "America/Denver")
DATE("2024-01-15T13:00:00-07:00")

FORMAT_DATE(date, layout[, timezone])

Formats a date using user-friendly patterns. When timezone is provided, the date is converted to that timezone before formatting. Timezone names are case-insensitive.

Supported patterns:

FORMAT_DATE(@created_at, 'YYYY-MM-DD')
FORMAT_DATE(@created_at, 'YYYY-MM-DD HH:mm', 'America/New_York')

DATE_ADD(date, amount, unit)

Adds a duration to a date. Returns an ISO 8601 / RFC 3339 formatted string.

Supported units: seconds, minutes, hours, days, weeks (case-insensitive).

DATE_SUB(date, amount, unit)

Subtracts a duration from a date. Returns an ISO 8601 / RFC 3339 formatted string.

Supported units: seconds, minutes, hours, days, weeks (case-insensitive).

DATE_DIFF(date1, date2[, unit])

Returns the difference between two dates. Defaults to seconds when unit is omitted.

DATE_PART(date, part)

Extracts a component (e.g., year, month, day, iso_week, epoch_seconds) as a number.

DATE_FROM_PARTS(year, month, day[, hour[, minute[, second]]])

Constructs a UTC timestamp from numeric parts. Returns an ISO 8601 / RFC 3339 formatted string.

CONVERT_TZ(date, timezone)

Converts a date to a different timezone. Returns an ISO 8601 / RFC 3339 formatted string with the timezone offset. Uses IANA timezone names (case-insensitive). Handles daylight saving time automatically.

CONVERT_TZ(NOW(), 'America/Denver')    // "2024-01-15T13:00:00-07:00"

For a complete list of valid IANA timezone identifiers, see the tz database.

Math & Aggregate Functions

SUM(array)

Returns the sum of all numeric elements in the array. Non-numeric elements are skipped. Returns 0 for an empty array.

SUM(ARRAY(1, 2, 3))                  // 6
SUM(MAP(@orders, $.total))           // sum of order totals

AVERAGE(array)

Returns the arithmetic mean of all numeric elements. Non-numeric elements are skipped. Errors on empty array.

AVERAGE(ARRAY(2, 4, 6))              // 4
AVERAGE(MAP(@users, $.age))          // average user age

MIN(array)

Returns the smallest numeric value in the array. Errors on empty array.

MIN(MAP(@products, $.price))

MAX(array)

Returns the largest numeric value in the array. Errors on empty array.

MAX(MAP(@scores, $.value))

ROUND(number[, precision])

Rounds a number to the given precision (decimal places). Defaults to 0. Negative precision rounds to powers of ten. Uses round-half-away-from-zero.

ROUND(3.456, 2)       // 3.46
ROUND(3.5)            // 4
ROUND(1234, -2)       // 1200

TO_FIXED(number, decimals)

Formats a number as a string with exactly decimals decimal places. Useful for display/currency formatting.

TO_FIXED(3.14159, 2)  // "3.14"
TO_FIXED(3.1, 3)      // "3.100"
TO_FIXED(5, 2)        // "5.00"

CEIL(number)

Returns the smallest integer greater than or equal to the number (rounds up).

CEIL(3.2)              // 4
CEIL(-3.2)             // -3

FLOOR(number)

Returns the largest integer less than or equal to the number (rounds down).

FLOOR(3.9)             // 3
FLOOR(-3.2)            // -4

Truthiness Rules

Field Access & Error Handling

• Missing fields or invalid array accesses (non-array or out-of-range) evaluate to an empty string.
• Errors include argument count/type mismatches and invalid operator usage.
• Nested access is supported: @user.friends[0].name

Combined Examples

// Build objects and arrays
OBJECT('name', @user.name, 'tags', ARRAY('a','b'))

// Nested access
@user.friends[0].name

// Combined usage
IF(LEN(@items) > 0, SPLIT(CONCAT(@prefix, ':', @suffix), ':'), ARRAY())