Expressions are the parts of a Lenses SQL query that will be evaluated to single values.
Below is the complete list of expressions that Lenses SQL supports.
A literal is an expression that represents a concrete value of a given type. This means that there is no resolution needed for evaluating a literal and its value is simply what is specified in the query.
Integer numbers can be introduced in a Lenses SQL query using integer literals:
SELECT 1 + 2 FROM myTopic
In the above query
2 are integer literals.
Decimal number literals can be used to express constant floating-point numbers:
SELECT 3.14 as pi FROM myTopic
To express strings, string literals can be used. Single quotes (
') and double quotes (
") are both supported as delimiters:
SELECT CONCAT("hello ", 'world!') FROM myTopic
In the example above,
"hello " and
'world!' are string literals.
Boolean constant values can be expressed using the
true boolean literals:
SELECT false, true FROM myTopic
Sometimes it is necessary to the
NULL literal in a query, for example to test that something is or is not null, or to put a
NULL the value facet,
useful to delete records in a compacted topic:
INSERT INTO cleanedTopic SELECT NULL as _value FROM myTopic WHERE myField IS NULL
An array is a collection of elements of the same type.
A new array can be defined with the familiar
["a", "b", "c"", "d"]
You can use more complex expressions inside the array:
[1 + 1, 7 * 2, COS(myfield)]
and nested arrays as well:
[["a"], ["b", "c"]]
Note: empty array literals like
 are currently not supported by LSQL. That will change in future versions.
An element of a field containing an array can be extracted appending, to the array expression, a pair of square brackets containing the index of the element.
SELECT myArray, myArray[0 + 1] FROM myTopic
More complex selections, like
myNestedArray are currently not supported. That will be fixed in future versions.
As a temporary workaround, the element_of function can be used:
SELECT element_of(element_of(myNestedArray, 0), 1) FROM myTopic
A Struct is a value that is composed by fields and sub-values assigned to those fields. It is similar to what an object is in JSON.
In LSQL there are two ways of building new structs.
SELECT projection, it is possible to use nested aliases to denote the fields of a struct.
In the next example, we are building a struct field called
user, with two subfields, one that is a string,
and another one that is a struct:
SELECT myName as user.name, "email" as user.contact.type, CONCAT(myName, "@lenses.io") as user.contact.value FROM myTopic
When the projection will be evaluated, a new struct
user will be built.
The result will be a struct with a
name field, and a nested struct assigned to the
A selection is an explicit reference to a field within a struct.
The syntax for a selection is as follows:
Selections can be used to directly access a field of a facet, optionally specifying the topic and the facet:
SELECT myField, -- value facet field, with implicit topic and facet myTopic.myField, -- value facet field, with explicit topic and implicit facet _value.myField, -- value facet field, with implicit topic and explicit facet myTopic._value.myField, -- value facet field, with explicit topic and facet _key.myKeyField, -- key facet field, with implicit topic and explicit facet myTopic._key.myKeyField -- key facet field, with explicit topic and facet FROM myTopic
If there are special characters in the field names, backticks (
`) can be used:
SELECT myStruct.`a field!` FROM myTopic
A binary expression is an expression that is composed of a left-hand side and a right-hand side sub-expressions and an operator that describes how the results of the sub-expressions are to be combined into a single result.
Currently, supported operators are:
- Logical operators:
- Arithmetic operators:
- Ordering operators:
- Equality operators:
- String operators:
- Inclusion operators:
A binary expression is the main way to compose expressions into more complex ones.
1 + field1 and
LENGTH(field2) > 5 are binary expressions, using the
+ and the
>= operator respectively.
CASE expressions return conditional values, depending on the evaluation of sub-expressions present in each of the
CASE’s branches. This expression is LSQL version of what other languages call a switch-statement or
SELECT CASE WHEN field3 = "Robert" THEN "It's bobby" WHEN field3 = "William" THEN "It's willy" ELSE "Unknown" END AS who_is_it FROM myTopic
A function is a predefined named operation that takes a number of input arguments and is evaluated into a result. Functions usually accept the result of other expressions as input arguments, so functions can be nested.
LSQL Streaming supports out-of-the-box a great number of different functions , and this set can be further expanded when User Defined Functions and User Defined Aggregated Functions ) are used.