Make use of sub queries

Sub queries in TQL enable you to take your queries to the next level. A sub query is a complete TQL query embedded in a main query. In the selection part of the main query you can access the results of the sub query, which effectively adds an extra level of processing you would otherwise not have. We introduced sub queries briefly on the page about constraints. In another section we showed you how to add a safetynet counter to your solution. Let's revisit that example to show how a sub query makes it possible to learn more about safetynet activation across sessions.

Compute aggregate information about a safetynet counter

Referring back to the safetynet counter we added, we can run a simple query to show per session, the total number of times the safetynet became active:

la s.sv:n:safetyNetCounter

The result of the query delivers raw information that as such is not very helpful: safetynet query with raw data

For example, you may want to know the average number of safetynet hits across sessions or other aggregate information. Of course no one wants to compute this by hand. This is where a subquery can help us, combined with aggregate functions available in TQL. We'll embed our simple query into a larger context to give us the mean, minimum and maximum value of the safetynet counter. This is a query to achieve that:

lu  mean result.count as average, 
    min result.count as minValue, 
    max result.count as maxValue:
result = @(la s.sv:n:safetyNetCounter as count)

The main query is able to look at the results of the sub query and process information about it:

safety net averages

Additional sub query examples

Here are some additional examples showing how you can use sub queries.

This query shows you the mean number of sessions per day in a particular month:

lu mean x.count as meanCount: x=@(d date: s.beginTime == '2016-04' , catd(model="date") s.beginTime as date order by count)

If your bot uses multiple channels and you require usage information about each of the channels, you can use a query similar to this, based on the channel parameter used:

d result.date as date, result.channel as channel :
    result = @( lu s.id as id, t.e.requestParameters.channel as channel, date:
            catd(model="date") s.beginTime as date)
    order by date desc

Multiple sub queries

One strength of sub queries is that you can combine multiple sub queries into a single query, and then present results from each of them.

This example shows relative occurrences of a particular product name in a single query. You would otherwise have to run each query separately to obtain the complete results:

la sum iphone.count, sum galaxy.count:

iphone = @(ca t.id as count :
      t.e.type == "request",
      t.e.userInput ~= ".*iphone.*"),

galaxy = @(ca t.id as count :
      t.e.type == "request",
      t.e.userInput ~= ".*galaxy.*")

It generates the following result:

sub query results

Was this page helpful?