Functions

VSCode

Malloy has a standard set of functions available at all times. These include scalar functions (e.g. concat), aggregate functions (e.g. stddev), and analytic (or "window") functions (e.g. lag).

These functions are defined in Malloy in order to provide basic typechecking of arguments and return types, as well as some compatibility transformations. Perfect compatibility across all dialects for all functions is not guaranteed, and specific differences are noted on a case-by-case basis.

For quick function lookup, see the alphebetized table of all available functions.

Note: the behavior of functions changed in v0.0.39. For more information, see a description of that change.

Syntax

Built-In Functions

Functions that are "built-in" to Malloy can be called the usual way with the name of the function, an open parenthesis, a comma separated list of Malloy expressions, and a close parenthesis, e.g. concat(upper(first), ' ', upper(last)).

Raw SQL Functions

Functions that are not built in may be called in the same way, but with an ! in between the function name and argument list: sinh!(x). In this case, Malloy assumes the return type of the function is the same as that of the first argument (or number if there are no arguments). When this is not correct, the return type can be specified after the !, e.g. hash!number(username).

Function Documentation

String Functions

concat

concat(value, ...)

Concatenates multiple values together, casting non-string values to string. The exact behavior of string casting may depend on the dialect.

If no values are given, concat returns the empty string.

Behavior for null depends on dialect: in BigQuery, if any argument is null, the result is null; in DuckDB and Postgres, null is treated as an empty string.

run: empty -> {
  group_by: 
    e1 is concat('foo', 'bar')
    e2 is concat(1, null)
    e4 is concat('Date: ', @2021-01-23)
    e3 is concat()
}

QUERY RESULTS

HTML JSON SQL

[
  {
    "e1": "foobar",
    "e2": "1",
    "e4": "Date: 2021-01-23",
    "e3": ""
  }
]

SELECT 
   CONCAT('foo','bar') as "e1",
   CONCAT(1,NULL) as "e2",
   CONCAT('Date: ',DATE '2021-01-23') as "e4",
   '' as "e3"
FROM (
    SELECT 1
  ) as base
GROUP BY 1,2,3,4
ORDER BY 1 asc NULLS LAST

lower

lower(value)

Returns a string like value but with all alphabetic characters in lowercase.

run: empty -> {
  group_by: e1 is lower('FOO')
}

QUERY RESULTS

HTML JSON SQL

[
  {
    "e1": "foo"
  }
]

SELECT 
   LOWER('FOO') as "e1"
FROM (
    SELECT 1
  ) as base
GROUP BY 1
ORDER BY 1 asc NULLS LAST

upper

upper(value)

Returns a string like value but with all alphabetic characters in uppercase.

run: empty -> {
  group_by: e1 is upper('foo')
}

QUERY RESULTS

HTML JSON SQL

[
  {
    "e1": "FOO"
  }
]

SELECT 
   UPPER('foo') as "e1"
FROM (
    SELECT 1
  ) as base
GROUP BY 1
ORDER BY 1 asc NULLS LAST

strpos

strpos(test_string, search_string)

Returns the 1-based position of the first occurrence of search_string in test_string, or 0 if search_string is not found.

run: empty -> {
  group_by: 
    e1 is strpos('abc', 'b')
    e2 is strpos('abc', 'd')
    e3 is strpos('abc', null)
    e4 is strpos(null, 'a')
}

QUERY RESULTS

HTML JSON SQL

[
  {
    "e1": 2,
    "e2": 0,
    "e3": null,
    "e4": null
  }
]

SELECT 
   STRPOS('abc','b') as "e1",
   STRPOS('abc','d') as "e2",
   STRPOS('abc',NULL) as "e3",
   STRPOS(NULL,'a') as "e4"
FROM (
    SELECT 1
  ) as base
GROUP BY 1,2,3,4
ORDER BY 1 asc NULLS LAST

starts_with

starts_with(value, prefix)

Returns true if value begins with prefix and false otherwise. If either value or prefix is null, the result is false (unlike in SQL, where it would be null).

run: empty -> {
  group_by: 
    e1 is starts_with('abc', 'a')
    e2 is starts_with('abc', 'b')
    e3 is starts_with('abc', null)
    e4 is starts_with(null, 'a')
}

QUERY RESULTS

HTML JSON SQL

[
  {
    "e1": true,
    "e2": false,
    "e3": false,
    "e4": false
  }
]

SELECT 
   COALESCE(STARTS_WITH('abc', 'a'), false) as "e1",
   COALESCE(STARTS_WITH('abc', 'b'), false) as "e2",
   COALESCE(STARTS_WITH('abc', NULL), false) as "e3",
   COALESCE(STARTS_WITH(NULL, 'a'), false) as "e4"
FROM (
    SELECT 1
  ) as base
GROUP BY 1,2,3,4
ORDER BY 1 asc NULLS LAST

ends_with

ends_with(value, suffix)

Returns true if value ends with prefix and false otherwise. If either value or suffix is null, the result is false (unlike in SQL, where it would be null).

run: empty -> {
  group_by: 
    e1 is ends_with('abc', 'c')
    e2 is ends_with('abc', 'b')
    e3 is ends_with('abc', null)
    e4 is ends_with(null, 'a')
}

QUERY RESULTS

HTML JSON SQL