# Select Syntax

The SELECT syntax is essentially a JSON representation of the MySQL `SELECT` syntax. See the
[MySQL documentation](https://dev.mysql.com/doc/refman/8.0/en/select.html) 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](#bkmrk-result-columns)    | If empty, returns all columns available from source
| from | [source](#bkmrk-source)                          | Source being selected from
| where | [expression](expression-syntax) | Condition for rows, where expression returns `true`
| group | `array` of [expressions](expression-syntax) | Used to group rows for aggregation functions
| having | [expression](expression-syntax) | Like `where`, but can filter aggregation results
| order | `array` of [order terms](#order-term)| Used to sort the results
| limit | [expression](expression-syntax)             | Limit the number of rows returned                        |
| offset | [expression](expression-syntax)             | 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_

```json
{ "type": "all" }
```

### Array

Specifies column(s) as an array of [result column](#bkmrk-result-column) objects. This is provided directly
as a JSON array.

_Example as JSON array:_

```json
[ ... ]
```

## 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](expression-syntax)      |
| `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](expression-syntax#columns)

```text
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](api-syntax-spec.md#database) |
| `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](#source)                                 |
| `s2`       | right join [source](#source)                                |

---

### Select Source

Source from the result of a select statement.

| Property   | Value                                                       |
|------------|-------------------------------------------------------------|
| `type`     | `"select"`                                                  |
| `select`   | [select](api-syntax-sel.md)                                 |

--- 

## Order Term

Specifies an expression and optional order.

| Property   | Value                                                       |
|------------|-------------------------------------------------------------|
| `e`        | [expression](expression-syntax)                              |
| `order`    | `"asc"` or `"desc"` (optional, default `"asc"`              |