Teneo Query Language Reference Guide
Main TQL commands
TQL supports five basic commands, as displayed in the table below. Both full and abbreviated forms are valid TQL syntax:
Command | Abbreviated form | Return value |
---|---|---|
listAll | la | list |
listUnique | lu | list |
countAll | ca | integer |
countUnique | cu | integer |
distribute | d | table |
Count All
The countAll function counts all values matching the query and returns an integer.
To count all user inputs, including duplicates:
tql
1ca t.e.userInput
2
To count the total number of user inputs containing the string "hello":
tql
1ca t.e.userInput : t.e.userInput ~= ".*hello.*"
2
Count Unique
The countUnique function counts all unique values matching the query and returns an integer.
To count all unique user inputs, excluding duplicates:
tql
1cu t.e.userInput
2
To count the number of dialogs, i.e. of unique sessions:
tql
1cu s.id
2
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.
The example below counts the number of occurrences for each unique user input (including empty values).
tql
1d t.e.userInput
2
Example output:
List All
The listAll function retrieves all values matching the query and returns a list.
The example below lists all user inputs, including duplicates.
tql
1la t.e.userInput
2
Example output:
List Unique
The listUnique function retrieves all unique values matching the query and returns a list.
List all unique session IDs:
tql
1lu s.id
2
List all user inputs, excluding duplicates:
tql
1lu t.e.userInput
2
Example output:
Selection
The selection specifies what data the TQL query returns. Variable names in the selection are re-used as column headers in the output. Queries composed of a selection with no constraints retrieve all existing matching items in the data.
In contrast, constraints in the TQL query are projected onto the selection, which may result in null or empty results if a constraint is not applicable to a variable in the selection, e.g. due to values belonging to different events.
Handling null results (! operator)
Due to the nature of the Teneo Platform and the large amount of data fields that exist in a solution, TQL results often come with lots of null and undefined values - for example, the query la e.userInput returns empty rows for all events not having the e.userInput property, which are the majority of them. This behavior slows down queries by producing a lot of meaningless results.
Therefore, in order to obtain more convenient query results and enhance the overall performance, Inquire adds, by default, exists-like constraints for every item in the selection, which filters fields with data and removes null and undefined results.
Therefore, the queries listed below return equivalent results:
tql
1la s.id, e.userInput
2
tql
1la s.id, e.userInput : exists e.userInput
2
tql
1la s.id, !e.userInput : exists e.userInput
2
Retrieving null results
Nonetheless, since certain use cases may require seeing undefined and null results, a short-hand syntax has also been introduced that allows to not add exists-like constraints. By using the exclusion operator !, query results also include null results.
Please note that if both exists and ! appear in the same query, exists prevails.
Aliases
TQL supports aliasing using the as notation.
tql
1lu t.e.userInput inputs
2
This example produces the result:
Aliasing can also be used as a shorthand for referring to outputs from transformers (see Transformers section below). In the Trend example below, aliasing is used to access and display multiple elements of the result as specified in the selection:
tql
1lu t.e.userInput, tau, direction, p, z :
2 trend(min-tau="0.0", max-p="0.05") t.e.userInput (tau, direction, p, z)
3
Example output:
Note: Inquire supports unique names only. This means that if two variables are assigned the same alias, only the last assignment is kept. In the example below, the result will be a single column containing the transaction time values. The session-level value from s.beginTime is not displayed.
tql
1lu s.beginTime time, t.time time
2
This behavior also applies to the variable name count, which is automatically created by the distribute command; that is, only one variable called count can exist in the same output and only the last assigned value will be kept.
Constraints
Constraints are used to specify conditions that sessions, transactions, and events properties must fulfill. They are separated from the selection by the colon symbol (:) and consist of multiple terms, separated by a comma. The relation between a property value and its condition is specified by means of one of eight operators below.
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 | x exists | no | any | any |
notexists | x does not exist | no | any | Any |
>, <, >=, <= | greater than x, less than x, greater or equal than x, less or equal than x | 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 it exists.
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).
The example below, which retrieves a list of user inputs, consists of a constraint with three terms specifying that the user session must have been initiated in March 2015, one of its events must have raised a flow located in the "Safetynet" folder, and the event that contains the userInput and the event indicating the folder name are related in the same transaction:
tql
1lu t.e1.userInput :
2 s.beginTime == "2015-03",
3 t.e2.folder ~= ".*?[S|s]afetynet.*?",
4 t.e1-{pathType=="flow-trigger"}>t.e2
5
Set-constraints
Set constraints allow a condition to be evaluated against more than one possible property value. Set constraints are specified by means of one of the constraints operators above, the reserved word in, and possible values enclosed by curly brackets {}.
The example below will return user inputs that consist of only lower case "hi" or lower case "hello":
tql
1la t.e.userInput :
2 t.e.userInput == in {"hi", "hello"}
3
This next example will return user inputs for sessions that were initiated in the range from January 2021 to March 2021:
tql
1la t.e.userInput :
2 s.beginTime == in {"2021-01".."2021-03"}
3
Meanwhile, this example covers all of 2021:
tql
1la t.e.userInput :
2 s.beginTime == in {"2021-01".."2021-12"}
3
It is also possible to put constraints on the hours and minutes as in the example below, which finds inputs for sessions initiated from midnight to 1 a.m.:
tql
1la t.e.userInput :
2 s.beginTime == in {"2021-01-01T00:00".."2021-01-01T01:00"}
3
Skip-constraints
The skip-to syntax specifies the order of precedence among transitions or events by setting out a starting point and an end point, where the end point has some property. The syntax (in pseudo-code) is schematically as follows:
tql
1start -{end point constraints}> end point
2end point <{end point constraints}- start
3
The starting and end points can be transactions or events (not necessarily in the same transaction). The constraints enclosed by {} specify the constraints the end points must fulfill. The direction of the arrow specifies whether the constraint is skipping forward or backward in the session.
The example query below lists non-empty user inputs alongside non-empty VA responses immediately following them:
tql
1lu s.id, t1.e.userInput, t2.e.answerText :
2 t1.e.type == "request",
3 t1.e -{type=="response", answerText~=".+"}> t2.e,
4 t1.e.userInput ~= ".+"
5
The second example query lists non-empty user inputs alongside non-empty VA responses immediately preceding them:
tql
1lu s.id, t1.e.answerText, t2.e.userInput :
2 t2.e.type == "request",
3 t2.e.userInput ~= ".+",
4 t1.e <{type=="response", answerText~=".+"}- t2.e
5
Regular expressions
TQL supports a subset of full Perl-style regular expressions, essentially the same subset supported by Elasticsearch. Regular expressions in TQL are by definition anchored, meaning that any regex must match the entire string from beginning to end. Therefore, the conventional anchoring symbols ^ and $ are not needed.
The table below summarizes the allowed regex symbols:
Operator/Symbol | Explanation |
---|---|
. ? + * { } [ ] ( ) " \ | | Reserved symbols |
. | Any character |
? | Zero or one time |
+ | One or more times |
* | Zero or more times |
{1,3} | Min / max number of matches |
() | Grouping |
| | Alternation |
[a-z], [A-Z], [^a] | Character classes (including negation) |
\d | Any digit |
\w | Any word character |
\W | Any non-word character |
\s | A space character |
\S | A non-space character |
\\b | A word boundary |
\\t | A tabulator character |
\\n | A newline character |
\\r | A carriage return character |
Regular expressions are supported for fields stored as strings only (not for numeric values or analyzed text). Property values are match against regular expression via the ~= (matches) and !~ (does not match) operators. The !~ operator evaluates to true either when the regex does not match or when the property is not present.
The first example below will identify user inputs that contain the words "Hello" or "hello", whereas the second will identify all non-empty inputs.
tql
1lu t.e.userInput : t.e.userInput ~= ".*\\b[H|h]ello.*\\b"
2
tql
1lu t.e.userInput : t.e.userInput ~= ".+"
2
Order of constraints
In the evaluation of a TQL query, the TQL parser transforms the TQL expression into a directed graph that guarantees that each part of the query expression is evaluated in the correct order. Therefore, the order of constraints does not matter.
For example, the two queries below return the same results:
tql
1d e.userInput :
2 s.beginTime == "2015-03",
3 e.userInput ~= ".*hello.*"
4
tql
1d e.userInput :
2 e.userInput ~= ".*hello.*",
3 s.beginTime == "2015-03"
4
Syntax and operators for diverse data types
Inquire holds diverse type of data, namely: string, integers, floats, dates, boolean, arrays, and maps. The following table summarizes the operators that can be used with each of them.
Data type | Example | Operators | Remarks |
---|---|---|---|
String | "foo", "bar" | ==, !=, | A quote sign followed by anything and ending with the same quote sign. The valid quote symbol is " |
Date | "2018-01-01", "2018-01", "2018" | ==, !=, exists, notexists, >, <, >=, <= | A string with an ISO-8601 formatted date or date time. This includes the following java Datetime classes: Date, Instant, LocalDate, LocalDateTime, OffsetDateTime, Time, UtilDate. |
Integer | 1, 3094, -5 | ==, !=, exists, notexists, >, <, >=, <= | An Integer |
Double/Float | 0.42, 1.5 | ==, !=, exists, notexists, >, <, >=, <= | A number with a '.' inside. |
Array | [0,1,2] | ~~, !~, exists, notexists, ==, != | It is possible to check if the array contains a particular value, or if it is exactly the same as a given array. |
Map Object | {name:"john", surname:"doe"} | ==, !=, ~=, !, exists, notexists | Maps (and objects in general) are stored as json strings. It is possible to check if the map contains a particular value by using regular expressions, or if it is exactly the same as a given map. |
Syntax for attributes with inner spaces
In general terms, querying Teneo Studio metadata variables follows standard TQL syntax rules, e.g.:
tql
1la t.e.userInput: t.md:CATEGORY
2
However, metadata name can contain a white space. In this case, the name in the query should be written using the formulas ['<metadata name>'] or ["<metadata name>"], as in:
tql
1la t.e.userInput: t.md:['CURRENT PAGE']=="http://mysite.com"
2
tql
1la t.e.userInput: t.md:["CURRENT PAGE"]==http://mysite.com
2
Engine-matching filters
Condition matching
The matches-condition transformer can be applied to a text field variable and transforms its values into a match or non-match against a solution language object condition. The matches-condition transformer has the following parameters (that all are mandatory):
Parameter | Default value | Description |
---|---|---|
solution | none | string specifying the solution containing the condition(s) |
condition | none | a string representing a language condition |
The output is a boolean value that discards the items that did not match the condition.
The example below returns only those user inputs that match the FOOD.LIST language object in the "en_tlr" solution:
tql
1lu t.e.userInput :
2 t.e.type == "request",
3 t.e.userInput matches-condition (solution = "en_tlr", condition = "%FOOD.LIST")
4
Matching language objects
The matching-lobs transformer can be used to find language objects within a solution that match a given input text. An optional filter is available for partial matching of language objects.
Parameter | Default value | Description |
---|---|---|
solution | none | solution to use, e.g. "en_tlr" |
filter | none | Optional. Regular expression that the language objects must fulfil, e.g. ".?PHR.?" to use PHR lobs only. |
The output values are:
- lob: the name of the language object that matches the input text
- used-words: an array of string that represent the words from the input text that contribute to the actual match
The first example below retrieves all language objects present in the "en_tlr" solution that match any user input that contains the word "no" surrounded by spaces. The second example does the same but looks for PHR language objects only.
tql
1lu t.e.userInput, lob, used-words :
2matching-lobs( solution="en_tlr" )
3 t.e.userInput (lob, used-words),
4t.e.userInput ~= ".* no .* "
5
6
tql
1lu t.e.userInput, lob, used-words :
2matching-lobs( solution="en_tlr", filter=".*?PHR.*?")
3 t.e.userInput (lob, used-words),
4t.e.userInput ~= ".* no .* "
5
Example matching
The matches-examples filter can be used to retrieve text field variables that match the language object condition generated by a set of (positive and/or negative) examples. The parameters are:
Parameter | Default value | Description |
---|---|---|
solution | none | The solution to match example against |
p | none | A string with a positive example to match against |
n | none | A string with a negative example to match against |
- At least one positive or negative example must be specified.
- More than one positive or negative example can be used at the same time.
The example below identifies user inputs matching the condition generated by "can you tell me a riddle?" and "do you know any riddles?". The generated condition depends on the content of the solution being uploaded to the environment as en_tlr.
tql
1lu e.userInput :
2e.type == "request",
3e.userInput matches-examples(
4 solution="en_tlr",
5 p = "Can you tell me a riddle?",
6 p = "Do you know any riddles?")
7
Classification
The classify transformer can be used to classify variable values according to certain criteria expressed using a LOB, e.g. for sentiment analysis identifier or yes/no answers. The parameters are:
Parameter | Default value | Description |
---|---|---|
solution | none | The solution name to profile against |
c | None | One or multiple c conditions having each a string with a language object to match against. E.g.: c=%YES, c=%NO |
<alias> | none | The alias for the variable that will hold the results |
<label> | none | As many labels as c parameters. The actual value assigned to the <alias> variable when the i-th LOB in c has matched. |
<no-match-label> | none | Optional. The value assigned to the <alias> variable when no LOB matches. |
The example below illustrates how user inputs can be classified as either "Positive", "Negative", or "Neutral". Let’s consider a solution named Sentiment that contains two language objects, SENTIMENT_POSITIVE.INDICATOR and SENTIMENT_NEGATIVE.INDICATOR. The classify operation below creates a new variable called "sentiment" which takes three values: Positive when the first LOB is matched, Negative when the second LOB is matched, and Neutral when no LOB matches.
tql
1lu s.id, sentiment :
2classify(
3 solution="Sentiment",
4 c="%SENTIMENT_POSITIVE.INDICATOR",
5 c="%SENTIMENT_NEGATIVE.INDICATOR" ) t.e.userInput
6 ( sentiment, "Positive", "Negative", "Neutral" ),
7t.e.type == "request"
8
The next example illustrates the use of the English TLR solution to annotate the polarity of yes/no answers. There is no "non-match" label, so input text that don’t match any of the two LOBs will be discarded:
tql
1lu s.id, polarity :
2classify(solution="en_tlr", c="%YES.PHR", c="%NO.PHR")
3 t.e.userInput (polarity, "Yes", "No"),
4t.e.type == "request"
5
Transformers
Transformers are a group of TQL functions that take a variable value, execute some data manipulation, and return one or more new data values. The returned value(s) and type of data manipulation differ depending on the transformer. Transformers are used as part of a constraint in the query, and the transformed output can be returned in the selection via aliasing.
With transformers, the Teneo Inquire API may throw a null pointer exception if the type of the variable to be transformed is not correctly specified.
Dates
The catd transformer can be applied to a date field and can be used to transform its time stamp value into other date formats or to group query results by time at different levels of granularity. It accepts the four parameters below, and at least either model or pattern are mandatory.
Parameters and values are listed below:
Parameter | Default value | Possible values | Description |
---|---|---|---|
model | none | "date", "day-of-week", "month", "quarter" | A model for grouping results by time at different levels of granularity |
pattern | none | "yyyy-MM-dd", "kk", "yyyy-'w'ww", etc. | A date format string for time and date output. See Java manual for all possible values. |
locale | "en" | as for Java Locale | A locale for formatting time and date output |
timezone | "UTC" | as described by Joda-Time's DateTimeZone class | A timezone id. Can be an offset relative to UTC, e.g. "-05:00" |
The example below transforms the full time stamp as stored in Teneo Inquire into a conventional year-month-day string (and is equivalent to calling pattern="yyyy-MM-dd"). The second example counts the number of sessions per weekday and the thirds example shows how to use relative time zone offsets to obtain results according to another time zone.
tql
1lu date :
2catd(model="date") s.beginTime date
3
tql
1d day :
2catd(model="day-of-week") s.beginTime day
3
tql
1d date :
2catd(model="date", timezone="-05:00") s.beginTime date
3
Trends
The trend transformer can be applied to a text field variable and transforms its values into a list of trending items. The trend algorithm is based on the Mann-Kendall non-parametric trend test, and the input arguments are:
Parameter | Default value | Description |
---|---|---|
min-tau | "0.5" | string specifying the cutoff for the absolute value of the tau correlation coefficient (min-tau) |
max-p | "0.05" | string specifying the cutoff for the p-value (max-p) |
The output values are:
- the text field variable values in the selection matching the input arguments to the trend transformer
- tau: the absolute value tau correlation coefficient
- direction: the direction of the trend (negative: "-" or positive: "+")
- p: the p-value (significance) of the trend
- z: the z-value (standard score) of the trend
The outputs can be referred to by means of aliasing.
In this example, we want to return any trending user inputs that are significant at the 0.05 level and that have a medium sized trend effect, alongside the tau value:
tql
1lu t.e.userInput, tau :
2 t.e.type == "request",
3 trend (max-p="0.05", min-tau="0.3") t.e.userInput tau
4
This next example returns all user inputs alongside their tau value, direction of trend (if any), p-value, and z-value, ordered by tau in ascending order:
tql
1lu t.e.userInput, tau, direction, p, z :
2 t.e.type == "request",
3 trend (max-p="1", min-tau="0") t.e.userInput (tau, direction, p, z)
4 tau
5
Meta parameters
Meta parameters are optional parameters that work with all commands. They generally affect the behavior of TQL, rather than operating directly on the data.
order by
The order by meta parameter orders the output data according to a mandatory variable name argument and an optional argument specifying the direction of the ordering.
Ordering is ascending by default, which can also be stated using the expression asc, but can also explicitly be set to descending using desc. For string values, ordering is alphabetical.
The examples below both order user inputs bottom up, i.e., from less frequent first to most frequent:
tql
1d e.userInput : e.type == "request" count
2
tql
1d e.userInput : e.type == "request" count
2
In contrast, the next example orders user inputs top to bottom, i.e., most frequent first:
tql
1d e.userInput : e.type == "request" count
2
limit
The limit meta parameter takes an integer argument specifying a cap on the number of results returned. The meta parameter is specified with the TQL query. The example below returns the 10 first results of the query:
tql
1lu e.userInput : type == "request" 10
2
You can also add the modifier soft to the limit meta parameter. When doing this the query will return as many distinct results as is specified by the limit parameter plus any existing duplicates. It can only be used together with order by, as shown in the example below:
tql
1la e.userInput : e.type == "request"
2 10 soft e.userInput
3
sample
The sample meta parameter takes an integer argument specifying how many sessions to randomly sample and return results from. The example below shows how to return results from a sample of 100 sessions:
tql
1 100 la e.userInput
2
Examples combining several constraints and transformers
The example below lists all unique user inputs that ended up in the Safetynet flow. Note that, by specifying that the transaction must have an event e of type request, we ensure that there will also be another event e2 present in the same transaction having the property fname (flow name). Also, the regular expression alternation pattern [S|s] allows the constraint to match flow names written either with lower or upper case "s".
tql
1lu t.e.userInput :
2 t.e.type == "request",
3 t.e2.fname ~= ".*[S|s]afetynet.*"
4
The next example counts the number of times that each of inputs in the example above occurs and list the top 10 inputs that are most frequent. Note that the operator order by used in this query orders the results in ascending order (the default value), whereas the operator limit picks the top 10 results (after being ordered):
tql
1d t.e.userInput :
2 t.e.type == "request",
3 t.e2.fname ~= ".*[S|s]afetynet.*"
4 count 10
5
This example counts the number of times a user input occurs per day, and orders the results by date:
tql
1d date, t.e.userInput : t.e.type == "request",
2 t.e2.fname ~= ".*[S|s]afetynet.*",
3 catd(model="date") s.beginTime date
4 date
5
The example below shows how to combine the matches-condition, which uses Teneo solutions to match Teneo Inquire data and Language Object expressions) with other constraints. For instance, find all Safetynet inputs that match the Language Object %INSULTS.LIST in the English TLR:
tql
1lu t.e.userInput : t.e.type == "request",
2 t.e2.fname ~= ".*[S|s]afetynet.*",
3 t.e.userInput matches-condition (
4 solution = "en_tlr",
5 condition ="%INSULTS.LIST" )
6
This example lists trending inputs that are matched by the Safetynet only:
tql
1lu t.e.userInput, tau, direction, p, z :
2 t.e.type == "request",
3 t.e2.fname ~= ".*[S|s]afetynet.*",
4 trend(min-tau="0", max-p="0.05") t.e.userInput (tau, direction, p, z)
5 tau
6
Other TQL commands
Sub queries
TQL supports sub queries, where a complete TQL query can be nested and its results can be accessed in the selection part of a superordinate TQL query. The schematic form of the sub query syntax is:
tql
1x = @( TQL query )
2
The example below lists all session IDs from a sub query that results in a unique list of session IDs from those sessions with a user input containing the word "good":
tql
1la result.id :
2result = @( lu s.id id, t.e.userInput :
3t.e.type == "request",
4t.e.userInput ~= ".*good.*" )
5
Save and Source
Teneo Inquire supports two ways to save and export query results data: from the Teneo Studio frontend and using the save TQL command.
save stores query results using name as the data results identifier. When results have been saved, then the source command can retrieve those results using the parameter name and use them as the source data for another query.
For instance, the query below saves the results of the distribute command using the name myData:
tql
1save (name="myData") d date, t.e.userInput :
2 t.e.type == "request",
3 t.e2.fname ~= ".*[S|s]afetynet.*",
4 catd(model="date") s.beginTime
5 date date
6
Note that the data saved using the above query contains two properties: the date and the user input. To make use of that data, you need to use the source command in combination with another TQL command.
The example shown below retrieves and distributes the user input:
tql
1d (source="myData") t.e.userInput
2
Meanwhile, the next example retrieves and distributes both the user input and the date:
tql
1d (source="myData") date, t.e.userInput
2
Aggregate functions
TQL supports aggregation functions on saved data or sub queries.
The aggregate functions are listed below:
Command | Returns |
---|---|
min | minimum value |
max | maximum value |
mean | arithmetic mean |
sum | sum of values |
num | number of observations |
The first example below returns the mean of the distribution of counts of user inputs per day from a sub query, while the second example returns the number of observations for a given date from a saved data set named "myData":
tql
1lu mean x.count meanCount :
2 x=@( d date, t.e.userInput :
3 t.e.type=="request", catd(model="date") s.beginTime date)
4
tql
1la (a="myData") num count, date : date == "2015-04-21"
2
Augmenters
Teneo Inquire Augmenters are applied to log data, either during import or once it has been imported, to enrich or summarize it in a format that can be later accessed by standard queries.
There are two types of Augmenters:
- Adorners add new data properties to log data in a format that can be used along original properties;
- Aggregators are a pre-calculation of sums of complex queries, which may have high computational costs in real time.
This section describes how to perform TQL operations with Augmenters. Note that Augmenters cannot be created using the Query interface in Studio, but the Data Manager Frontend.
Adorners
The adorn command adds new properties to sessions, transactions and events to facilitate simpler and faster queries. Adorner properties have the following syntax: <LEVEL.a.PREFIX:name>, where
- LEVEL can refer to session s, transaction t, or event e
- a stands for adorner
- PREFIX is the data type, and
- name (following the colon) is the adorner name chosen at creation time.
The example below adorns sessions with the property issueComplete, a boolean variable set to true where the meta data variable ISSUE_INFORMATION contains the string "Complete":
tql
1adorn s.a.b:issueComplete = true :
2 exists t.e.md:ISSUE_INFORMATION,
3 t.e.md:ISSUE_INFORMATION ~= ".*Complete.*"
4
Then, the issueComplete adornment can be queried the same way as any other property. The first example below shows how to use it in the selection construct to obtain all session IDs that were completed, while the second example shows how to use a as a constraint to retrieve all user inputs where the session was completed:
tql
1la s.id, s.a.b:issueComplete
2
tql
1la s.t.e.userInput:
2 exists s.a.b:issueComplete,
3 s.t.e.type == "request"
4
Aggregators
The aggregate command computes sums values of properties, grouped by date. Aggregations are stored aside from the rest of the data, so they cannot be queried along with them in a single query. Aggregations have a name (provided in the Log Data Manager frontend) and can generate one or more <key> along three additional properties: date, week, and count. The <key> property holds the list of items that matched the TQL query, count has the size of the items list, and date and week take the session date and week values.
The example below creates an aggregator named dailySafetynet with a single key dailySN holding the number of inputs that fall into a safetynet trigger per day:
tql
1aggregate s:dailySN = t.id :
2 t.e1.type == "request",
3 t.e2.fname ~= ".*[s|S]afetynet.*"
4
The syntax to query aggregated values takes the aggregator name as a mandatory parameter, and any of the other properties generated by the aggregator. The first example below shows how to retrieve all the data stored by the aggregator, grouped by date by default:
tql
1la (a= "SNHits per day") date, week, count, SNHits
2
The second example shows how to obtain the same grouped by week:
tql
1d (a= "SNHits per day") week, count
2
The third example obtains the total sum of the count variable:
tql
1ca (a= "SNHits per day") count
2
To count the total number of user inputs containing the string "hello":
tql
1ca t.e.userInput : t.e.userInput ~= ".*hello.*"
2
Count Unique
The countUnique function counts all unique values matching the query and returns an integer.
To count all unique user inputs, excluding duplicates:
tql
1cu t.e.userInput
2
To count the number of dialogs, i.e. of unique sessions:
tql
1cu s.id
2
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.
The example below counts the number of occurrences for each unique user input (including empty values).
tql
1d t.e.userInput
2
Example output:
List All
The listAll function retrieves all values matching the query and returns a list.
The example below lists all user inputs, including duplicates.
tql
1la t.e.userInput
2
Example output:
List Unique
The listUnique function retrieves all unique values matching the query and returns a list.
List all unique session IDs:
tql
1lu s.id
2
List all user inputs, excluding duplicates:
tql
1lu t.e.userInput
2
Example output:
Selection
The selection specifies what data the TQL query returns. Variable names in the selection are re-used as column headers in the output. Queries composed of a selection with no constraints retrieve all existing matching items in the data. In contrast, constraints in the TQL query are projected onto the selection, which may result in null or empty results if a constraint is not applicable to a variable in the selection, e.g. due to values belonging to different events.
Handling null results (! operator)
Due to the nature of the Teneo Platform and the large amount of data fields that exist in a solution, TQL results often come with lots of null and undefined values - for example, the query la e.userInput returns empty rows for all events not having the e.userInput property, which are the majority of them. This behavior slows down queries by producing a lot of meaningless results.
Therefore, in order to obtain more convenient query results and enhance the overall performance, Inquire adds, by default, exists-like constraints for every item in the selection, which filters fields with data and removes null and undefined results.
Therefore, the queries listed below return equivalent results:
tql
1la s.id, e.userInput
2
tql
1la s.id, e.userInput : exists e.userInput
2
tql
1la s.id, !e.userInput : exists e.userInput
2
Retrieving null results
Nonetheless, since certain use cases may require seeing undefined and null results, a short-hand syntax has also been introduced that allows to not add exists-like constraints. By using the exclusion operator !, query results also include null results.
Please note that if both exists and ! appear in the same query, exists prevails.
Aliases
TQL supports aliasing using the as notation.
tql
1lu t.e.userInput inputs
2
This example produces the result:
Aliasing can also be used as a shorthand for referring to outputs from transformers (see Transformers section below). In the Trend example below, aliasing is used to access and display multiple elements of the result as specified in the selection:
tql
1lu t.e.userInput, tau, direction, p, z :
2 trend(min-tau="0.0", max-p="0.05") t.e.userInput (tau, direction, p, z)
3
Example output:
Note: Inquire supports unique names only. This means that if two variables are assigned the same alias, only the last assignment is kept. In the example below, the result will be a single column containing the transaction time values. The session-level value from s.beginTime is not displayed.
tql
1lu s.beginTime time, t.time time
2
This behavior also applies to the variable name count, which is automatically created by the distribute command; that is, only one variable called count can exist in the same output and only the last assigned value will be kept.
Constraints
Constraints are used to specify conditions that sessions, transactions, and events properties must fulfill. They are separated from the selection by the colon symbol (:) and consist of multiple terms, separated by a comma. The relation between a property value and its condition is specified by means of one of eight operators below.
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 | x exists | no | any | any |
notexists | x does not exist | no | any | Any |
>, <, >=, <= | greater than x, less than x, greater or equal than x, less or equal than x | 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 it exists.
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).
The example below, which retrieves a list of user inputs, consists of a constraint with three terms specifying that the user session must have been initiated in March 2015, one of its events must have raised a flow located in the "Safetynet" folder, and the event that contains the userInput and the event indicating the folder name are related in the same transaction:
tql
1lu t.e1.userInput :
2 s.beginTime == "2015-03",
3 t.e2.folder ~= ".*?[S|s]afetynet.*?",
4 t.e1-{pathType=="flow-trigger"}>t.e2
5
Set-constraints
Set constraints allow a condition to be evaluated against more than one possible property value. Set constraints are specified by means of one of the constraints operators above, the reserved word in, and possible values enclosed by curly brackets {}.
The example below will return user inputs that consist of only lower case "hi" or lower case "hello":
tql
1la t.e.userInput :
2 t.e.userInput == in {"hi", "hello"}
3
This next example will return user inputs for sessions that were initiated in the range from January 2021 to March 2021:
tql
1la t.e.userInput :
2 s.beginTime == in {"2021-01".."2021-03"}
3
Meanwhile, this example covers all of 2021:
tql
1la t.e.userInput :
2 s.beginTime == in {"2021-01".."2021-12"}
3
It is also possible to put constraints on the hours and minutes as in the example below, which finds inputs for sessions initiated from midnight to 1 a.m.:
tql
1la t.e.userInput :
2 s.beginTime == in {"2021-01-01T00:00".."2021-01-01T01:00"}
3
Skip-constraints
The skip-to syntax specifies the order of precedence among transitions or events by setting out a starting point and an end point, where the end point has some property. The syntax (in pseudo-code) is schematically as follows:
tql
1start -{end point constraints}> end point
2end point <{end point constraints}- start
3
The starting and end points can be transactions or events (not necessarily in the same transaction). The constraints enclosed by {} specify the constraints the end points must fulfill. The direction of the arrow specifies whether the constraint is skipping forward or backward in the session.
The example query below lists non-empty user inputs alongside non-empty VA responses immediately following them:
tql
1lu s.id, t1.e.userInput, t2.e.answerText :
2 t1.e.type == "request",
3 t1.e -{type=="response", answerText~=".+"}> t2.e,
4 t1.e.userInput ~= ".+"
5
The second example query lists non-empty user inputs alongside non-empty VA responses immediately preceding them:
tql
1lu s.id, t1.e.answerText, t2.e.userInput :
2 t2.e.type == "request",
3 t2.e.userInput ~= ".+",
4 t1.e <{type=="response", answerText~=".+"}- t2.e
5
Regular expressions
TQL supports a subset of full Perl-style regular expressions, essentially the same subset supported by Elasticsearch. Regular expressions in TQL are by definition anchored, meaning that any regex must match the entire string from beginning to end. Therefore, the conventional anchoring symbols ^ and $ are not needed.
The table below summarizes the allowed regex symbols:
Operator/Symbol | Explanation |
---|---|
. ? + * { } [ ] ( ) " \ | | Reserved symbols |
. | Any character |
? | Zero or one time |
+ | One or more times |
* | Zero or more times |
{1,3} | Min / max number of matches |
() | Grouping |
| | Alternation |
[a-z], [A-Z], [^a] | Character classes (including negation) |
\d | Any digit |
\w | Any word character |
\W | Any non-word character |
\s | A space character |
\S | A non-space character |
\\b | A word boundary |
\\t | A tabulator character |
\\n | A newline character |
\\r | A carriage return character |
Regular expressions are supported for fields stored as strings only (not for numeric values or analyzed text). Property values are match against regular expression via the ~= (matches) and !~ (does not match) operators. The !~ operator evaluates to true either when the regex does not match or when the property is not present.
The first example below will identify user inputs that contain the words "Hello" or "hello", whereas the second will identify all non-empty inputs.
tql
1lu t.e.userInput : t.e.userInput ~= ".*\\b[H|h]ello.*\\b"
2
tql
1lu t.e.userInput : t.e.userInput ~= ".+"
2
Order of constraints
In the evaluation of a TQL query, the TQL parser transforms the TQL expression into a directed graph that guarantees that each part of the query expression is evaluated in the correct order. Therefore, the order of constraints does not matter.
For example, the two queries below return the same results:
tql
1d e.userInput :
2 s.beginTime == "2015-03",
3 e.userInput ~= ".*hello.*"
4
tql
1d e.userInput :
2 e.userInput ~= ".*hello.*",
3 s.beginTime == "2015-03"
4
Syntax and operators for diverse data types
Inquire holds diverse type of data, namely: string, integers, floats, dates, boolean, arrays, and maps. The following table summarizes the operators that can be used with each of them.
Data type | Example | Operators | Remarks |
---|---|---|---|
String | "foo", "bar" | ==, !=, | A quote sign followed by anything and ending with the same quote sign. The valid quote symbol is " |
Date | "2018-01-01", "2018-01", "2018" | ==, !=, exists, notexists, >, <, >=, <= | A string with an ISO-8601 formatted date or date time. This includes the following java Datetime classes: Date, Instant, LocalDate, LocalDateTime, OffsetDateTime, Time, UtilDate. |
Integer | 1, 3094, -5 | ==, !=, exists, notexists, >, <, >=, <= | An Integer |
Double/Float | 0.42, 1.5 | ==, !=, exists, notexists, >, <, >=, <= | A number with a '.' inside. |
Array | [0,1,2] | ~~, !~, exists, notexists, ==, != | It is possible to check if the array contains a particular value, or if it is exactly the same as a given array. |
Map Object | {name:"john", surname:"doe"} | ==, !=, ~=, !, exists, notexists | Maps (and objects in general) are stored as json strings. It is possible to check if the map contains a particular value by using regular expressions, or if it is exactly the same as a given map. |
Syntax for attributes with inner spaces
In general terms, querying Teneo Studio metadata variables follows standard TQL syntax rules, e.g.:
tql
1la t.e.userInput: t.md:CATEGORY
2
However, metadata name can contain a white space. In this case, the name in the query should be written using the formulas ['<metadata name>'] or ["<metadata name>"], as in:
tql
1la t.e.userInput: t.md:['CURRENT PAGE']=="http://mysite.com"
2
tql
1la t.e.userInput: t.md:["CURRENT PAGE"]==http://mysite.com
2
Transformers
Transformers are a group of TQL functions that take a variable value, execute some data manipulation, and return one or more new data values. The returned value(s) and type of data manipulation differ depending on the transformer. Transformers are used as part of a constraint in the query, and the transformed output can be returned in the selection via aliasing.
With transformers, the Teneo Inquire API may throw a null pointer exception if the type of the variable to be transformed is not correctly specified.
Dates
The catd transformer can be applied to a date field and can be used to transform its time stamp value into other date formats or to group query results by time at different levels of granularity. It accepts the four parameters below, and at least either model or pattern are mandatory.
Parameters and values are listed below:
Parameter | Default value | Possible values | Description |
---|---|---|---|
model | none | "date", "day-of-week", "month", "quarter" | A model for grouping results by time at different levels of granularity |
pattern | none | "yyyy-MM-dd", "kk", "yyyy-'w'ww", etc. | A date format string for time and date output. See Java manual for all possible values. |
locale | "en" | as for Java Locale | A locale for formatting time and date output |
timezone | "UTC" | as described by Joda-Time's DateTimeZone class | A timezone id. Can be an offset relative to UTC, e.g. "-05:00" |
The example below transforms the full time stamp as stored in Teneo Inquire into a conventional year-month-day string (and is equivalent to calling pattern="yyyy-MM-dd"). The second example counts the number of sessions per weekday and the thirds example shows how to use relative time zone offsets to obtain results according to another time zone.
tql
1lu date :
2catd(model="date") s.beginTime date
3
tql
1d day :
2catd(model="day-of-week") s.beginTime day
3
tql
1d date :
2catd(model="date", timezone="-05:00") s.beginTime date
3
Trends
The trend transformer can be applied to a text field variable and transforms its values into a list of trending items. The trend algorithm is based on the Mann-Kendall non-parametric trend test, and the input arguments are:
Parameter | Default value | Description |
---|---|---|
min-tau | "0.5" | string specifying the cutoff for the absolute value of the tau correlation coefficient (min-tau) |
max-p | "0.05" | string specifying the cutoff for the p-value (max-p) |
The output values are:
- the text field variable values in the selection matching the input arguments to the trend transformer
- tau: the absolute value tau correlation coefficient
- direction: the direction of the trend (negative: "-" or positive: "+")
- p: the p-value (significance) of the trend
- z: the z-value (standard score) of the trend
The outputs can be referred to by means of aliasing.
In this example, we want to return any trending user inputs that are significant at the 0.05 level and that have a medium sized trend effect, alongside the tau value:
tql
1lu t.e.userInput, tau :
2 t.e.type == "request",
3 trend (max-p="0.05", min-tau="0.3") t.e.userInput tau
4
This next example returns all user inputs alongside their tau value, direction of trend (if any), p-value, and z-value, ordered by tau in ascending order:
tql
1lu t.e.userInput, tau, direction, p, z :
2 t.e.type == "request",
3 trend (max-p="1", min-tau="0") t.e.userInput (tau, direction, p, z)
4 tau
5
Meta parameters
Meta parameters are optional parameters that work with all commands. They generally affect the behavior of TQL, rather than operating directly on the data.
order by
The order by meta parameter orders the output data according to a mandatory variable name argument and an optional argument specifying the direction of the ordering.
Ordering is ascending by default, which can also be stated using the expression asc, but can also explicitly be set to descending using desc. For string values, ordering is alphabetical.
The examples below both order user inputs bottom up, i.e., from less frequent first to most frequent:
tql
1d e.userInput : e.type == "request" count
2
tql
1d e.userInput : e.type == "request" count
2
In contrast, the next example orders user inputs top to bottom, i.e., most frequent first:
tql
1d e.userInput : e.type == "request" count
2
limit
The limit meta parameter takes an integer argument specifying a cap on the number of results returned. The meta parameter is specified with the TQL query. The example below returns the 10 first results of the query:
tql
1lu e.userInput : type == "request" 10
2
You can also add the modifier soft to the limit meta parameter. When doing this the query will return as many distinct results as is specified by the limit parameter plus any existing duplicates. It can only be used together with order by, as shown in the example below:
tql
1la e.userInput : e.type == "request"
2 10 soft e.userInput
3
sample
The sample meta parameter takes an integer argument specifying how many sessions to randomly sample and return results from. The example below shows how to return results from a sample of 100 sessions:
tql
1 100 la e.userInput
2
Examples combining several constraints and transformers
The example below lists all unique user inputs that ended up in the Safetynet flow. Note that, by specifying that the transaction must have an event e of type request, we ensure that there will also be another event e2 present in the same transaction having the property fname (flow name). Also, the regular expression alternation pattern [S|s] allows the constraint to match flow names written either with lower or upper case "s".
tql
1lu t.e.userInput :
2 t.e.type == "request",
3 t.e2.fname ~= ".*[S|s]afetynet.*"
4
The next example counts the number of times that each of inputs in the example above occurs and list the top 10 inputs that are most frequent. Note that the operator order by used in this query orders the results in ascending order (the default value), whereas the operator limit picks the top 10 results (after being ordered):
tql
1d t.e.userInput :
2 t.e.type == "request",
3 t.e2.fname ~= ".*[S|s]afetynet.*"
4 count 10
5
This example counts the number of times a user input occurs per day, and orders the results by date:
tqltql
1d date, t.e.userInput : t.e.type == "request",
2 t.e2.fname ~= ".*[S|s]afetynet.*",
3 catd(model="date") s.beginTime as date
4order by date asc
5
The example below shows how to combine the matches-condition, which uses Teneo solutions to match Teneo Inquire data and Language Object expressions) with other constraints. For instance, find all Safetynet inputs that match the Language Object %INSULTS.LIST in the English TLR:
tql
1lu t.e.userInput : t.e.type == "request",
2 t.e2.fname ~= ".*[S|s]afetynet.*",
3 t.e.userInput matches-condition (
4 solution = "en_tlr",
5 condition ="%INSULTS.LIST" )
6
This example lists trending inputs that are matched by the Safetynet only:
tql
1lu t.e.userInput, tau, direction, p, z :
2 t.e.type == "request",
3 t.e2.fname ~= ".*[S|s]afetynet.*",
4 trend(min-tau="0", max-p="0.05") t.e.userInput (tau, direction, p, z)
5 tau
6
Other TQL commands
Sub queries
TQL supports sub queries, where a complete TQL query can be nested and its results can be accessed in the selection part of a superordinate TQL query. The schematic form of the sub query syntax is:
tql
1x = @( TQL query )
2
The example below lists all session IDs from a sub query that results in a unique list of session IDs from those sessions with a user input containing the word "good":
tql
1la result.id :
2result = @( lu s.id id, t.e.userInput :
3t.e.type == "request",
4t.e.userInput ~= ".*good.*" )
5
Save and Source
Teneo Inquire supports two ways to save and export query results data: from the Teneo Studio frontend and using the save TQL command.
save stores query results using name as the data results identifier. When results have been saved, then the source command can retrieve those results using the parameter name and use them as the source data for another query.
For instance, the query below saves the results of the distribute command using the name myData:
tql
1save (name="myData") d date, t.e.userInput :
2 t.e.type == "request",
3 t.e2.fname ~= ".*[S|s]afetynet.*",
4 catd(model="date") s.beginTime
5 date date
6
Note that the data saved using the above query contains two properties: the date and the user input. To make use of that data, you need to use the source command in combination with another TQL command.
The example shown below retrieves and distributes the user input:
tql
1d (source="myData") t.e.userInput
2
Meanwhile, the next example retrieves and distributes both the user input and the date:
tql
1d (source="myData") date, t.e.userInput
2
Aggregate functions
TQL supports aggregation functions on saved data or sub queries.
The aggregate functions are listed below:
Command | Returns |
---|---|
min | minimum value |
max | maximum value |
mean | arithmetic mean |
sum | sum of values |
num | number of observations |
The first example below returns the mean of the distribution of counts of user inputs per day from a sub query, while the second example returns the number of observations for a given date from a saved data set named "myData":
tql
1lu mean x.count meanCount :
2 x=@( d date, t.e.userInput :
3 t.e.type=="request", catd(model="date") s.beginTime date)
4
tql
1la (a="myData") num count, date : date == "2015-04-21"
2
Augmenters
Teneo Inquire Augmenters are applied to log data, either during import or once it has been imported, to enrich or summarize it in a format that can be later accessed by standard queries.
There are two types of Augmenters:
- Adorners add new data properties to log data in a format that can be used along original properties;
- Aggregators are a pre-calculation of sums of complex queries, which may have high computational costs in real time.
This section describes how to perform TQL operations with Augmenters. Note that Augmenters cannot be created using the Query interface in Studio, but the Data Manager Frontend.
Adorners
The adorn command adds new properties to sessions, transactions and events to facilitate simpler and faster queries. Adorner properties have the following syntax: <LEVEL.a.PREFIX:name>, where
- LEVEL can refer to session s, transaction t, or event e
- a stands for adorner
- PREFIX is the data type, and
- name (following the colon) is the adorner name chosen at creation time.
The example below adorns sessions with the property issueComplete, a boolean variable set to true where the meta data variable ISSUE_INFORMATION contains the string "Complete":
tql
1adorn s.a.b:issueComplete = true :
2 exists t.e.md:ISSUE_INFORMATION,
3 t.e.md:ISSUE_INFORMATION ~= ".*Complete.*"
4
Then, the issueComplete adornment can be queried the same way as any other property. The first example below shows how to use it in the selection construct to obtain all session IDs that were completed, while the second example shows how to use a as a constraint to retrieve all user inputs where the session was completed:
tql
1la s.id, s.a.b:issueComplete
2
tql
1la s.t.e.userInput:
2 exists s.a.b:issueComplete,
3 s.t.e.type == "request"
4
Aggregators
The aggregate command computes sums over values of properties, grouped by date. Aggregations are stored aside the rest of the data, so they cannot be queried along them in a single query. Aggregations have a name (provided in the Log Data Manager frontend) and can generate one or more <key> along three additional properties: date, week, and count. The <key> property holds the list of items that matched the TQL query, count has the size of the items list, and date and week take the session date and week values.
The example below creates an aggregator named dailySafetynet with a single key dailySN holding the number of inputs that fall into a safetynet trigger per day:
tql
1aggregate s:dailySN = t.id :
2 t.e1.type == "request",
3 t.e2.fname ~= ".*[s|S]afetynet.*"
4
The syntax to query aggregated values takes the aggregator name as a mandatory parameter, and any of the other properties generated by the aggregator. The first example below shows how to retrieve all the data stored by the aggregator, grouped by date by default. The second example shows how to obtain the same grouped by week, and the third example obtains the total sum of the count variable:
tql
1la (a= "SNHits per day") date, week, count, SNHits
2
tql
1d (a= "SNHits per day") week, count
2
tql
1ca (a= "SNHits per day") count
2