Malloy Documentation
search

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
QUERY RESULTS
[
  {
    "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.

document
run: flights -> {
  aggregate:
    distance_2003 is sum(distance) { where: dep_time ? @2003 }
    ca_flights is count() { where: origin.state ? 'CA' }
}
QUERY RESULTS
[
  {
    "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.

document
run: flights -> {
  aggregate: distance_summary is concat(total_distance::string, ' miles')
}
QUERY RESULTS