sq implements a
jq-style query language, formally
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
sq loads the source data into a scratch database,
and executes the query against that database.
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:
|Handle||Table Selector(s)||Column Expression(s)||Row Range|
Behind the scenes, this SLQ query is translated to a SQL query, which is executed
@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
For a single-table query, you can concatenate the handle and table name.
In this example, we list all the rows of the
# 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
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