Query-language reference

A RhyDB query is a pipeline of operations that starts from a table name and produces a table of result rows.

Query structure

default
  .filter(country = 'Switzerland')
  .groupBy({count := count()}, {pangoLineage})

Method syntax is equivalent to passing the value on the left as the first function argument. Named arguments use :=. After a named argument, all remaining arguments must also be named.

Tables

The table holding the sequences and their metadata is named default. Preprocessing adds further tables that are queried like any other table:

reference_genomes holds the reference sequences, one row per sequence, with the string columns name, type (nucleotide or amino_acid) and sequence.
A lineage column configured with lineageIndexType: table or both gets a lineage relation table named after the column. It holds one row per direct edge of the lineage tree, with the child in lineage and its direct parent in parent (null for roots), as well as id, is_recombinant_edge and recombinant_clade_ancestor.

tables() lists the tables of an instance.

Literals

Type	Syntax	Example
String	single-quoted	`‘Switzerland’`
Integer	bare number	`42`
Float	decimal	`3.14`
Boolean	`true` or `false`	`true`
Null	`null`	`null`
Date	`‘YYYY-MM-DD’::date`	`‘2024-05-15’::date`
Set	`{value, …}`	`{‘A’, ‘B’}`
Record	`{name := value, …}`	`{label := ‘A’, n := 3}`

Operators

Boolean expressions use && (and), || (or), and ! (not). Parentheses control grouping.

A comparison takes a column identifier on one side and a literal on the other. The column may be on either side, so age > 30 and 30 < age are the same filter.

Operator	Meaning	Column types
`=`	equals	all
`<>`	not equals	all
`<`, `<=`, `>`, `>=`	ordering	int, float, date, and string (lexicographic)

country = 'Germany' && age >= 18
!(date < '2024-01-01'::date)

A boolean column can serve as a predicate on its own. default.filter(isHuman) means isHuman = true, and default.filter(!isHuman) is its complement. Only boolean columns may be written this way; a bare reference to a column of another type is rejected.

●

Null values and comparisons

A null cell never matches a comparison, <> included, so country <> 'Germany' leaves out the rows whose country is null. ! is a set complement rather than SQL’s NOT, so !(country = 'Germany') does return those rows. Comparing against the null literal is an error; use

isNull() or isNotNull()

to test for missing values.

Pipeline operations

`filter(predicate)`

Keep the rows of the preceding result for which the predicate is true. A filter can follow any operation, such as a groupBy, a map that derives the filtered column, a join, or schema().

A filter that applies to the rows of a table, directly or through project, a deterministic orderBy or unionAll, supports every predicate. On other results, a predicate can combine comparisons, boolean columns, and isNull() with && and ||.

default.filter(country = 'USA' && age >= 18)
default.groupBy({count := count()}, {country}).filter(count > 1000)

`groupBy(aggregates [, columns])`

Aggregate rows. The first argument is a record of aggregates; the optional second argument is a set of grouping columns. Currently, count() is the supported aggregate.

default.groupBy({count := count()}, {country, pangoLineage})

`project(fields)`

Return only the columns in the given set. At least one column must be kept.

default.project({primaryKey, country, date})

`projectout(fields)`

Return all columns except the ones in the given set, in their original order. Every named column must exist, and at least one column must remain.

default.projectout({main, unaligned_main})

`map(expressions)`

Add or replace columns using name-and-value assignments. Values may be literals, columns, or non-boolean scalar functions.

default.map({cohort := 'A', copiedCountry := country, week := date.isoWeek()})

`orderBy(fields)`

Sort by bare ascending fields or asc/desc expressions.

default.orderBy({date.desc(), primaryKey})

`limit(count)`

Return at most count rows.

default.limit(100)

`offset(count)`

Skip count rows. Use a deterministic order when paginating.

default.orderBy({primaryKey}).offset(100).limit(100)

`randomize([seed := n])`

Return rows in random order. A seed makes the order reproducible.

default.randomize(seed := 42).limit(10)

`join(left, right, on [, type := kind])`

Combine two pipelines by equality between columns. Multiple equalities may be joined with &&. The default type is inner. The two inputs must use disjoint column names. A filter after a join applies to the joined rows; apply sequence predicates such as hasMutation() to an input pipeline instead.

Type	Kept rows	Output columns
`inner`	Matching pairs only (default)	Left and right
`left`	All left rows; right columns are null-filled when unmatched	Left and right
`right`	All right rows; left columns are null-filled when unmatched	Left and right
`full`	All rows from both sides; the unmatched side is null-filled	Left and right
`leftSemi`	Left rows with a match	Left only
`rightSemi`	Right rows with a match	Right only
`leftAnti`	Left rows without a match	Left only
`rightAnti`	Right rows without a match	Right only

default.groupBy({countWorld := count()}, {pangoLineage})
.join(
  default
    .filter(country = 'Spain')
    .groupBy({countSpain := count()}, {pangoLineage})
    .map({pangoLineage2 := pangoLineage})
    .project({pangoLineage2, countSpain}),
  pangoLineage = pangoLineage2,
  type := left
)

`unionAll(left, right)`

Concatenate two results with identical column names, types, and order. Duplicate rows are retained.

default.filter(country = 'Germany').project({country})
.unionAll(default.filter(country = 'France').project({country}))

`schema()`

Describe the schema of a table or of any pipeline result without reading its rows. Returns fieldName and type.

default.schema()
default.groupBy({count := count()}, {country}).schema()

`tables()`

List the tables of the instance as rows with a single tableName column. It takes no arguments and starts a pipeline instead of following a table.

tables()

Sequence aggregations

●

Note

These operations aggregate changes across the input rows. A preceding filter chooses the records to analyze; it does not restrict which changes the aggregation returns.

`mutations(minProportion := p [, sequenceNames := {...}] [, fields := {...}])`

Aggregate nucleotide substitutions and deletions above a frequency threshold. Returns source and observed symbols, position, sequence name, proportion, coverage, and count.

default.filter(country = 'Switzerland')
.mutations(minProportion := 0.05, sequenceNames := {main})

`aminoAcidMutations(minProportion := p [, ...])`

Aggregate amino-acid substitutions and deletions above a frequency threshold.

default.aminoAcidMutations(minProportion := 0.1, sequenceNames := {S})

`insertions([sequenceNames := {...}])`

Aggregate every nucleotide insertion in the input rows by sequence, position, and inserted symbols.

default.insertions(sequenceNames := {main})

`aminoAcidInsertions([sequenceNames := {...}])`

Aggregate every amino-acid insertion in the input rows by sequence, position, and inserted symbols.

default.aminoAcidInsertions(sequenceNames := {S})

Graph operations

`transitiveClosure(input, from, to [, includeVertices := bool] [, startingFrom := {...}])`

Treat each input row as an edge from its from column to its to column, both of type string, and return a from and to row for every pair connected by one or more edges. Rows with a null vertex are ignored. includeVertices := true adds the pair (v, v) for every vertex. startingFrom, a set of string literals, restricts the result to paths that start at the given vertices. The row order is unspecified.

On a lineage relation table, which lists each lineage with its parent, the closure from parent to lineage pairs every lineage with each of its descendants. The example counts the sequences of a lineage and all its sublineages.

pangoLineage
.transitiveClosure('parent', 'lineage', includeVertices := true, startingFrom := {'B.1.1.7'})
.join(default.map({lineageName := pangoLineage}).project({lineageName}), to = lineageName)
.groupBy({count := count()}, {from})

Phylogenetic operations

`mostRecentCommonAncestor(column [, printNodesNotInTree := bool])`

Find the most recent common ancestor of the filtered records in a configured phylogenetic-tree column.

default.filter(country = 'Germany').mostRecentCommonAncestor('usherTree')

`phyloSubtree(column [, printNodesNotInTree := bool] [, contractUnaryNodes := bool])`

Return a Newick subtree spanning the filtered records.

default.filter(country = 'Germany').phyloSubtree('usherTree')

Write statements

`insertInto(query, table)`

Run a query and insert its rows into an existing table, given as a bare name or a string literal. Output columns are matched to the target columns by name: every target column must be produced with a matching type, and additional columns are ignored. Sequence columns cannot be inserted.

insertInto must be the outermost operation of a query. It is sent to the

POST /admin/query

endpoint, which saves the result as a new data version.

source.filter(country = 'Switzerland').project({primaryKey, country}).insertInto(archive)