Formulas

Formulas can operate on values within one column and access values in other columns within the row. Common use cases are concatenation, conditional logic, arithmetic operations, and replication of part or all of another column. Many scripts that begin with SELECT * can be expressed as a formula, because formulas automatically add a single expression into a well-formed SELECT.

The default for all columns is to be arrays of strings, even if they contain only one value. Therefore, certain additional array functions are needed to handle transformations correctly. The following is a list of useful array functions:

• array converts a string into an array, in order to keep columns consistent.
• len is useful when writing CASE statements if you need to handle different array lengths differently. This function differs from the length function, which returns the length of a string.
• to_string allows you to access a particular index of a multi-value field. to_string(<attributeName>) is the preferred way to access strings from a single-value field.
• array.concat lets you concatenate arrays. Note that array concatenation adds values to an array in such a way that array_concat(["A", "B"]) and array.concat(["AB"]) both end up as "AB".

The output attribute is the one to which output is written. Note that this overwrites any values already in that attribute. Attribute names are case sensitive, but function names and keywords, such as case, when, and empty are not.