Additional tQL features

Data Transformation with the Painless Script (instead of Format-function)

select converted statusId as "Status", priorityId as priority,  creationDate,
script( ' def month;
    if (doc["creationDate"].size()!=0) 
     { 
        Instant instant = Instant.ofEpochMilli(Long.parseLong(doc["creationDate"].value.millis.toString()));
        ZonedDateTime zdt = ZonedDateTime.ofInstant(instant, ZoneId.of("Z"));
        month = zdt.getMonthValue();
     }
    else month = 0;
    return month ') as month
from ticket
where creationDate between '2022-02-01T00:00:00.000+0000' and '2022-02-28T00:00:00.000+0000'
order by statusId desc

Ordering collection of JSON-doc by fields

The ORDER BY clause is used to sort the results of SELECT by one or more expressions ORDER BY expression [ ASC | DESC ] [, ...], where the expression represents an input column or an output column**.**

select typeCode as "PEW type"
       , id as "PEW count"
       , chars.project
    from ticket
    where creationDate between '2022-03-01T00:00:00.000+0000' and '2022-03-05T00:00:00.000+0000'
    order by  chars.project asc
    Limit 5

PEW type	PEW count	chars.project
Story	e0b3ba37-cccd-4ee9-8e3e-1598bdec6a73	ADAPSCET
Story	ae1e9265-cda2-4d70-ba47-41c921e51fd7	ADAPSCET
Subtask	ca9ace47-28c2-4881-b8dc-c71820f0b9d9	CET
Subtask	15aa1cd5-aa3b-4961-8426-74eb82565971	CET
Story	1318e692-d78e-43cc-8ebe-81345e4ad155	CET

Using Group by and Painless for Calculation in Nested Objects

More complex example:

select custom.worklogs.workerId,
round(sum(custom.worklogs.duration)/60/8, 1) AS "Sum duration MD"
from ticket
group by custom.worklogs[startDate > '2022-01-01T00:00:00.000+0000'].workerId

custom.worklogs.workerId	Sum duration MD
6f30f0d3-1074-45a9-bfe9-93f0c802a10a	618.9
d8769f98-9758-4fcd-80a2-410c9d915b52	92.5
e7f5865c-7719-4a14-beef-4bce2aae34f9	30.6
bd4cabfe-17b5-4d43-9020-7b8d04187567	74.5

or even:

select custom.worklogs[].workerId,
    format(histogram(custom.worklogs[startDate > '2022-01-01T00:00:00.000+0000'].startDate, 'month'), 'MM') as "Month",  
    round(sum(custom.worklogs.duration)/60/8, 1) AS "Sum duration MD"
from ticket
group by custom.worklogs[].workerId
, histogram(custom.worklogs[startDate > '2022-01-01T00:00:00.000+0000'].startDate, 'month')

custom.worklogs.workerId	Month	Sum duration MD
6f30f0d3-1074-45a9-bfe9-93f0c802a10a	01	118
e7f5865c-7719-4a14-beef-4bce2aae34f9	01	11.3
6c570ee6-139e-47b0-ac8f-8c9ae2d203ee	01	16.6
d8769f98-9758-4fcd-80a2-410c9d915b52	01	16.4

Post-Processing

All tQL queries run against the ElasticSearch index, and query results are then translated to tabular form. They are post-processed before returning to the client.

Converted

The general expression for post-processing is “converted”.

select converted key, name, priorityId, statusId from ticket

TSMTTS-1430	Display modification	Blocker	Closed
TSMTTS-1429	Another issue	Major	Closed

In this case, each column is consulted with tSM Listing Column Definition to display “converter” and found values. The value is then translated (typical converters are “register value id” -> register value name”, boolean to “Yes”/”No” etc.).

Please note that the result is the same as if you use the convert method for all fields.

select convert(key), convert(name), convert(priorityId), convert(statusId) from ticket

The conversion optional parameters:

select converted(withOrigins) *  from ticket

• withOrigins - add original values as a new column at the end with _origin prefix. It is useful for aggregation reports, where we need information about the original value before conversion

select converted(withOrigins) statusId from ticket group by statusId

statusId	origin_statusId
Nový	17da3ee8-ac27-0633-61f5-b03bec1d79e4
Uzavřen	e9d53fcd-f867-439e-c1ed-8667cc48038e
Zpracování	f4961557-06c0-4b5c-bdc1-2a76efc1c2e2

• useConvertOnBackend - consult column definition for the flag convertOnBackend and do the conversion only if the flag is set. We use this settings from tSM UI application to select which fields should be converted on backend (e.g. ID to name translation) and which on frontend only (e.g. user icon)

• useExportFields - if exportField is defined in the listing column definition, use this column instead of the requested field. We use it for export to excel.

• skipConvert - switch off conversion. It can be used in combination with other parameters.

select converted(skipConvert) statusId
from ticket
group by statusId

statusId
17da3ee8-ac27-0633-61f5-b03bec1d79e4
e9d53fcd-f867-439e-c1ed-8667cc48038e
f4961557-06c0-4b5c-bdc1-2a76efc1c2e2

Internals

If you execute a query like this:

select priorityCode, round(count(id) / 100, 1) from ticket group by priorityCode

priorityCode	calc4
	0.8
blocker	3.4
critical	6.2
major	33.6

What happens in the background is:

• internal column “__internalCalc1” is created for count(id)
• internal column “__internalCalc2” is created for “__internalCalc1” / 100
• internal column “__internalCalc3” is created for “round(__internalCalc2)”
• Only “__internalCalc1” makes it to the ElasticSearch query
• For each ElasticSearch result row, the internal calculation for __internalCalc2 and __internalCalc3 is evaluated
• The result is returned as calc4 (from __internalCalc3)

Functions

A list of functions supported in tQL.

Select transformation

script

Script functions support painless scripts for extended data transformation.

For additional information about painless see https://www.elastic.co/guide/en/elasticsearch/reference/current/modules-scripting-painless.html

For example, it could be used simultaneously to check null values and transform data:

select typeCode as "PEW type"
             , script('if (doc["closedDate"].size() !=0)
                         doc["closedDate"].value.millis / (60.0 * 10.0*60); 
                    else "2022-03-04T01:16:43.046+0000"
                ') as closedDate
             from ticket
    limit 2

PEW type	closedDate	EW type
	2022-03-04T01:16:43.046+0000
Subtask	45648841.277777776	Subtask

script_array

Elasticsearch always returns script results as an array. The default behavior in tQL is that if the array contains only one field, it will return the extracted value. But if you know that the result should be an array, you might force it with this function instead of the standard script.

select typeCode as "PEW type"
             ,script_array(
                 'def array = new def[] {2, 3, 4, 6};
               return array ') as closedDate
        from ticket
where closedDate between  '2022-03-04T01:16:43.046+0000' and
'2022-03-14T01:16:43.046+0000'
limit 30

PEW type	closedDate
Ukol	2,3,4,6
Subtask	2,3,4,6
Subtask	2,3,4,6
Subtask	2,3,4,6
Subtask	2,3,4,6

searchHit

searchHit() returns the whole object from ElasticSearch. This function is mainly used for internal tSM purposes, but you can use it also to consult the whole JSON document content.

select searchHit() from ticket

{
  "_index": "dataliteticket_20220516_064313",
  "_type": "_doc",
  "_id": "null",
  "_score": 1.0,
  "_source": {
    "custom": {
      "worklogs": [
        {
          "_index": "dataliteticket_20220516_064313",
          "_type": "_doc",
          "duration": 60,
          "workerId": "04f316cd-4897-45e3-8145-7bf9e951c9a4",
          "endDate": "2021-09-16T12:16:0"
        }
      ]
    }
  }
}

JSON

Constructs JSON objects as a result, and it can be joined from multiple columns.

select
-- property names are generated automatically if not specified
json(key, name),
-- key can be identifier or double quoted string
json(myKey: key, "My complex key": name),
-- example of constant and complex value in json function call
json(name: 'myScript', scriptValue:
     script('return params._source["tasks"]?.stream()?.collect(Collectors.toList())')
)
from ticket

Datediff

Returns the difference between two dates. The first parameter is the result units.

select converted key as "ID"
, tasks[].id
, tasks.history.userGroupCode
, dateDiff('ms', tasks[].startTime, first(tasks[].history[changes = 'USER_GROUP' and userId is not null].startTime)) as Date_Diff
from ticket
where tasks[].history[changes = 'USER_GROUP'] and key = 'PEW-3973'
group by key
, tasks[].id
, tasks[].history[changes = 'USER_GROUP'].userGroupCode
, tasks[].startTime

ID	history - user group	Date_Diff
pew-3972	ZPEW	192
pew-3973	ZPEW	1596
pew-3974	ZPEW	911
pew-3975	ZPEW	1525
pew-3976	ZPEW	192
…	…	…

You can use other values - mcs', 'ms', 'ss, 'mi', 'hh', 'dd', 'wi', 'mo', 'qq', and 'yyyy’.

Notice. The converted result is a truncated integer value of a time interval (for example 22 h -> 0 d).

select datediff('mcs','2019-12-31T23:59:59.00+0000', '2020-01-01T00:00:00.00+0000') as Interval_mcs
, datediff('ms','2019-12-31T23:59:59.00+0000', '2020-01-01T00:00:00.00+0000') as Interval_ms
, datediff('ss','2019-12-31T23:59:59.00+0000', '2020-01-01T00:00:00.00+0000') as Interval_ss
, datediff('mi','2019-12-31T23:59:59.00+0000', '2020-01-01T00:00:00.00+0000') as Interval_mi
, datediff('hh','2019-12-31T23:59:59.00+0000', '2020-01-01T00:00:00.00+0000') as Interval_hh
, datediff('dd','2019-12-31T23:59:59.00+0000', '2020-01-01T00:00:00.00+0000') as Interval_dd
, datediff('wi','2019-12-31T23:59:59.00+0000', '2020-01-01T00:00:00.00+0000') as Interval_wi
, datediff('mo','2019-12-31T23:59:59.00+0000', '2020-01-01T00:00:00.00+0000') as Interval_mo
, datediff('qq','2019-12-31T23:59:59.00+0000', '2020-01-01T00:00:00.00+0000') as Interval_qq
, datediff('yyyy','2019-12-31T23:59:59.00+0000', '2020-01-01T00:00:00.00+0000') as Interval_yyyy
from ticket
limit 1

Interval_mcs	Interval_ms	Interval_ss	Interval_mi	Interval_hh	Interval_dd	Interval_wi	Interval_mo	Interval_qq	Interval_yyyy
1000000	1000	1	0	0	0	0	0	0	0

If you need an approximate result, you can transform milliseconds (‘ms’) by arithmetic transformation like

dateDiff('ms', tasks[].startTime, tasks[].history[].startTime) /1000.0 / 60.0 /60.0

and use the format function.

select round(datediff('ms',tasks.history.startTime,tasks.history.endTime)/1000.0/60.0 , 1)
from ticket
group by tasks[].history[].startTime, tasks[].history[].endTime

Duration
13.1
0.7
0
91.2
200.1
8
1213.4
13.4
…

Note that if datediff function has been used with nested values, you have to use group by clause to return single values of dates (since nested values could contain an array of dates) otherwise the result would be null.

Wrong result

select round(datediff('ms',tasks.history.startTime,tasks.history.endTime)/1000.0/60.0 , 1)
from ticket

Duration

null

null

null

…

Post-Processing

round

select round(avg(custom.worklogs.duration), 2) as rand_avg
from nested(ticket,  custom.worklogs)
group by custom.worklogs[]

rand_avg

160.95

format

format(whenInserted, 'dd.MM.yyyy')

select format(histogram(creationDate, 'month'), "YYYY-MM") as month
from ticket

month

2022-02

2022-03

2022-04

2022-05

• "dd" represents the day of the month
• "MM" represents the month
• "yyyy" represents the year
• "HH" represents the hour in 24-hour format
• "mm" represents the minutes
• "ss" represents the seconds

convert

Use keyword converted for po st-processing, and use a converter to translate one value for another:

• select
• convert(priority), // default converter configured for column priority
• convert(myBooleanCol, 'booleanTranslateValueConvertor'), // convert with boolean converter

For example:

• priorityId - keyword, indexed as is. There is a registered converter to translate GUID to name in tSM
• priorityName - indexed with a normalizer ASCIIi and lowercase. Thus results are normalized. This column has no converter in tSM, no post-processing is happening. See https://www.elastic.co/guide/en/elasticsearch/reference/current/normalizer.html

Converted rules definition is allocated in Listeners->Fields configuration

Select convert(priorityId), priorityName
from ticket
group by priorityId, priorityName

Priority	priorityName
Major	major
Trivial	trivial
Blocker	blocker
Minor	minor
Critical	critical

Without conversion, the result looks a little bit different

Select priorityId, priorityName
from ticket
group by priorityId, priorityName

priorityId	priorityName
6e71a823-806e-3d03-11ee-0f0e2d55061d	major
6fd9ef55-bcec-f460-1244-0b43b3605b34	trivial
795c96a5-4b5b-7b8a-9f57-428b33aa0534	blocker
910c2db8-77da-2e2b-590b-b00f929492fe	minor
c9c96175-67b8-e8c0-6f4b-59a67a53f808	critical

Notice. Converted is not applied to the WHEN clause because it’s post-processing operation

select convert(priorityId) as priority, convert(statusId) as Status custom.worklogs.workerIdfrom ticket
where priorityName like 'major'
order by statusId desc

Additional Samples of the Complex Usage of tQL Clauses for Data Retrieval and Transformation

Support of sum, count, max, min, and avg realized in tQL could also be applied with complex painless scripts:

select count(id)
        , max( 
            'def month, d;
            if (doc["creationDate"].size()!=0)
                { Instant instant = Instant.ofEpochMilli(Long
                .parseLong(doc["creationDate"]
                .value.millis.toString())); 
                ZonedDateTime  zdt = ZonedDateTime.ofInstant(instant, ZoneId.of("Z"));
                month = zdt.getMonthValue();  
                } 
return month') as max_month
    , round(avg(
        'def month, d;
            if (doc["creationDate"].size()!=0)
                { Instant instant = Instant.ofEpochMilli(Long
                .parseLong(doc["creationDate"]
                .value.millis.toString())); 
                ZonedDateTime  zdt = ZonedDateTime.ofInstant(instant, ZoneId.of("Z"));
                month = zdt.getMonthValue();  
                } 
return month'
     ) , 2) as avg_duration
from ticket
where statusName like 'Closed'

Date Transformation with the Painless Script (instead of Format-function)

select converted statusId as "Status", priorityId as priority,  creationDate,
script( ' def month;
    if (doc["creationDate"].size()!=0) 
    { 
        Instant instant = Instant.ofEpochMilli(Long.parseLong(doc["creationDate"].value.millis.toString()));
        ZonedDateTime zdt = ZonedDateTime.ofInstant(instant, ZoneId.of("Z"));
        month = zdt.getMonthValue();
    }
    else month = 0;
    return month ') as month
from ticket
where creationDate between '2022-02-01T00:00:00.000+0000' and '2022-02-28T00:00:00.000+0000'
order by statusId desc

Convert

All tQL queries run against the ElasticSearch index, and query results are then translated to tabular form. They are post-processed before returning to the client. The general expression for post-processing is “converted”.

select converted key, name, priorityId, statusId from ticket

TSMTTS-1430	Display modification	Blocker	Closed
TSMTTS-1429	Another issue	Major	Closed

In this case, each column is consulted with tSM Listing Column Definition to display “converter” and found values. The value is then translated (typical converters are “register value id” -> register value name”, boolean to “Yes”/”No” etc.).

Please note that the result is the same as if you use the convert method for all fields.

select convert(key), convert(name), convert(priorityId), convert(statusId) from ticket

Internals

If you execute a query like this:

select priorityCode, round(count(id) / 100, 1) from ticket group by priorityCode

priorityCode	calc4
	0.8
blocker	3.4
critical	6.2
major	33.6

What happens in the background is:

• internal column “__internalCalc1” is created for count(id)
• internal column “__internalCalc2” is created for “__internalCalc1” / 100
• internal column “__internalCalc3” is created for “round(__internalCalc2)”
• Only “__internalCalc1” makes it to the ElasticSearch query
• For each ElasticSearch result row, the internal calculation for __internalCalc2 and __internalCalc3 is evaluated
• The result is returned as calc4 (from __internalCalc3)

Aggregation Functions and Painlesss Scripts

Support of sum, count, max, min, and avg realized in tQL could also be applied with complex painless scripts:

select count(id)
        , max( 
            'def month, d;
            if (doc["creationDate"].size()!=0)
                { Instant instant = Instant.ofEpochMilli(Long
                .parseLong(doc["creationDate"]
                .value.millis.toString())); 
                ZonedDateTime  zdt = ZonedDateTime.ofInstant(instant, ZoneId.of("Z"));
                month = zdt.getMonthValue();  
                } 
return month') as max_month
    , round(avg(
        'def month, d;
            if (doc["creationDate"].size()!=0)
                { Instant instant = Instant.ofEpochMilli(Long
                .parseLong(doc["creationDate"]
                .value.millis.toString())); 
                ZonedDateTime  zdt = ZonedDateTime.ofInstant(instant, ZoneId.of("Z"));
                month = zdt.getMonthValue();  
                } 
return month'
     ) , 2) as avg_duration
from ticket
where statusName like 'Closed'