Select Syntax

The SELECT syntax is essentially a JSON representation of the MySQL SELECT syntax. See the MySQL documentation for more detailed information.

SELECT

The SELECT syntax is contained in a single JSON object.

Property	Value	Notes
distinct	boolean, default false	If true, only returns unique values
columns	result columns	If empty, returns all columns available from source
from	source	Source being selected from
where	expression	Condition for rows, where expression returns true
group	array of expressions	Used to group rows for aggregation functions
having	expression	Like where, but can filter aggregation results
order	array of order terms	Used to sort the results
limit	expression	Limit the number of rows returned
offset	expression	Offset of the start of the rows

Result Columns

Specifies the column(s) to select.

All

Specifies all columns from the source. This is the same as the MySQL SELECT * syntax. This is the default if no value for the columns property is set.

Property	Value
type	"all"

Example

{ "type": "all" }

Array

Specifies column(s) as an array of result column objects. May be provided directly as a JSON array.

Property	Value
type	"array"
array	array of result column(s)

Example as JSON object:

{
 "type"  : "array",
 "array" : [ ... ]
}

Example as JSON array:

[ ... ]

Result Column

Specifies an expression and optional alias.

Property	Value
e	expression
alias	string (optional)

Source

A source is a SQL table (or virtual table) from which a SELECT statement loads data.

Table Source

A source from any table.

Property	Value
type	"table"
table	string table
alias	string (optional)

The table syntax is the same as the table portion of the column expression syntax

table           = system-table-name | database-table
database-table  = database-path ['@' database-table-name]

May also be provided directly as a JSON string (without the alias property).

System Table Source

Deprecated

A source from a system table.

Property	Value
type	"table_system" or "ts"
table	string table name
alias	string (optional)

---

Database Table Source

A source from a database table.

Property	Value
type	"table_database" or "td"
database	database specifier
table	string table name
alias	string (optional)

---

Join Source

A source derived from a SQL join of two sources.

Property	Value
type	"join"
op	"join", "left", "left_outer", "inner", or "cross"
s1	left join source
s2	right join source

---

Select Source

Source from the result of a select statement.

Property	Value
type	"select"
select	select

Order Term

Specifies an expression and optional order.

Property	Value
e	expression
order	"asc" or "desc" (optional, default "asc"