Skip to main content

tQL - tSM Query Language

What is tQL?

tQL is a query language for querying tSM entities with ElasticSearch index support. It is similar to ElasticSearch SQL and OpenSearch SQL with some differences:

  • Enhanced support for nested objects
  • tQL consults tSM Column Definitions for data types and other configuration
  • Post-processing of elastic result (calculations)
  • Conversion of elastic result using tSM Converters (e.g. translate priorityId to priority name)

Please note that although tQL has a similar syntax to SQL, there are many differences. Please see a detailed explanation of Elasticsearch.

tQL is used extensively in tSM:

  • tSM Listings
  • tSM Reporting and Dashboards (aggregations, charts)
  • Dedicated tQL Console to run and debug queries
  • API queries
  • Access rules definition (filters)
  • SLA calculation (filters)

Select Queries

tQL supports retrieving rows/columns data from the existing indexes. Like SQL-similar languages, tQL uses standard syntaxes for the SELECT statement which contains main clauses SELECT, FROM, WHERE, GROUP BY, ORDER BY, and LIMIT.

SELECT select_list FROM index [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] [NULLS FIRST | LAST] ] [LIMIT max_doc_count]

The general execution of SELECT is as follows:

  1. All elements in the FROM list are computed. Currently, FROM supports exactly one index. However, do note that the table name can be a pattern.
  2. If the WHERE clause is specified, all rows that do not satisfy the condition are eliminated from the output.
  3. If the GROUP BY clause is specified, or if there are aggregate function calls, the output is combined into groups of rows that match one or more values, and the results of aggregate functions are computed. If the HAVING clause is present, it eliminates groups that do not satisfy the given condition.
  4. The actual output rows are computed using the SELECT output expressions for each selected row or row group.
  5. If the ORDER BY clause is specified, the returned rows are sorted in the specified order. If ORDER BY is not given, the rows are returned in whatever order the system finds the fastest to produce.
  6. If the LIMIT is specified (cannot use both in the same query), the SELECT statement only returns a subset of the result rows.

tQL support all general types of queries:

  • Retrieving columns data and constant values from a table
  • Calculate or summarize data
  • Find quickly specific data by filtering on specific criteria (conditions)
  • Ordering collection of JSON-doc by fields
NameStatus
Test TicketIn Progress