Query Language
On this page
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:
Handle | Table Selector(s) | Column Expression(s) | Row Range |
---|---|---|---|
@sakila_pg | .actor | .first_name, .last_name | .[0:3] |
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