# 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 href="#literals" id="literals"></a>

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**&#x20;

Integer numbers can be introduced in a Lenses SQL query using integer literals:

```sql
SELECT 1 + 2 FROM myTopic
```

In the above query `1`, `2` are integer literals.

**Decimals**&#x20;

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

```sql
SELECT 3.14 as pi FROM myTopic
```

**Strings**&#x20;

To express strings, string literals can be used. Single quotes (`'`) and double quotes (`"`) are both supported as delimiters:

```sql
SELECT CONCAT("hello ", 'world!') FROM myTopic
```

In the example above, `"hello "` and `'world!'` are string literals.

**Booleans**&#x20;

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

```sql
SELECT false, true FROM myTopic
```

**Nulls**&#x20;

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:

```sql
INSERT INTO cleanedTopic
SELECT NULL as _value FROM myTopic WHERE myField IS NULL
```

#### Arrays  <a href="#arrays" id="arrays"></a>

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

**Array expressions**&#x20;

A new array can be defined with the familiar `[...]` syntax:

```sql
["a", "b", "c"", "d"]
```

You can use more complex expressions inside the array:

```sql
[1 + 1, 7 * 2, COS(myfield)]
```

and nested arrays as well:

```properties
[["a"], ["b", "c"]]
```

*Note*: empty array literals  `[]` are currently not supported by Lenses SQL. That will change in future versions.

**Array selections**&#x20;

An element of an array can be extracted appending, to the array expression, a pair of square brackets containing the element's index.

Some examples:

```sql
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 href="#structs" id="structs"></a>

A Struct is a value composed of 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**&#x20;

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:

```sql
SELECT
    myName as user.name,
    "email" as user.contact.type,
    CONCAT(myName, "@lenses.io") as user.contact.value
FROM myTopic
```

When the projection is 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`  `value` subfields.

**Struct Expressions**&#x20;

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`They do not cover all 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 define JSON objects:

```sql
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

```sql
{
    name: myName,
    contact: { type: "email", value: CONCAT(myName, "y") }
} as user
```

is equivalent to the three projections used in the previous paragraph:

```sql
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**&#x20;

A selection is an explicit reference to a field within a struct. The syntax for a selection is:

```properties
<expression>.<field_name>
```

Selections can be used to access a field of a facet directly, optionally specifying the topic and the facet:

```sql
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 access a nested field of a struct expression directly:

```sql
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**&#x20;

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

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

#### Binary Expressions  <a href="#binary-expressions" id="binary-expressions"></a>

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  <a href="#case-statements" id="case-statements"></a>

`CASE` expressions return conditional values depending on the evaluation of sub-expressions present in each of the `CASE`’s branches. This expression is a Lenses SQL version of what other languages call a switch-statement, or if-else if-else construct.

```sql
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 href="#functions" id="functions"></a>

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