TQL Query Syntax

This page serves as a guide for the most common Teneo Query Language queries.

For more information, please see TQL Cookbook, TQL Syntax Overview, TQL manual and TQL reference guide.

Command

When using TQL, we are always telling it to do something for us: show us a list of user inputs, count the number of times the Safetynet occurred, compute the number of sessions per day, etc. The action we tell TQL to take is what we call the command. The command is the most crucial part of a TQL query, as it tells TQL how to process and present the information that the query extracts from the session logs.

This query starts with a command: la, which is shorthand for listAll:

la s.id, t.e.userInput, t.e2.answerText:  t.e.userInput != "" limit 30 

These are the five commands that form the basis of TQL:

Command	Abbreviated Form	Return Value
listAll	la	list
listUnique	lu	list
countAll	ca	integer
countUnique	cu	integer
distribute	d	table

The command is an essential part of every TQL query. Without it, you will receive a Query Failed message.

List all

The listAll function retrieves all values matching the query and returns a list.

Example	Description
la t.e.userInput	Lists all user inputs, including duplicates

List unique

The listUnique function retrieves all unique values matching the query and returns a list.

Example	Description
lu s.id	Lists all unique session ids
lu t.e.userInput	Lists all user inputs, excluding duplicates

Count all

The countAll function counts all values matching the query and returns an integer.

Example	Description
ca t.e.userInput	Counts all user inputs

Count unique

The countUnique function counts all unique values matching the query and returns an integer.

Example	Description
cu t.e.userInput	Counts all unique user inputs, excluding duplicates
cu s.id	Counts the number of dialogues / the number of unique sessions

Uniqueness is determined by string comparison and is case sensitive.

Distribute

The distribute function counts the number of occurrences for all unique values matching the query and returns a 2-column table of unordered counts.

Example	Description
d t.e.userInput	Shows a distribution of occurrences of each unique user input (including empty values)

Constraint

If we query a heavily used system to show us a list of all user inputs it has ever received, the result will be more data than we can handle. We need to filter it down, perhaps to a specific time frame or event, for example. These filters we add are called constraints. Constraints are used to specify which session, transaction, and event properties are relevant for our query. Not every query needs a constraint; they are only necessary if we wish to add a layer of specificity. If we're exploring our data, we may first want to see an overview of what's there, and then refine the query with constraints to zoom in on what we need. Constraints are separated from the selection by a colon symbol (:) and can consist of multiple terms, all separated by commas.

In our example query the constraint part is t.e.userInput != "" :

la s.id, t.e.userInput, t.e2.answerText: t.e.userInput != "" limit 30

The command and selection in this example are used to tell TQL to show us all the user input + bot response pairs, along with the session ID they belong to. The added constraint excludes the input + response pairs that are blank, i.e. those where the person chatting with the bot hit enter without typing a message. The constraint syntax allows us to specify conditions that must be met in order to include those particular properties in the selection. We can use standard logical operators as well as regular expressions for constraints.

Core constraint operators

TQL supports the eight basic constraint operators listed in the table below. The table shows a brief explanation of their function, an indication of whether or not they support regular expressions, what field types they apply to, and example event properties they can be applied to.

Operator	Function	Support regex	Applies to	Example event properties
==	equals	no	strings, numeric, date	userInput, index, beginTime
!=	not equals	no	strings, numeric, date	userInput, index, beginTime
~=	matches	yes	strings	userInput, folder, fname
!~	not matches	yes	strings	userInput, folder, fname
~~	contains	no	analyzed text	userInputWords, answerTextWords
!~~	not contains	no	analyzed text	userInputWords, answerTextWords
exists	exists	no	any	any
notexists	does not exist	no	any	any
>, <, >=, <=	greater than, less than, greater or equal than, less or equal than	no	numeric, date	transactionsCount, beginTime,

The exists and notexists operators are special in that they only take one argument (the event property to check) and return a match if the constraint is met, e.g. if the parameter exists when we are using the exists operator.

The 'contains' operator ~~ can be used to perform case-insensitive queries of userInputWords and answerTextWords, by checking if a field contains or does not contain a word or word sequence. This is possible because these two fields are stored as analyzed text, which is lowercased, tokenized, and normalized before being stored as an array of tokens (words).

Time Constraint

The built-in Date Range Selector allows you to easily add time constraints to your query.

The default setting for the start date is one week prior to the current date and "today" for the end date.

Advanced constraints

TQL offers two advanced possibilities you can use to construct especially precise queries: sub queries and skip constraints.

Sub queries

Sub queries allow you to nest a complete TQL query in a main query and access the results in the selection. This is useful, for example, in exploring aggregate results such as averages, etc. The schematic for sub queries is:

x = @( TQL query )

This example shows our sample query adapted as a sub query. In the selection we display unique occurrences of the ID we found:

lu result.id: result = @(la s.id as id, t.e.userInput, t.e2.answerText:  t.e.userInput != "")

Skip constraints

Skip constraints helps the you find inputs that come after a specific input that triggered a flow. For example when you want to see what input came right after the user hits the Safetynet fallback flow. The skip-to syntax specifies the order of precedence among transitions or events by setting out a start point and an end point, where the start or end point has some property. The syntax (in pseudo-code) is schematically as follows:

start -{end point constraints}> end point
end point <{start point constraints}- start

The start and end points can be transactions or events (not necessarily in the same transaction). The constraints enclosed by {} specify the constraints that the start or end point must fulfill. The direction of the arrow specifies whether the constraint is skipping forward or backward in the session.

Meta-parameter

Independent of the TQL query that is going to run, it may be important to use Teneo Query Language to present the data in a certain way or to run a limited form of the query, e.g. to test it before putting it into operation. This is where meta-parameters come in. Meta-parameters are optional instructions that you can add to your query. They generally affect the behavior of TQL, rather than operating directly on the data.

In our query the meta-parameter is limit 30, telling TQL to only show 30 results:

la s.id, t.e.userInput, t.e2.answerText:  t.e.userInput != "" limit 30

The three meta-parameters available in TQL are:

Meta-parameter name	Description
order by	Sort results by a certain property or variable, either ascending or descending
limit	Limit the query to n results (to save processing time if you don't need to see all the results)
sample	Show a sample of results (to save processing time; also when first testing a query to confirm that it does get results)

Meta parameters are added to the end of a valid TQL query with no separator (comma). An exception is the meta-parameter sample, which is placed at the beginning of the query.

Selection

When we use TQL to get information for us, we have to tell it which bits of information we want. This mandatory part of the query is called the selection. The selection indicates which properties are displayed by the query.

In our query the selection part is s.id, t.e.userInput, t.e2.answerText:

la s.id, t.e.userInput, t.e2.answerText:  t.e.userInput != "" limit 30

Adding labels to a selection

The query generates a list of entries in which the session ID, user input, and answer text of a single transaction are displayed.

In the query results, each column is given a default name that matches the property queried. So the column with session ID is labelled s.id, just as it appears in the query:

You also have the option of adding more meaningful header labels to your results using the following format:

la s.id as "session ID", t.e.userInput as userInput, t.e2.answerText as "answer text":  t.e.userInput != "" limit 30

When adding labels to query selections you can apply multi-word labels in double quotes or a single word without quotes. The advantage when not using quotation marks is that you can refer to the single word label in other parts of the query, for example: la t.e.userInput as userInput: t.e.userInput != "" order by userInput.

Transformer

We are often interested in viewing data over time in order to identify trends; we want to be able to see how our bot's conversations develop over time. Transformers are a group of TQL functions that make it easier to query dates and trends. They perform background computations that deliver new data values that you can display or query. Transformers appear as part of a query's constraint, and the transformed values can be used as part of the selection. There are two types of transformers used by TQL:

• Date transformers
• Trend transformers

Our focus here is on date transformers. For information about trends, check the TQL Reference Guide and the TQL Cookbook.

Date transformers

Date transformers allow you to organize events by different periods of time. They use the TQL function catd which allows you to specify one or both of the following:

• a model, specifying the level of granularity for the query. Possible values are:

  • date

  • day-of-week

  • month

  • quarter

• a pattern, specifying which date or time format to use (based on Java specifications for date format strings): Some examples:

  • yyyy-MM-dd

  • kk

  • yyyy-'w'ww

  • ...

Optional parameters allow you to specify a time zone or Java Locale. Please refer to the manual for more detail.

Let's look at a sample query that shows the number of conversations per day that occurred during business hours. Notice that you can format and also comment your queries to make them easier to read.

d date: 
  catd(model="date") s.beginTime as date,	// tells TQL to organize the results by unique dates
  catd(pattern="HH") s.beginTime as hour,	// tells TQL to make hour available in the query
  hour == in {"09".."18"}					// constraint to only show sessions during these hours
  order by date

Here you can see the results sorted by date. The first column shows the date. The second shows the number of sessions during business hours. This information could also be fed into a dashboard, where it could be converted into a graph.

Keep in mind that if you use Date Transformers in your queries in the Log Data Source, you should remove the time constraint in the Date Range Selector to avoid having potentially mismatching time ranges.