Row level security rules reference
Learn the operators and functions you can use to create rules for row level security.
ThoughtSpot allows you to create row level security rules using expressions. If an expression evaluates to "true" for a particular row and group combination, that group will be able to see that row. This reference lists the various operators and functions you can use to create rules.
For information on how to use the row level security functions and operators, see About RuleBased Row Level Security.
There is a special variable called ts_groups
, which you can use when creating row level security rules.
It fetches a list of the groups that the currently logged in user belongs to.
For each row, if the expression in the rule evaluates to 'true' for any one of these groups, that row will be shown to the user.
You can also see this list of operators and examples from within the Rule Builder by selecting Rule Assistant.
Conversion functions
These functions can be used to convert data from one data type to another. Conversion to or from date data types is not supported.
Function  Description  Examples 


Returns the input as a boolean data type ( 


Accepts a date represented as an integer or text string, and a second string parameter that can include strptime date formatting elements. Replaces all the valid strptime date formatting elements with their string counterparts and returns the result. Does not accept epoch formatted dates as input. 


Returns the input as a double data type. 


Returns the input as an integer. 


Returns the input as a text string. To convert a date data type to a string data type, specify the date format you want to use. 

Date functions
Function  Description  Examples 


Returns the result of adding the specified number of days to the given date. 


Returns the result of adding the specified number of minutes to the given date/datetime/time. 


Returns the result of adding the specified number of months to the given date. 


Returns the result of adding the specified number of seconds to the given date/ datetime/ time. 


Returns the result of adding the specified number of weeks to the given date. 


Returns the result of adding the specified number of years to the given date. 


Returns the date portion of a given date. 


Returns the number (131) of the day of the month for the given date. 


Returns the number of the day in a quarter for a given date. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result. The default is 'calendar'. 
In the following example, May 1st is the start of the fiscal year.


Returns the number (17) of the day in a week for a given date. Monday is 1, and Sunday is 7. 


Returns the number (1366) of the day in a year from a given date. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result. The default is 'calendar'. 
In the following example, May 1st is the start of the fiscal year.


Returns the day of the week for the given date. 


Subtracts the second date from the first date and returns the result in number of days, rounded down if not exact. 


Subtracts the second date from the first date and returns the result in number of seconds. 


Returns the hour of the day for the given date. 


Returns true if the given date falls on a Saturday or Sunday. 


Returns the number (112) of the month from a given date. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result. The default is 'calendar'. 
In the following example, May 1st is the start of the fiscal year.


Returns the month (13) number for the given date in a quarter. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result. The default is 'calendar'. 
In the following example, May 1st is the start of the fiscal year.


Returns the current timestamp. 


Returns the number (14) of the quarter associated with the given date. You can add an optional second parameter to specify 'fiscal' or 'calendar' dates. The default is 'calendar'. 
In the following example, May 1st is the start of the fiscal year.


Returns MMM yyyy for the first day of the month. Your installation configuration can override this setting so that it returns a different format such as MM/dd/yyyy. Speak with your ThoughtSpot administrator for information on doing this. 


Returns the date for the first day of the quarter for the given date. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result. The default is 'calendar'. 
In the following example, May 1st is the start of the fiscal year.


Returns the date for the first day of the week for the given date. 


Returns the date for the first day of the year for the given date. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result. The default is 'calendar'. 
In the following example, May 1st is the start of the fiscal year.


Returns the time portion of a given date. 


Returns the current date. 


Returns the week number for the given date in a month. 


Returns the week number for the given date in a quarter. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result. The default is 'calendar'. 
In the following example, May 1st is the start of the fiscal year.


Returns the week number for the given date in a year. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result. The default is 'calendar'. 
In the following example, May 1st is the start of the fiscal year.


Returns the year from a given date. You can add an optional second parameter to specify whether a 'fiscal' or 'calendar' year is used to calculate the result. The default is 'calendar'. 
In the following example, May 1st is the start of the fiscal year. Per standard convention, the fiscal year is defined by the yearend date.

Mixed functions
These functions can be used with text and numeric data types.
Function  Description  Examples 


Returns true if the first value is not equal to the second value. 


Returns true if the first value is less than the second value. 


Returns true if the first value is less than or equal to the second value. 


Returns true if the first value is equal to the second value. 


Returns true if the first value is greater than the second value. 


Returns true if the first value is greater than or equal to the second value. 


Returns the larger of the values. 


Returns the smaller of the values. 

Number functions
Function  Description  Examples 


Returns the result of multiplying both numbers. 


Returns the result of adding both numbers. 


Returns the result of subtracting the second number from the first. 


Returns the result of dividing the first number by the second. 


Returns the first number raised to the power of the second. 


Returns the absolute value. 


Returns the inverse cosine in degrees. 


Returns the inverse sine (specified in degrees). 


Returns the inverse tangent in degrees. 


Returns the inverse tangent in degrees. 


Returns the cube root of a number. 


Returns the smallest following integer. 


Returns the cosine of an angle (specified in degrees). 


Returns the cube of a number. 


Returns Euler’s number (~2.718) raised to a power. 


Returns 2 raised to a power. 


Returns the largest previous integer. 


Returns the natural logarithm. 


Returns the logarithm with base 10. 


Returns the logarithm with base 2 (binary logarithm). 


Returns the remainder of first number divided by the second number. 


Returns the first number raised to the power of the second number. 


Returns a random number between 0 and 1. 


Returns the first number rounded to the second number (the default is 1). 


Returns the result of dividing the first number by the second. If the second number is 0, returns 0 instead of NaN (not a number). 


Returns +1 if the number is greater than zero, 1 if less than zero, 0 if zero. 


Returns the sine of an angle (specified in degrees). 


Returns the distance in km between two points on Earth. 


Returns the square of a numeric value. 


Returns the square root. 


Returns the tangent of an angle (specified in degrees). 

Operators
Operator  Description  Examples  


Returns true when both conditions are true, otherwise returns false. 



Conditional operator 



Returns the first value if it is not null, otherwise returns the second value. 
ifnull (cost, 'unknown') 


Returns true if the value is null. 



Returns true if the condition is false, otherwise returns false. 



Returns true when either condition is true, otherwise returns false. 

Text functions
Function  Description  Examples 


Returns two or more values as a concatenated text string. Use single quotes around each literal string, not double quotes. 


Returns true if the first string contains the second string, otherwise returns false. 


Accepts two text strings. Returns the edit distance (minimum number of operations required to transform one string into the other) as an integer. Works with strings under 1023 characters. 


Accepts two text strings and an integer to specify the upper limit cap for the edit distance (minimum number of operations required to transform one string into the other). If the edit distance is less than or equal to the specified cap, returns the edit distance. If it is higher than the cap, returns the cap plus 1. Works with strings under 1023 characters. 


Accepts a document text string and a search text string. Returns true if relevance score (0100) of the search string with respect to the document is greater than or equal to 20. Relevance is based on edit distance, number of words in the query, and length of words in the query which are present in the document. 


Accepts a document text string and a search text string. Returns the relevance score (0100) of the search string with respect to the document. Relevance is based on edit distance, number of words in the query, and length of words in the query which are present in the document. If the two strings are an exact match, returns 100. 


Accepts two text strings. Returns true if they are spelled similarly and false if they are not. Works with strings under 1023 characters. 


Returns the length of the text. 


Returns the numeric position (starting from 0) of the first occurrence of the second string in the first string, or 1 if not found. 


Returns the portion of the given string, beginning at the location specified (starting from 0), and of the given length. 

Variables
These variables can be used in your expressions.
Function  Description  Examples 


Returns a list of all the groups the current logged in user belongs to. For any row, if the expression evaluates to true for any of the groups, the user can see that row. 


Returns the user with the matching name. 
