Arrays

Select Entire Array

Enter a specific array name instead of * with select command to query the entire array by it's name. In case you query a not existing array, you get NULL in the response.

Template: select array_name from stream_name

Selecting arrays by the name

-- returns all Entires arrays from binance stream
SELECT entries FROM binance

Select Array Elements

Use dot separator to specify a specific array element.

Template: select array_name.field_name from stream_name

Picking elements from arrays

-- select all Entries arrays
SELECT entries FROM bitfinex

-- return an array of Price fields from all Entries arrays
SELECT entries.price FROM bitfinex 

-- check all array Price elements against the specified condition and return an array of boolean elements
SELECT entries.price > 10 FROM bitfinex 

-- checks array values and returns a single true/false per array
SELECT ANY(entries.price > 10) FROM bitfinex 

-- use alias name as object reference
SELECT
(entries[THIS IS deltix.timebase.api.messages.universal.L1Entry] AS trades).price AS 'Price',
trades.size AS 'Size'
FROM kraken
WHERE size(trades) > 0

-- returns arrays of Price values that match the condition
SELECT entries.price AS 'price' FROM bitfinex
WHERE ANY(entries.price > 10)

-- selecting Bid and Ask prices and sizes from Entries array in the L1entry object
WITH
entries AS array(deltix.timebase.api.messages.universal.L1entry) AS l1
SELECT
l1[side == ASK].price AS 'askPrice',
l1[side == ASK].size AS 'askSize',
l1[side == BID].price AS 'bidPrice',
l1[side == BID].size AS 'bidSize'
FROM binance
WHERE size(l1) > 0

-- select all Prices from Entries array in the L1Entry object that are both > 0 and > 20
SELECT entries[THIS IS deltix.timebase.api.messages.universal.L1Entry].price AS 'Price' FROM bitfinex
WHERE size(entries[THIS IS deltix.timebase.api.messages.universal.L1Entry]) > 0 AND ALL(entries[THIS IS deltix.timebase.api.messages.universal.L1Entry].price > 20)

Select Embedded Arrays

QQL allows selecting arrays within arrays. Such queries return arrays with elements from embedded arrays.

Template: select array_name.embedded_array_name.field_name from stream_name

Selecting embedded arrays

-- returns all Attributes arrays that are embedded in the Entries array
SELECT entries.attributes FROM packages

-- returns an array with all Value fields from all Attributes arrays that are embedded in the Entries array
SELECT entries.attributes.value FROM packages

Filter Arrays

Filter by Type

Use keyword THIS to filter arrays by type.

Filter arrays using THIS keyword

-- select Entries arrays from L1Entry object type that meet the condition
SELECT entries[THIS IS deltix.timebase.api.messages.universal.L1Entry] AS 'entry' FROM bitfinex
WHERE size(entries[THIS IS deltix.timebase.api.messages.universal.L1Entry]) > 0

-- select specific fields from Entries array within the L1Entry object
SELECT entries[THIS IS deltix.timebase.api.messages.universal.L1Entry].price AS 'Price', entries[THIS IS deltix.timebase.api.messages.universal.L1Entry].size AS 'Size' FROM bitfinex
WHERE size(entries[THIS IS deltix.timebase.api.messages.universal.L1Entry]) > 0

Nullability Check

Filtering out NULL values

SELECT (entries AS array(TradeEntry))[THIS IS NOT NULL] FROM binance

Predicates

You can use THIS variable to iterate through array elements, filter objects by type, filter object fields against a specified condition.

Using THIS to iterate through array elements

-- returns just Entries array elements of a Trade type. In this case we check all Entries array elements one by one and select just the ones of a Trade type.
SELECT entries[THIS IS TradeEntry] FROM binance

-- In this case we check all Size values in all Entries array elements and return only those that meet the query condition (>10). Query returns an arrays of Size values that are greater than 10.
SELECT entries.size[THIS > 10] FROM binance
SELECT entries[THIS.size > 10].size FROM binance
SELECT entries[size > 10].size FROM binance

-- returns all Entries array elements where Price field value is greater than 2000
SELECT entries[THIS.price > 2000] FROM binance

-- same as previous - THIS variable can be skipped in this case
SELECT entries[price > 2000] FROM binance

-- take Entry objects where Price is greater than 4000 and select Price fields that are less than 5000
SELECT entry[price > 4000].price[THIS < 5000] FROM binance
ARRAY JOIN entries AS entry

-- same as previous
SELECT entry[price > 4000 and price < 5000].price FROM binance
ARRAY JOIN entries AS entry

-- same as previous
SELECT entry[price > 4000][price < 5000].price FROM binance
ARRAY JOIN entries AS entry

-- same as previous
SELECT entry.price[this > 4000 and this < 5000] FROM binance
ARRAY JOIN entries AS entry

Select by Element Index

You can select array elements by their indices.

Template: select array_name[array_element_index] from stream_name

Selecting by the index

-- select Entries array with index = 10
SELECT entries[10] FROM binance

-- select symbol and max Price with index = 0 from Entries array from binance stream and aggregate all by symbol
SELECT symbol, max{}(entries.price[0]) FROM binance
GROUP BY symbol

Selecting array records with negative indices.

Template: select array_name[array_element_index] from stream_name

Selecting negative indices

-- returns records in the reversed order. This query returns the first array record from the tail.
SELECT entries[-1] FROM binance

Selecting an array of records by their indices.

Template: select array_name[[index_array]] from stream_name

Selecting array elements by their indices

-- returns an array of Entries records according to their indices
SELECT entries[[2, 3]] FROM binance

Selecting array elements using an array of Boolean values.

Each Boolean element corresponds to the array element index.

Template: select array_name[[boolean_values_array]] from stream_name

-- returns array elements with indices 0 and 2
SELECT entries[[True, False, True]] FROM binance

-- returns all records with price greater than 2000. [entries.price > 2000] returns an array of boolean values.
-- See Comparison Operations further in this manual.
SELECT entries[entries.price > 2000] FROM binance

Slicing Queries

Slicing queries use index ranges to query array elements.

Template: select array_name[index_range] from stream_name

-- select all Entries array elements between indices 1 and 2
SELECT entries[1:2] FROM binance

-- select all Entries array elements with index less than 2
SELECT entries[:2] FROM binance

-- select all Entries array elements with index greater than 2
SELECT entries[2:] FROM binance

Slicing with Steps

Template: select array_name[index_range_with_step] from stream_name

Slicing examples

-- select all Entries array elements
SELECT entries[::] FROM binance

-- select all  Entries array elements in the reversed order
SELECT entries[::-1] FROM binance

-- select  Entries array elements with indices from 1 to 5 with step=2
SELECT entries[1:5:2] FROM binance

Position

Position function can be applied only within square brackets [] and allows checking each array element one by one by their indices against a specified condition.

Using Position function

-- select Price from Entries array with indices that are greater than 3
SELECT entries[position() > 3].price FROM binance

-- select Price from Entries array with both index that is greater than 3 and Price value that is less than 1000
SELECT entries[price < 1000 and position() > 3].price FROM binance

Array Join

Array Join function allows executing Join operation on nested arrays.

Array Join unfolds nested arrays and joins every array element with the existing stream message. Empty elements are skipped.

Example for illustration purposes:

Consider the following stream:

timeStamp	numbers	characters
1,	[1,2,3],	[]
2,	[3,4,5],	[]
3,	[],	[a,b,c]
4,	[],	[e,f,g]

1. Let's make Array Join by Characters:

   -- Numbers arrays are NULL, because we Join by Characters arrays
   SELECT * FROM stream
   ARRAY JOIN characters

   timeStamp	numbers	characters
   3,	NULL,	a
   3,	NULL,	b
   3,	NULL,	c
   4,	NULL,	e
   4,	NULL,	f
   4,	NULL,	g

2. We can get the same result a bit different way:

   SELECT timestamp, numbers, characters FROM stream
   ARRAY JOIN characters

   timeStamp	numbers	characters
   3,	NULL,	a
   3,	NULL,	b
   3,	NULL,	c
   4,	NULL,	e
   4,	NULL,	f
   4,	NULL,	g

3. Let's make Array Join by Numbers:

   -- Characters arrays are NULL, because we Join by Numbers arrays
   SELECT timestamp, numbers, characters FROM stream
   ARRAY JOIN numbers

   timeStamp	numbers	characters
   1,	1,	NULL
   1,	2,	NULL
   1,	3,	NULL
   2,	3,	NULL
   2,	4,	NULL
   2,	5,	NULL

4. Now, let's perform Array Join by both types of arrays:

   SELECT timestamp, numbers, characters FROM stream
   ARRAY JOIN numbers, characters

   timeStamp	numbers	characters
   1,	1,	NULL
   1,	2,	NULL
   1,	3,	NULL
   2,	3,	NULL
   2,	4,	NULL
   2,	5,	NULL
   3,	NULL,	a
   3,	NULL,	b
   3,	NULL,	c
   4,	NULL,	e
   4,	NULL,	f
   4,	NULL,	g

Close to reality example 1:

-- In this example we filtered Entries array by type to output just L2EntryNew type.
-- We use Array Join to unfold Entries array and create a new field called Entry, where each element is Entries array element.
SELECT entry FROM binance
ARRAY JOIN entries[THIS IS deltix.timebase.api.messages.universal.L2EntryNew] AS entry

Close to reality example 2:

-- select Price and Size from Entries array from KrakenTradeEntry message type
-- cast Entries array to KrakenTradeEntry type
-- make array join by Price and Size
SELECT entry.price AS 'TradePrice', entry.size AS 'TradeSize'
FROM kraken
ARRAY JOIN (entries AS array(deltix.qsrv.hf.plugins.data.kraken.types.KrakenTradeEntry))[THIS IS NOT NULL] AS entry

Close to reality example 3:

-- select BID Price/Size and ASK Price/Size from Entry object
-- cast to L1entry object type
-- array join to output BID Price/Size and ASK Price/Size as individual fields
SELECT
entry[side == ASK].price AS askPrice,
entry[side == ASK].size AS askSize,
entry[side == BID].price AS bidPrice,
entry[side == BID].size AS bidSize
FROM kraken
ARRAY JOIN (entries as array(deltix.timebase.api.messages.universal.L1entry))[THIS IS NOT NULL] AS entry

Using Array Join with Asterisk

You can use * in combination with aliases to make Array Join as a new field. In such cases a new fields with Join result is added to the existing set of fields.

-- New field Char is added with Characters array elements as a result of a Join
SELECT * FROM stream
ARRAY JOIN characters AS char

timeStamp	numbers	characters	char
3,	NULL,	[a,b,c],	a
3,	NULL,	[a,b,c],	b
3,	NULL,	[a,b,c],	c
4,	NULL,	[e,f,g],	e
4,	NULL,	[e,f,g],	f
4,	NULL,	[e,f,g],	g

-- We Join Characters as Numbers and as a result Numbers type is changed to string.
SELECT * FROM stream
ARRAY JOIN characters AS numbers

timeStamp	numbers	characters
3,	a,	[a,b,c]
3,	b,	[a,b,c]
3,	c,	[a,b,c]
4,	e,	[e,f,g]
4,	f,	[e,f,g]
4,	g,	[e,f,g]

Close to reality example:

In this example we unfold Entries array and create a new field called Joined_Field, where each entry is Entries array element.

Operations with Array Join

You can apply other, mentioned in this tutorial, operations in combinations with Array Joins.

-- select and Joins just Price > 2000 as Entry from the Entries array
SELECT entry FROM binance
ARRAY JOIN entries AS entry
WHERE entry.price > 2000

-- select and Join Price as L1Price just from L1Entry array type
SELECT entry.price as L1Price FROM binance
ARRAY JOIN entries AS entry
WHERE entry IS L1Entry

Join Multiple Arrays

You can use Array Join with more than one type of array.

Join multiple arrays

-- use Array Join to unfold Entries polymorphic array and output various array types as separate fields
SELECT entry, num FROM binance
ARRAY JOIN entries AS entry, enumerate(entries) AS num

Left Array Join

You can use Left Array Join to display elements that are not joined. In this case, not joined elements are returned as is. Joined elements are joined the regular way.

Example for illustration purposes:

Let's consider the following stream.

timeStamp	numbers	characters
1,	[1,2,3],	[]
2,	[3,4,5],	[]
3,	[],	[a,b,c]
4,	[],	[e,f,g]

Let's make Left Join by Characters. In this case Numbers are returned without changes and Characters are unfolded into 6 messages.

Using LEFT ARRAY JOIN

-- Numbers array remain as is. Join is made by Character array elements.
SELECT timeStamp, numbers, characters FROM stream
LEFT ARRAY JOIN characters

timeStamp	numbers	characters
1,	[1,2,3],	[]
2,	[3,4,5],	[]
3,	NULL,	a
3,	NULL,	b
3,	NULL,	c
4,	NULL,	e
4,	NULL,	f
4,	NULL,	g