Working with Arrays
This page describes a tutorial on how to work with array data in your Kafka topics using Lenses SQL Processors.
In this tutorial, we will see how to use Lenses SQL to extract, manipulate and inspect the single elements of an array.
In Lenses SQL you can use a LATERAL JOIN
to treat the elements of an arrays as a normal field.
You will learn to:
Extract the single elements of an array with a single
LATERAL
join.Extract elements of a multi-level array with nested
LATERAL
joins.Use arbitrary complex array expressions as the right hand side of a
LATERAL
join.
Lateral Join on a simple array
In this example, we are getting data from a sensor.
Unfortunately, the upstream system register the readings in batches, while what we need is a single record for every reading.
An example of such a record is the following:
Notice how each record contains multiple readings, inside the reading
array field.
We can replicate such a structure running the following query in SQL Studio:
We can again use SQL Studio to insert some data to play with:
What we want to obtain is a new topic readings
, where each record contain a single reading, together with its meter_id
. Considering the first record, we expect to explode it to four different records, one for each reading:
In LensesSQL you can easily achieve that with the special LATERAL
syntax .
You can create a processor defined as:
The magic happens in batched_readings LATERAL readings as reading
. With that we are basically saying:
for each record in
batched_readings
for each element inside the
readings
array of that recordbuild a new record with all the fields of the original
batched_readings
record, plus an extrareading
field, that will contain the value of the current element ofreadings
.
We can then use in the SELECT
both the original fields and the new reading
field.
If you save the processor and run it, you will see that it will emit the records we expected.
Filtering
One of the powerful features of a LATERAL
join is that the expression you put in the LATERAL
can be used as a normal field. This means that you can then use it for example also in a WHERE
or in a GROUP BY
.
In this section we will see how to filter records generated by a LATERAL
using a normal WHERE
.
We want to modify our previous processor in order to emit only the readings greater than 95
.
To do that is enough to use reading
as if it were a normal field, in the WHERE
section:
Running the processor we get the records
Lateral Join on a multi-level array
This example is similar to the previous one. The only difference is that the readings are now stored in batches of batches:
As you can see, nested_readings
is an array whose elements are array of integers.
We can again use SQL Studio to insert some data:
We would like to define a processor that emits the same records of the previous one.
In this case though we are dealing with nested_readings
, that is a multi-level array, so a single LATERAL
join is not enough. But nesting a LATERAL
inside another will do the jo
This is roughly what happens in the FROM
clause:
We first unwrap the first level of the array, doing a
batched_readings_nested LATERAL nested_readings as readings
.At that point,
readings
will be an array of integers.We can then use it in the outer
... LATERAL readings as reading
join, and the single integers will finally be extracted and made available asreading
.
Complex array expressions
In this section we will see how it is possible to use any expression as the right hand side of a LATERAL
join, as long as it gets evaluated to an array.
We have a table where the meter readings are split into two columns, readings_day
and readings_night
.
Let’s insert the same data as the first example, but where the readings are split across the two columns.
To extract the readings one by one, we need first to concatenate the two arrays readings_day
and readings_night
. We can achieve that using flatten.
We can then use the concatenated array in a LATERAL
join:
The processor defined above will emit the records
as we expected.
Last updated