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

data
  .filter(country = 'Switzerland')
  .group(by := {pangoLineage}, aggs := {count := count()})

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 data. Further tables are queried like any other table:

reference_genomes is a built-in table that every database contains. It 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. data.filter(isHuman) means isHuman = true, and data.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(condition)`

Keep the rows of the preceding result for which the condition is true. A filter can follow any operation, such as a group, 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 order or unionall, supports every predicate. On other results, a predicate can combine comparisons, boolean columns, and isNull() with && and ||.

data.filter(country = 'USA' && age >= 18)
data.group(by := {country}, aggs := {count := count()}).filter(count > 1000)

`group(by, aggs)`

Aggregate rows. by is the set of grouping columns, and the empty set {} aggregates all rows into a single group. aggs is a record of named aggregates. The result has one row per group with the grouping columns and the aggregates. Rows with a null grouping value form their own group.

Aggregate	Result
`count()`	The number of rows in the group, as an int64.
`sum(column)`	The sum of a numeric column. Integer columns sum to an int64 and float columns to a float. Null values are skipped; a group without any non-null value sums to null.

data.group(by := {}, aggs := {count := count()})
data.group(by := {country, pangoLineage}, aggs := {count := count()})
data.group(by := {country}, aggs := {count := count(), totalAge := sum(age)})

`project(expressions)`

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

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

`projectout(remove)`

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.

data.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.

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

`order(by)`

Sort by a set of keys, each a bare column name (ascending) or an asc/desc expression.

data.order(by := {date.desc(), primaryKey})

`limit(count)`

Return at most count rows.

data.limit(100)

`offset(count)`

Skip count rows. Use a deterministic order when paginating.

data.order(by := {primaryKey}).offset(100).limit(100)

`randomize([seed := n])`

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

data.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

data.group(by := {pangoLineage}, aggs := {countWorld := count()})
.join(
  data
    .filter(country = 'Spain')
    .group(by := {pangoLineage}, aggs := {countSpain := count()})
    .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.

data.filter(country = 'Germany').project({country})
.unionall(data.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.

data.schema()
data.group(by := {country}, aggs := {count := count()}).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.

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

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

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

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

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

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

data.insertions(sequenceNames := {main})

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

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

data.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 written as column names, 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(data.map({lineageName := pangoLineage}).project({lineageName}), to = lineageName)
.group(by := {from}, aggs := {count := count()})

Phylogenetic operations

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

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

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

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

Return a Newick subtree spanning the filtered records.

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

Write statements

`insertInto(query, table)`

Run a query and insert its rows into an existing table, given by its name. 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)

`createTable(table, columns [, primaryKey := column])`

Create a new, empty table. table is a name made of letters, digits, _ and - that no existing table uses, built-in tables included, and is neither database_schema nor data_version. columns is a record that maps each column name to its type. A type is written as a bare name, such as int, or with named options, such as string(generateIndex := true). Column names that are not plain identifiers are written as double-quoted identifiers, such as “S:ORF1a”. The optional primaryKey names a string column without an index.

Type	Options	Column
`string`	`generateIndex := bool`	String; dictionary-encoded with a bitmap index when indexed
`int` / `int32`, `int64`		32-bit or 64-bit integer
`float`		Floating-point number
`boolean`		Boolean
`date`		Date
`nucleotideSequence`	`reference := name` (required)	Aligned nucleotide sequence
`aminoAcidSequence`	`reference := name` (required)	Aligned amino-acid sequence
`zstdCompressedString`	`dictionary := query` (required)	String compressed with a zstd dictionary, such as an unaligned sequence

A sequence column takes its reference from the row of reference_genomes with the given name and a matching type. The dictionary query of a zstdCompressedString column must return exactly one row with one string column, usually a reference sequence. References and dictionaries are copied into the new table when it is created. Lineage indexes and phylogenetic-tree columns cannot be declared with createTable.

Like insertInto, createTable must be the whole query and is sent to the

POST /admin/query

endpoint. It returns a single createdTable row instead of table rows.

createTable(covid, {
primaryKey := string,
country := string(generateIndex := true),
age := int,
date := date,
main := nucleotideSequence(reference := main),
"S" := aminoAcidSequence(reference := "S"),
unaligned_main := zstdCompressedString(
  dictionary := reference_genomes.filter(name = 'main' && type = 'nucleotide').project({sequence})
)
}, primaryKey := primaryKey)