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. MayThis beis provided directly
as a JSON array.
| |
| |
Example as JSON object:
{
"type" : "array",
"array" : [ ... ]
}
Example as JSON array:
[ ... ]
Result Column
Specifies an expression and optional alias. The alias can be referenced in the where clause with an alias expression.
| 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" |