Hiperspace SQL is a full structured query language interpreter for querying a hIperspace. It supports the full range of query operations, including joins and aggregates.

Queries can select from any set within the domain subspace, including views, segments and aspects.

The result from a query can retrieved as a collection or a an Parquet column oriented dataset

Query

All SQL queries start with a SELECT followed by a comma-list of columns and one or more FROM clauses. WHERE filters, GROUP BY and ORDER BY are optional. The SELECT keyword is parsed without case, and can be SELECT, select, Select or even SeLeCt.

Join

The FROM clause can be a single table or a number of joins. It is best practice to include an alias when selecting from a table set to avoid ambiguity inm the rest of the script

A Join is either

A reference to a set within the subspace
A JOIN to another table set or query
An inline-view/sub-query (e.g. 'SELECT c.* FROM customers AS c JOIN (SELECT a.customer_id FROM accounts WHERE balance > 1000) AS s ON c.customer_id = s.customer_id;')

columns

The list of columns consist of a comma serperated list of expressions with an optional (but recomended) AS alias. Lists of columns are used in the SELECT, GROUP BY and ORDER BY clauses.

expression

Expressions are either:

A reference to a property in a Set. A reference in Hiperspace.SQL can be any property Person.Name or a propperty of a property Person.Father.Name, Person.Father.Mother.Name. References do not need to be base {number, text, date} values, but can be nested values and collections
A literal value { 'text', number, date, TRUE, FALSE}
a parameter (e.g Person.Name = :param)
An arithmetic expression to multiply, divide, add, subtract another expression
a boolean expression that evaluates to TRUE or FALSE
An aggregate function of {*MAX, MIN, AVG, COUNT}

boolean

Boolean expressions are used in WHERE and HAVING predicates, and optionally as a column in s SELECT column

Boolean Expressions are either:

A range expression (*e.g. 'Account.Balance BETWEEN 100 AND 1000')
The oposite of another boolean expression
A unary expression of greather-than, less-than, equality, inequality
A relational exression of IN, NOT IN, EXISTS, NOT EXISTS with an sub-query
A NULL checking operations (e..g. 'Customer.DateOfBirth IS NOT NULL'