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.

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 rows for which the predicate is true.

default.filter(country = 'USA' && age >= 18)

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

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

`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. Apply filters to an input pipeline because a join result cannot be filtered.

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 input schema without reading its rows. Returns fieldName and type.

default.schema()

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})

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')