LiveQL Reference

A single asterisk (*). This returns all columns from the specified table. The single asterisk cannot be used in conjunction with any other projections.

A comma-separated list of exact column names from a LiveView table. The column name can be aliased with an AS clause.

A comma-separated list of simple (arbitrarily complex but no aggregate functions) StreamBase expressions, and optionally, an alias. These are also called calculated columns.

A LiveView aggregation expression. The aggregation expression consists of an aggregate function, the arguments for the aggregate function, and, optionally, an alias. Aggregate expressions may be aliased with an AS clause. The arguments of LiveView aggregate functions can be an exact column name or a simple StreamBase expression that resolves to the data type required by the aggregate function.

You can either use ORDER BY or GROUP BY in a single LiveQL statement, but not both.

Use the ORDER BY keyword to sort query results in ascending or descending order. Use the LIMIT keyword to define the maximum query result size. These query modifiers have the following syntax:

SELECT projection FROM Table
WHERE predicate-expr 
{WHEN colname-timestamp {BETWEEN timestamp-expr1 AND timestamp-expr2}}
ORDER BY colname1 {asc|desc} {, 
  colname {asc|desc}, ...} LIMIT N

LIMIT has a special meaning in ORDER BY queries. In ORDER BY queries, the limit defines the maximum size of the result set. The result set will grow to size LIMIT and then the correctly ordered rows will be added/removed or updated from the result set regardless of how many total rows meet the predicate.

Use the IN keyword to specify multiple values in a WHERE clause, using the following syntax. The square brackets are a required part of the syntax. Specify literals to match the data type of the specified column (or expression that resolves to a column name).

SELECT projection FROM Table 
  WHERE colname IN [literal_of_colname_datatype, 
  literal_of_colname_datatype, … ]

For example:

WHERE quantityRemaining IN [2,3,4]

Or:

WHERE category IN [“toy”,“book”]

For non ORDER BY queries, LIMIT acts as a mechanism for clients to optionally use to protect themselves from unexpectedly large result sets. If the result set grows to LIMIT size, the query is closed and the client gets an exception.

GROUP BY clauses let you collect data from multiple rows and group the results by query projection. A GROUP BY entry is required for any raw column name in the query projection. Queries with GROUP BY clauses have the following syntax:

SELECT projection FROM Table 
  WHERE predicate-expr 
  {WHEN colname-timestamp {BETWEEN timestamp-expr1 AND timestamp-expr2}}
  GROUP BY colname1, colname2, ...

Note that top-level functions must be aggregate functions, however aggregate functions can take simple expressions as arguments. GROUP BY column names can and usually are included in the projection.

Time-window modifiers let you limit results to a specific time window. Time windows have the following syntax:

SELECT projection FROM Table 
  {WHERE predicate-expr}
  WHEN colname-timestamp BETWEEN timestamp-expr1 
  AND timestamp-expr2
  {GROUP BY colname1, colname2 | ORDER BY colname1 {asc|desc}, ...} LIMIT N

Time-delay modifiers let you limit results to conditions that are true for a specified amount of time.

SELECT projection FROM Table 
  WHERE predicate-expr FOR number MILLISECONDS
  {GROUP BY colname1, colname2 | ORDER BY colname1 {asc|desc}, ...} LIMIT N