The JEQL Language Specification

SQL Extensions

JEQL defines extensions to standard SQL to make various processing patterns more convenient, succinct and/or efficient.

SELECT syntax

`*-EXCEPT` modifier

Starred table expressions (select-all-columns syntax) allows a following EXCEPT (col, col,...) modifier. This allows easily specifiying all columns minus a given set of columns (which is essentially impossible in standard SQL).

Internal `WITH` clause

Allows computing named expressions which can be used in the SELECT list. This provides better modularity (e.g. avoiding repeated subexpressions), better documentation (by using meaningful names for subexpressions) and reduces code bulk (by factoring out common subexpressions).

(Note: This is not the same thing as the "pre-SELECT" WITH clause, which allows defining named subqueries. JEQL does not need this syntax, since the language already allows naming and chaining of select expressions within scripts.)

`LIMIT n` and `OFFSET n`

Allows easily specifying that only a subset of queried rows are to be returned. Supported in some but not all SQL dialects.

TABLE-valued constants

The TABLE ( ( ... ) ( ...) ... ) syntax allows specifying constant values for tabular data in scripts.

Stream-oriented Processing

KEEP() pseudo-function allows referring to values from previous rows in a stream
PREV() allows referring to values from the previous row

Aggregate Functions

FIRST() aggregate function computes the first non-null value in a group. Useful for peforming the crosstab-by-aggregation pattern.

Data Extraction

The SPLIT BY clause allows splitting single rows into multiple rows containing different components of complex objects. There is no convenient, general-purpose way to do this in standard SQL.
Examples include:
- splitting integer ranges into a set of rows with a sequence of integer values
- extracting points, line segments, or components from geometries
- splitting strings into substrings based on patterns or regular expressions
- splitting date ranges into sequences of hours, days, or months.
Component values and their index in the parent object are provided by the SPLITVALUE and SPLITINDEX variables in the select list.