Expressions in Malloy are much like expressions in any other language; they can have variables and operators and function calls in the same syntax users are familiar with. However, Malloy also introduces several other kinds of expressions useful for the task of data analysis and transformation.
Section | Examples |
---|---|
Identifiers Fields referenced by name |
origin.city `year` |
Mathematical operations | x * 100 -cost (a + b) / c |
Comparison operators | x > 200 state != 'cancelled |
Boolean operators Logically combine booleans |
height > 10 and height < 100 is_cancelled or is_delayed not is_commercial_flight |
Functions | floor(10.35) concat(first_name, ' ', last_name) cbrt!(x) timestamp_seconds!timestamp(val) |
Aggregation | sum(distance) aircraft.count() aircraft_models.seats.avg() |
Aggregation Control / Subtotals | all(sum(distance)) all(aircraft.count(), destination_code) exclude(aircraft_models.seats.avg(), carrier.nickname) |
Filtered expressions | avg(age) { where: state = 'CA' } flight_count { where: origin.county is not null } |
Type cast |
|
Safe type cast | birth_year::number start_time_string::timestamp |
Pick expressions Malloy's take on CASE statements |
pick 'S' when size < 3 else 'L' |
Time ranges Ranges with start and end or duration |
start_time for 3 hours @2003 for 10 months @2003 to @2005 |
Numeric ranges Numeric ranges with start and end |
10 to 20 |
Time truncation | event_time.quarter now.year |
Time extraction Extract one part of a time value |
day_of_year(event_time) minute(now) |
Interval extraction Extract the interval between two times |
days(created_at to shipped_at) |
Time literals | @2003-04-19 @2020-Q4 @2021-10-24 10:00:00 |
Partial comparison Reusable conditions |
> 42 is not null ~ r'C.*' |
Alternation Logically combine conditions |
> 5 & < 10 'red' | 'blue' |
Application Apply conditions to values |
state ? 'CA' weight ? > 100 & < 1000 |
Null Operations Expressions relating to NULL values |
eventStatus != null eventStatus ?? 'unknown' |
Operator Precedence | a + b * c and d ?? e > f |
Identifiers
Fields may be referenced by name, and fields in joins or nested structures can be accessed using a period (.
) as in item.item_property
Escaping reserved keywords
If a column name in a table conflicts with a keyword in Malloy, use backquotes to quote the keyword. Back-quoted identifiers can also
contain spaces and any special characters the database supports in field names; .
s in back-quoted identifiers are not currently supported.
dimension: year_plus_one is `year` + 1 `name with spaces` is some value
[ { "county": "CUYAHOGA" }, { "county": "FRANKLIN" }, { "county": "MONTGOMERY" } ]
WITH __stage0 AS ( SELECT group_set, origin_0."state" as "state__0", CASE WHEN group_set=1 THEN origin_0."county" END as "county__1", CASE WHEN group_set=1 THEN COUNT(1) END as "flight_count__1" FROM '../data/flights.parquet' as base LEFT JOIN '../data/airports.parquet' AS origin_0 ON origin_0."code"=base."origin" CROSS JOIN (SELECT UNNEST(GENERATE_SERIES(0,1,1)) as group_set ) as group_set WHERE origin_0."county" IS NOT NULL GROUP BY 1,2,3 ) , __stage1 AS ( SELECT "state__0" as "state", COALESCE(LIST({ "county": "county__1", "flight_count": "flight_count__1"} ORDER BY "flight_count__1" desc NULLS LAST) FILTER (WHERE group_set=1),[]) as "by_county" FROM __stage0 GROUP BY 1 ) SELECT by_county_0."county" as "county" FROM __stage1 as base LEFT JOIN LATERAL (SELECT UNNEST(base."by_county"), 1 as ignoreme) as by_county_0_outer(by_county_0,ignoreme) ON by_county_0_outer.ignoreme=1 LIMIT 3
Identifiers that share a name with a keyword in Malloy must be enclosed in back ticks `
, e.g. `year`
.
Mathematical Operators
Typical mathematical operators +
, -
, *
, /
, and %
(modulo) work as expected, and parentheses may be used to override precedence, e.g. six is 10 * (3 - 2) / 2 + 1
.
The unary minus / negation operator is also allowed, e.g. value is -cost
.
Logical Operators
Comparison Operators
Standard comparison operators >
, <
, >=
, <=
, and =
are available in Malloy. "Not equals" is expressed using the !=
operator.
Boolean Operators
Malloy includes the basic binary boolean operators and
and or
, as well as the unary not
operator.
Functions
Many functions available in SQL are available unchanged in Malloy. Malloy provides some compatibility transformations for known functions. Unknown functions may be called like cbrt!(x)
or timestamp_seconds!timestap(value)
to specify a return type. For detailed information, as well as a full list of all available functions, see the Functions section.
Aggregation
Aggregations may included in an expression to create measures, e.g. count()
, sum(distance)
, or aircraft_models.seats.avg()
. For detailed information, see the Aggregates section.
Filtered Expressions
Aggregate expressions may be filtered, using the usual filter syntax.
run: flights -> { aggregate: distance_2003 is sum(distance) { where: dep_time ? @2003 } ca_flights is count() { where: origin.state ? 'CA' } }
[ { "distance_2003": 42545813, "ca_flights": 40670 } ]
SELECT COALESCE(SUM(CASE WHEN (base."dep_time">=TIMESTAMP '2003-01-01 00:00:00') and (base."dep_time"<TIMESTAMP '2004-01-01 00:00:00') THEN base."distance" END),0) as "distance_2003", COUNT(CASE WHEN origin_0."state"='CA' THEN 1 END) as "ca_flights" FROM '../data/flights.parquet' as base LEFT JOIN '../data/airports.parquet' AS origin_0 ON origin_0."code"=base."origin"
Type Cast
Type cast may be accomplished with the ::
operator.
Type casting may be accomplished with the ::type
syntax.