DDSQL Scalar Functions

Join the Beta!

DDSQL is in private beta.

Request Access

These functions return one value per row.

String functions and operators

NameReturn typeDescription
upper(text s)textConverts s to uppercase.
lower(text s)textConverts s to lowercase.
length(text s)integerCounts the number of characters in s.
concat(expr x, y, …)textConcatenates the provided expressions.
substr(expr s, numeric start, numeric numChars)textReturns a substring of s from start to a max of numChars, if provided. start is a 1-based index, so substr('hello', 2) returns 'ello'. If the start is less than 1, it is treated as if it were 1. The result is computed by taking the range of characters [start, start+numChars], where if any value is less than 1, it is treated as 1. This means substr('hello', -2, 4) returns 'h'.
replace(text s, text from, text to)textReplaces all occurrences in s of substring from with substring to.
regexp_replace(text s, text pattern, text replacement)textReplace substrings in s that match the POSIX regular expression pattern with the replacement. Supports Go’s regular expression syntax.

Mathematical functions and operators

NameReturn typeDescription
abs(numeric n)integerReturns the absolute value of n.
round(numeric n, [s])numericRound n to s decimal places.
mod(numeric x, numeric y)integerReturns the remainder of x / y.
floor(numeric n)numericReturns the nearest integer that is less than or equal to n.
ceil(numeric n)numericReturns the nearest integer that is greater than or equal to n.
power(numeric n, numeric s)numericRaises n to the s power.
ln(numeric n)numericCalculates the natural logarithm of n.
sqrt(numeric n)numericCalculates the square root of n.

Array functions and operators

NameReturn typeDescription
array_length(array a)integerReturns the length of the array a for each row.
array_contains(array a, expr e)booleanReturns true if the value the expr e evaluates to is in the array a for each row.
array_cat(array a, array b)arrayReturns a new array containing the combined elements from array a and array b.
array_append(array a, expr e)arrayReturns a new array that includes all the original elements of the input array followed by the appended element.
string_to_array(text s, delimiter, [,nullString])arrayReturns an array of substrings obtained by splitting the input string s, using the specified delimiter. The third argument, nullString, is optional and specifies substrings that are replaced with NULL.
array_to_string(array a, delimiter, [,nullString])stringConcatenates array elements using supplied delimiter and optional null string.
unnest(array a)variableReturns each element in the array as a separate row. The return type is the element type of the array.
unnest can only be used in the SELECT clause of a query. If other columns are SELECTed with unnest, the value at each row in the table is repeated at each output row with each unnested element. If multiple columns are being unnested, all the unnested columns are zipped up together, with NULL filling in the output values for shorter arrays.

Date/time functions and operators

date_trunc

NameReturn typeDescription
date_trunc(string precision, timestamp t)timestampTruncates the timestamp to the chosen precision (“second”, “minute”, “hour”, “day”, “week”, “month”, or “year”).

Conditional expressions

NameReturn typeDescription
coalesce(expr x, y, …)variableReturns the first non-null expression.
nullif(expr x, expr y)variableReturns NULL if both arguments are equal. Otherwise, returns x.

JSON functions and operators

NameReturn typeDescription
json_extract_path_text(text json, text path…)textExtracts the JSON sub-object in JSON as text, defined by the path. Its behavior is equivalent to the postgres function with the same name. For example, json_extract_path_text(col, ‘forest') returns the value of the key forest for each JSON object in col. See the example below for a JSON array syntax.

JSON array

Return the 0th element in a JSON array under the key forest in each JSON object or row in col.

[{
"forest": "trees"
}]
json_extract_path_text(col, ‘forest', ‘0')