Getting Started

The basic structure of a query statement is

q = @from <range variable> in <source> begin
    <query statements>
end

Multiple <query statements> are separated by line breaks. Probably the most simple example is a query that filters a DataFrame and returns a subset of its columns:

using Query, DataFrames

df = DataFrame(name=["John", "Sally", "Kirk"], age=[23., 42., 59.], children=[3,5,2])

x = @from i in df begin
    @where i.age>50
    @select {i.name, i.children}
    @collect DataFrame
end

println(x)

# output

1×2 DataFrames.DataFrame
│ Row │ name   │ children │
├─────┼────────┼──────────┤
│ 1   │ "Kirk" │ 2        │

Result types

A query that is not terminated with a @collect statement will return an iterator that can be used to iterate over the individual elements of the result set. A @collect statement on the other hand materializes the results of a query into a specific data structure, e.g. an array or a DataFrame. The Data Sinks section describes all the available formats for query materialization.

Tables

The Query package does not require data sources or sinks to have a table like structure (i.e. rows and columns). When a table like structure is queried, it is treated as a set of NamedTuples, where the set elements correspond to the rows of the source, and the fields of the NamedTuple correspond to the columns. Data sinks that have a table like structure typically require the results of the query to be projected into a NamedTuple. The experimental {} syntax in the Query package provides a simplified way to construct NamedTuples in a @select statement.

Missing values

Missing values are represented as DataValue types from the DataValues.jl package. Here are some usage tips.

All arithmetic operators work automatically with missing values. If any of the arguments to an arithmetic operation is a missing value, the result will also be a missing value.

All comparison operators, like == or < etc. also work with missing values. These operators always return either true or false.

If you want to use a function that does not support missing values out of the box, you can lift that function using the . operator. This lifted function will propagate any missing values, i.e. if any of the arguments to such a lifted function is missing, the result will also be a missing value. For example, to apply the log function on a column that is of type DataValue{Float64}, i.e. a column that can have missing values, one would write log.(i.a), assuming the column is named a. The return type of this call will be DataValue{Float64}.

Piping data through a query

Queries can also be intgrated into data pipelines that are constructed via the |> operator. Such queries are started with the @query macro instead of the @from macro. The main difference between those two macros is that the @query macro does not take an argument for the data source, instead the data source needs to be piped into the query. In practice the syntax for the @query macro looks like this:

using Query, DataFrames

df = DataFrame(name=["John", "Sally", "Kirk"], age=[23., 42., 59.], children=[3,5,2])

x = df |> @query(i, begin
            @where i.age>50
            @select {i.name, i.children}
          end) |> DataFrame

println(x)

# output

1×2 DataFrames.DataFrame
│ Row │ name   │ children │
├─────┼────────┼──────────┤
│ 1   │ "Kirk" │ 2        │

Note how the range variable i is the first argument to the @query macro, and then the second argument is a begin...end block that contains the query operators for the query. Note also that it is recommended to use parenthesis () to call the @query macro, otherwise any continuing pipe operator will not work.