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.

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"]

You cannot use projection aliases in a WHERE clause. The WHERE clause defines what values are delivered to the projection, therefore you cannot have the projection provide info to the WHERE.

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.

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 expression1 AS Alias1, expression2 AS Alias2, ...

Note that top-level functions in the projection must be aggregate functions, however aggregate functions can take simple expressions as arguments. GROUP BY expressions can be any simple expression. Aliasing is optional, but common as the GROUP BY values are usually included in the projection and are referenced by their aliased name.

HAVING allows a query that uses aggregation to have further filtering after the aggregation. HAVING is typically used with a GROUP BY clause. Clauses in simple expressions are converted internally to WHERE clauses to reduce performance impact; using overly restrictive HAVING clauses in aggregate queries can affect performance.

The HAVING clause must always be used at end of a query. For example:

SELECT projection 
FROM source_table
 {WHERE predicate-expr {FOR time-delay MILLISECONDS}}
 {WHEN colname-timestamp {BETWEEN timestamp-expr1 AND timestamp-expr2}}
 {GROUP BY expression1, expression2, ... }
 {ORDER BY colname1 <DESC | ASC> , colname2 <DESC | ASC>, …  
  LIMIT row-limit}
 {HAVING expression}; 

HAVING expression examples:

SELECT category, count(), avg(lastSoldPrice) FROM ItemsSales GROUP BY 
  category HAVING count() > 5

SELECT category, count() FROM ItemsSales GROUP BY category 
  HAVING avg(lastSoldPrice)> 5

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 expression1, expression2, … | 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 expression1, expression2, … | ORDER BY colname1 {asc|desc}, ...} LIMIT N