Lenses.io Help Center
Latest 5.0 4.3 4.2 4.1 4.0 3.2 3.1 3.0 2.3 2.2 2.1 2.0 1.1 1.0
Latest 5.0 4.3 4.2 4.1 4.0 3.2 3.1 3.0 2.3 2.2 2.1 2.0 1.1 1.0

4.3

You are viewing documentation for an older version of Lenses.io View latest documentation here

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.

Contents