Function reference

Functions compute a value for each row. Predicates are used in filter expressions, and functions that produce values can be used in map, group, and other places that take a scalar expression.

General functions

at(column, position) / column[position]

Return the character at a 1-based position. A position beyond the value returns an empty string; null remains null. column[position] is shorthand for column.at(position).

data.map({symbol := S[501]})
data.group(by := {S[501]}, aggs := {count := count()})

isoWeek(input)

Return the ISO 8601 week date of a date as a string of the form <ISO-year>-W<ISO-week>, such as 2026-W12. The zero-padded week keeps the values sortable within a year. The ISO week-numbering year can differ from the calendar year around the turn of the year: 2021-01-01 falls into 2020-W53.

data.map({week := date.isoWeek()})

concat(left, right)

Join two strings into one. Both arguments must be strings, and the result is null if either of them is null. Nest calls to join more than two strings.

data.map({place := concat(concat(country, ' / '), division)})

between(column, from, to)

Test an inclusive date or numeric range. Use null for an open bound. For float columns, the upper bound is currently exclusive.

date.between('2024-01-01'::date, '2024-12-31'::date)
age.between(18, null)

in(column, {values}) / in(column, subquery)

Test whether a column equals one of the values in a set. String, integer, float, date, and boolean columns are supported, and every value must be a literal of the column’s type. An empty set matches no rows.

Instead of a set, the values can come from a subquery that returns exactly one column of the column’s type. The subquery cannot refer to the row being tested. RhyDB runs it once while planning the query, and its non-null values then act like a set. Subqueries are supported in filter and map.

country.in({'Germany', 'France', 'Italy'})
age.in({30, 40})
date.in({'2021-01-01'::date, '2021-06-01'::date})
data.filter(country = 'Germany' && pangoLineage.in(
data.filter(country = 'Switzerland')
  .group(by := {pangoLineage}, aggs := {count := count()})
  .filter(count > 5000)
  .project({pangoLineage})
))

isNull(column) / isNotNull(column)

Test whether a column value is null or non-null. These are the only way to filter on missing values, because comparing a column against the null literal is an error.

isNotNull(date)

like(column, pattern)

Match a string with an RE2 regular expression.
division.like('Basel.*')

Lineage and phylogenetic functions

lineage(column, value [, includeSublineages := bool] [, recombinantFollowingMode := string])

Match a lineage of a column with a lineage definition, written as its canonical name or as one of its aliases. includeSublineages := true also matches every lineage below it. A value that is neither a lineage nor an alias of the definition matches nothing. The value cannot be null; use isNull() instead.

With includeSublineages := true, recombinantFollowingMode decides which recombinant lineages count as sublineages: ‘doNotFollow’ (default) follows no recombinant edges, ‘alwaysFollow’ includes recombinants with at least one parent in the searched clade, and ‘followIfFullyContainedInClade’ includes recombinants whose parents all lie in it.

pangoLineage.lineage('B.1.1.7', includeSublineages := true)
pangoLineage.lineage('XBB', includeSublineages := true, recombinantFollowingMode := 'alwaysFollow')

lineageFromTables(column, value, lineages, aliases [, includeSublineages := bool] [, recombinantFollowingMode := string])

Work like lineage(), but read the lineage tree from the named tables. lineages needs the columns lineage and parent, and aliases the columns alias and lineage. column.lineage(value) is the same as column.lineageFromTables(value, column, column_aliases) over the tables that preprocessing creates for every lineage column; other tables with these columns can define a different tree.

pangoLineage.lineageFromTables('BA.2', pangoLineage, pangoLineage_aliases, includeSublineages := true)

phyloDescendantOf(column, node)

Test whether a value descends from a node in a configured phylogenetic tree.
usherTree.phyloDescendantOf('NODE_0000072')

Sequence functions

●
Note

Every sequence predicate requires a sequence name. Positions are 1-based. Nucleotide and amino-acid functions have parallel forms where shown.

nucleotideEquals(position := n, symbol := s, sequenceName := name)

Test the symbol at a nucleotide reference position. Use . to represent the reference symbol. aminoAcidEquals is the amino-acid form.

nucleotideEquals(position := 23403, symbol := 'G', sequenceName := 'main')
aminoAcidEquals(position := 501, symbol := 'Y', sequenceName := 'S')

hasMutation(position := n, sequenceName := name) / hasAAMutation(...)

Test whether a position differs from the reference and is not an unknown symbol.
hasMutation(position := 23403, sequenceName := 'main')

insertionContains(position := n, value := regex, sequenceName := name)

Test whether an insertion after a reference position matches a pattern of sequence symbols and .. Position 0 is before the first symbol. aminoAcidInsertionContains is the amino-acid form, in which the stop symbol is written as \*.

insertionContains(position := 22204, value := 'A.*G', sequenceName := 'main')
aminoAcidInsertionContains(position := 214, value := '.*EPE', sequenceName := 'S')

maybe(child) / exact(child)

Relax a child sequence expression to admit ambiguity, or require an exact match.
maybe(nucleotideEquals(position := 122, symbol := 'A', sequenceName := 'main'))

nOf(count, {children} [, matchExactly := bool])

Test whether at least count child predicates match, or exactly count when matchExactly is true.

nOf(2, {
nucleotideEquals(position := 241, symbol := 'T', sequenceName := 'main'),
nucleotideEquals(position := 3037, symbol := 'T', sequenceName := 'main'),
nucleotideEquals(position := 23403, symbol := 'G', sequenceName := 'main')
})

nucleotideMutationProfile(distance := n, ..., sequenceName := name)

Test whether a sequence is within a conservative-difference distance of a profile. Define the profile with exactly one of querySequence, sequenceId, or mutations. aminoAcidMutationProfile is the amino-acid form.

nucleotideMutationProfile(
distance := 3,
sequenceName := 'main',
mutations := {
  {position := 241, symbol := 'T'},
  {position := 23403, symbol := 'G'}
}
)