4.3

Expressions

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.

Literals 

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.

Integers 

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.

Decimals 

Decimal number literals can be used to express constant floating-point numbers:

SELECT 3.14 as pi FROM myTopic

Strings 

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.

Booleans 

Boolean constant values can be expressed using the false and true boolean literals:

SELECT false, true FROM myTopic

Nulls 

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

Arrays 

An array is a collection of elements of the same type.

Array expressions 

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 LSQL. That will change in future versions.

Array selections 

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.

Example:

SELECT
  myArray[0],
  myArray[0 + 1]
FROM myTopic

More complex selections, like myNestedArray[0][1] 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

Structs 

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.

Nested aliases 

In a 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 contact field, containing type and value subfields.

Struct Selections 

A selection is an explicit reference to a field within a struct.

The syntax for a selection is as follows:

[<named_source>.][<facet>.]<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

Special characters in field names 

If there are special characters in the field names, backticks (`) can be used:

SELECT myStruct.`a field!` FROM myTopic

Binary Expressions 

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: AND, OR
  • Arithmetic operators: +, -, *, /, % (mod)
  • Ordering operators: >, >=, <, <=
  • Equality operators: =, !=
  • String operators: LIKE, NOT LIKE
  • Inclusion operators: 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.

Case statements 

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 if-elseif-else construct.

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

Functions 

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.