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

Array selections

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].

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 Lenses SQL 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 Expressions

While nested aliases are a quick way to define 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 to the one used to define 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.

Struct Selections

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.

Special characters in field names

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

SELECT { `a field!`: "hi" }.`a field!` FROM myTopic

Binary Expressions

A binary expression is an expression that is composed of the left-hand side and 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 Lenses SQL 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.