Skip to content

TQL

Disclaimer: work in progress, not complete yet.

Introduction

TQL (Tiny Query Language) is a proprietary query language tailored for the BurstIQ Platform; however, it mimics SQL very closely although more limiting than SQL. Being familiar with SQL will allow the reader to pick up on the subtle differences rather quickly.

For very complex aggregation or manipulations/transforms it is recommended that the reader review the TQLFlow documentation.

TQL can be used with IQ and BurstChain® endpoints. In fact, in some cases just a WHERE clause is required as a way of allowing the user to just supply that construct.

Syntax

When creating a new TQL query it should resemble this basic structure where SELECT and FROM Clauses are required and all other Clauses are optional.

Clause Syntax Order:

SELECT
FROM
[JOIN | INNER JOIN]
[LEFT OUTER JOIN]
[WHERE]
[GROUP BY]
[ORDER BY]
[SKIP]
[LIMIT]

NOTE: all Keywords can be UPPER case or lower case, but not MixED case.

The Full Grammar definition for TQL can be found here.

Clauses

Clause Description
SELECT Used to select data from a database
FROM Used to designate the dictionary the where clause applies
JOIN Used to combine rows from two or more tables, based on a related column between them
WHERE Used to extract only those records that fulfill a specified condition
GROUP BY Used with Aggregator functions to group the result-set by one or more columns
ORDER BY Used to sort the result-set in ascending or descending order
SKIP Used to skip N number of records (occurs before limit)
LIMIT Used to restrict the output to only N number of records

Examples

In these examples assume a transaction dictionary exists and is populated with thousands of records/assets.

Basic:

All of the documents in this collection/chain are returned!

SELECT *
FROM transactions

Select:

Used to select data from a database

SELECT <field>, <field>, ... <field>

SELECT can have one or more fields and field can be any of these: attribute, literals, select-functions, ALL, *, aggregator-function

Only the documents in this collection/chain that contain an attribute called ‘num’ whose numeric value is 234 are returned. Let’s assume 100 documents are returned.

SELECT *
FROM transactions AS t
WHERE t.num = 234

From:

Used to designate the dictionary the where clause applies, this the is dictionary we are querying data from.

FROM <dictionary-name>

This example is querying data from the transactions dictionary. This is also showing how to create an alias for the dictionary to use alongside attributes, which can be seen in the WHERE clause.

SELECT *
FROM transactions AS t
WHERE t.num = 234

Join (Inner):

Used to combine rows from two or more tables, based on a related column between them

When joining two collections together, there must be something to match between the two collections.

INNER JOIN <dictionary-name> AS <alias> ON <condition>

SELECT e.id AS id, e.amount AS amt, t.charge AS chg
FROM encounters AS e
INNER JOIN transactions AS t ON t.id = e.id
WHERE e.num = 15131

Where:

Used to extract only those records that fulfill a specified condition

WHERE <condition>

This TQL example matches records where the attribute num is equal to 234

SELECT *
FROM transactions AS t
WHERE t.num = 234

Group by:

Used to combine rows from two or more tables, based on a related column between them

A simple Group by with Join.

GROUP BY <attribute>, ..., <attribute>

SELECT e.id AS id, e.amount AS amt, t.charge AS chg
FROM encounters AS e
INNER JOIN transactions AS t ON t.id = e.id
WHERE e.num = 15131
GROUP BY id, amt
LIMIT 666

Order By:

Used to sort the result-set in ascending or descending order

ORDER BY <attribute> [DESC]

The 100 documents returned will now be sorted by first_name in descending order. If no ASC or DESC is specified ASC is the default.

SELECT *
FROM transactions AS t
WHERE t.num = 234
ORDER BY t.first_name DESC

Skip:

Used to skip N number of records (occurs before limit)

SKIP <integer>

The sorted 100 documents returned will now be limited to just the first 10 AFTER skipping the first 40.

SELECT *
FROM transactions AS t
WHERE t.num = 234
ORDER BY t.first_name DESC
SKIP 40
LIMIT 10

Limit:

Used to restrict the output to only N number of records

LIMIT <integer>

The sorted 100 documents returned will now be limited to just the first 10.

SELECT *
FROM transactions AS t
WHERE t.num = 234
ORDER BY t.first_name DESC
LIMIT 10

Large TQL Example:

This example demonstrates all of the possible constructs and the ability to build complex TQL statements.

SELECT e.id AS id,
   e.location AS location,  
   sum(e.amount) AS amt,  
   sum(t.charge) AS chg,  
   sum(bc.total_charges) AS total_chg,
   push('a') AS aaaaa,
   push({'id': t.id}) AS ids,
   push({'x': [e.id, p, 'j']}) AS array,
   append(['a'], x, ['a', 'b']) AS mergedArray,
   {
     'x': x,
     'd': append([x, 'a'])
   } AS doc,
   ['a', IF(b > 0 then 'b' else 'c'), MAP(x USING elem with ADD(elem, 3))] AS a,
   3L AS mylong,
   3.3 AS myfloat,
   true AS myboolean,
   Date('13/4/2020') AS mydate,
   first(c.status) AS status,
   attr
FROM encounters AS e
INNER JOIN transactions AS t ON (t.id = e.id AND t.chg < e.amount)
INNER JOIN billed_charges AS bc ON (bc.id = e.id AND bc.flag IN ('a', 'b', 'c'))
INNER JOIN col AS c ON col.id = e.id
WHERE e.num = 15131 AND e.amount > 100
GROUP BY id, location, status, attr
ORDER BY id desc, attr
SKIP 100
LIMIT 600