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)
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)
usherTree.phyloDescendantOf('NODE_0000072')Sequence functions
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(...)
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. is written as aminoAcidInsertionContains is the amino-acid form, in which
the stop symbol \*.
insertionContains(position := 22204, value := 'A.*G', sequenceName := 'main')
aminoAcidInsertionContains(position := 214, value := '.*EPE', sequenceName := 'S')maybe(child) / exact(child)
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'}
}
)