View the latest documentation 5.5
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 1, 2 are integer literals.
1
2
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.
"hello "
'world!'
Boolean constant values can be expressed using the false and true boolean literals:
false
true
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:
NULL
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 [...] syntax:
[...]
["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 Lenses SQL. That will change in future versions.
[]
An element of an array can be extracted appending, to the array expression, a pair of square brackets containing the index of the element.
Some examples:
SELECT myArray[0], myNestedArray[1][1], [1, 2, 3][myIndex], complexExpression[0].inner[1] FROM myTopic
Note how the expression on the left of the brackets can be of arbitrary complexity, like in complexExpression[0].inner[1] or [1, 2, 3][myIndex].
complexExpression[0].inner[1]
[1, 2, 3][myIndex]
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 Lenses SQL there are two ways of building new structs.
In a SELECT projection, it is possible to use nested aliases to denote the fields of a struct.
SELECT
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:
user
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 contact field, containing type and value subfields.
name
contact
type
value
While nested aliases are a quick way for defining new structs, they have some limitations: they can only be used in the projection section of a SELECT, and they do not cover all the cases where a struct can potentially be used.
Struct expressions overcome these limitations.
With struct expressions one can explicitly build complex structs, specifying the name and the values of the fields, one by one, and as any other expression, they can be used inside other expressions and in any other part of the query where an expression is allowed.
The syntax is similar tho the one used to defined JSON objects:
SELECT { name: myName, contact: { type: "email", value: CONCAT(myName, "@lenses.io") } } as user, { name: myName, contacts: [ { type: "email", value: myEmail }, { type: "address", value: myAddress } ] } as userWithContacts FROM myTopic
Note how the first projection
{ name: myName, contact: { type: "email", value: CONCAT(myName, "y") } } as user
is equivalent to the three projections used in the previous paragraph:
myName as user.name, "email" as user.contact.type, CONCAT(myName, "@lenses.io") as user.contact.value
while the second projection userWithContacts is not representable with nested aliases, because it defines structs inside an array.
userWithContacts
A selection is an explicit reference to a field within a struct. The syntax for a selection is:
<expression>.<field_name>
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
It is also possible to select a field from more complex expressions. Here we use selections to select fields from array elements, or to directly access a nested field of a struct expression:
SELECT anArrayWithObjects[1].field, anArrayWithNestedObjects[1].children[2].field, { a: { b: 123 } }.a.b FROM myTopic
In general, a field selection can be used on any expression that returns a struct.
If there are special characters in the field names, backticks (`) can be used:
`
SELECT { `a field!`: "hi" }.`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:
AND
OR
+
-
*
/
%
>
>=
<
<=
=
!=
LIKE
NOT LIKE
IN
NOT IN
A binary expression is the main way to compose expressions into more complex ones.
For example, 1 + field1 and LENGTH(field2) > 5 are binary expressions, using the + and the >= operator respectively.
1 + field1
LENGTH(field2) > 5
CASE expressions return conditional values, depending on the evaluation of sub-expressions present in each of the CASE’s branches. This expression is Lenses SQL version of what other languages call a switch-statement or if-elseif-else construct.
CASE
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.
Lenses SQL 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.
On this page