Expressions

VSCode

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.

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' }
}

QUERY RESULTS

HTML JSON SQL

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

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

QUERY RESULTS

HTML JSON SQL