Query Language

sq implements a jq-style query language, formally known as SLQ.

Behind the scenes, all sq queries execute against a SQL database. This is true even for document sources such as CSV or XLSX. For those document sources, sq loads the source data into a scratch database, and executes the query against that database.

Fundamentals #

Let’s take a look at a query.

$ sq '@sakila_pg | .actor | .first_name, .last_name | .[0:3]'
first_name  last_name
PENELOPE    GUINESS
NICK        WAHLBERG
ED          CHASE

You can probably guess what’s going on above. This query has 4 segments:

Behind the scenes, this SLQ query is translated to a SQL query, which is executed against the @sakila_pg source (which in this example is a Postgres database). The SQL generated query will look something like:

SELECT "first_name", "last_name" FROM "actor" LIMIT 3 OFFSET 0

Shorthand #

For a single-table query, you can concatenate the handle and table name. In this example, we list all the rows of the actor table.

# Longhand
$ sq '@sakila_pg | .actor'

# Shorthand
$ sq '@sakila_pg.actor'

If the query only has a single segment and doesn’t contain any shell delimiters or control chars, you can omit the quotes:

$ sq @sakila_pg.actor

If the query is against the active source, then you don’t even need to specify the handle.

$ sq .actor

Column Aliases #

You can use the column alias feature to change the name of one or more columns. For example:

$ sq '.actor | .first_name:given_name, .last_name:family_name'
given_name   family_name
PENELOPE     GUINESS
NICK         WAHLBERG

Behind the scenes, sq uses the SQL column AS alias construct. The query above would be rendered into SQL like this:

SELECT "first_name" AS "given_name", "last_name" AS "family_name" FROM "actor"

This works for any type of column expression, including functions.

$ sq '.actor | count(*):quantity'
quantity
200