Malloy Filter Expressions

VSCode

Malloy includes specialized sub-languages for filtering string, number, date, timestamp or boolean data. There is a specialized language for each data type designed to create concise, powerful, and readable data filters.

A filter expression is a special "f-string", starting with f' and ending with '

A filter expression is applied to a value with the ~ or !~ operators. The result of a filter expression comparison will be a boolean value. Here are examples of a filter expressions in Malloy for each data type.

dimension:
  is_primary_color is color ~ f'red,green,blue'
  one_digit is num_val ~ f'(1,9)'
  did_not_happen is have_pictures ~ f'false'
  happened_this_week is event_timestamp ~ f'this week'
  total_this_quarter is num_val.sum() { where: order_date ~ f'this quarter' }

String Filter Expressions

VSCode

Examples of String Filter Expressions

String Filter Expression	Meaning
`correct`	Is the string `correct`
`happy%`	Starts with `happy`
`%joy`	Ends with `joy`
`%love%`	Contains `love`
`abc_`	`abc` followed by any character
`null`	Is a `NULL` value
`\null`	Is the string `null`
`ten\%`	Is the string `ten%`
`empty`	Is the empty string or is `NULL`

The \ character is used to escape any special meaning

Negation in String Filter Expressions

Negated Expression	Meaning
`-mistake`	Is not the string `mistake`
`-empty`	Is not an empty string
`-%anger`	Does not end with `anger`
`-null`	Is not a `NULL` value

A - before a string expression means "not"

Combining String Filter Expressions

There are three seperators that can be used to combine string filter expressions: , (the comma operator), ; (the 'and' operator), | (the 'or' operator) Also groups of string clauses can use ( and ) for grouping.

A string of clauses seperated by , will have all the positive matches or'd together (included), and all the negative matches anded (excluded)

Combined Expression	Meaning
`red,blue,green`	Is one of the primary colors
`red \| blue \| green`	Is one of the primary colors
`joy%,-%sadness%`	Starts with `joy` and does not contains `sadness`
`joy% ; -%sadness%`	Starts with `joy` and does not contains `sadness`
`\ % , %\_`	Starts with space or ends with `_`

White space at the beginning or end of a clause is ignored
You will need to use \ (backslash) to escape ...
- Spaces at the beginning or end of a match
- Any of , | ; \ ( )
- The match charcacters % and _
- \null or \empty

Numeric Filter Expressions

VSCode

Numeric Filter Expression	Meaning
`42`	Equals 42
`= 42`	Equals 42
`!= 42`	Not Equal 42
`> 42`	Could also be `<` `<=` `>=`
`(1 to 5)`	1, 2, 3, 4, or 5
`[1 to 5]`	2, 3, or 4
`null`	The `NULL` value

In to ranges the () characters indicate inclusion of the endpoints, and [] indicate exclusion.

Negation in Numeric Filter Expressions

Negated Expression	Meaning
`not 21`	Does not equal 21
`not (1,2,3)`	Less than 1 or greater than 3
`not null`	Is not a `NULL` value

Combining Numeric Filter Expressions

Numeric filters can be combined with and and or operators and grouped with ( and )

Filters using = and != can have a list of values separated by ,

Combined Expression	Meaning
`= 2,4,6,8`	Equals one of 2,4,6 or 8
`2,4,6,8`	Equals one of 2,4,6 or 8
`!= 1,3,5,7`	Does not equal any of 1,3,5,7
`> 10 and != 15`	Greater than 10 and not equal 15
`(0 to 4) or (5 to 9)`	Exclude between for and 5

Temporal Filter Expressions

VSCode

Temporal Units

Malloy has a temporal filter language for operation on date and timestamp data. The first type of temporal filters are filters which describe a unit of time. Expressions which take a time extent can use year, quarter, month, week, day, hour, or minute, and any of those can have an s (as in years or days)

Temporal Unit Expression	Meaning
`now`	The current time
`last week`	The sunday week which ended before the current day
`next month`	The next month
`this quarter`	The quarter containing the current day
`today`	The current day
`yesterday`	The day before the current day
`tomorrow`	The day after the current day
`2 days ago`	The day before yesterday
`1 week from now`	Same as seven days from now
`monday`	The most recently passed monday
`last monday`	The most recently passed monday
`next friday`	The first friday in the future

Temporal Ranges

For all temporal units except now, the unit describes a range of time. There are a other ways to describe a range, many of which start with a temporal unit

Temporal Range Expression	Meaning
`before tuesday`	Before any temporal unit
`after next month`	After any temporal unit
`last monday to next friday`	Using two units to specify a range
`2 weeks ago for 2 days`	Can combine a unit and a duration
`last 3 days`	3 days of data, does not include today
`next 2 weeks`	2 weeks of data, does not include today
`2 days`	2 days of data, yesterday and today
`null`	The `NULL` value

Combining Temporal Filter Expressions

Temporal filter expressions can be combined with and and or operators and grouped with ( and )

Boolean Filter Expressions

VSCode

Because a boolean value might equal NULL, there are the following boolean filters.

Boolean Filter Expression	Meaning
`true`	Value is `true`
`false`	Value is `false` or `NULL` (the inverse of `true`)
`=false`	Value is `false`
`null`	Value is `NULL`

Boolean filters are negated with not. There are no combination or grouping operators.

More About F-Strings

VSCode

Escaping is different inside of a f-string than it is inside of a normal string. The backslash character does escape, but it will also be part of the resulting string. In most other strings, in order for the string to contain a backlash you must write two backslashes. This will only matter for string filter expressions which use \.

F-strings can also be written f"..." or f`...` if one of those quotation choices produces an easier to read expression

There are also tripled versions of each f-string, f'''...''', f"""...""" and f```...```

f"Don't need to quote the apostrophe"
f`Don't need to quote the " either`
f'''Or here, can use ` ' and " without quoting them'''