Advanced Trino SQL functions
This section describes how to use advanced functions in Trino SQL. To learn more about Trino SQL, see the official Trino documentation
try and try_cast functions
try(expression)
The try function catches exceptions in the expression and returns NULL for the exception values. Without the try function, an exception in the statement causes an error and the query fails.
You can also use it with the coalesce function to replace NULL values with a specific value. For example, the following converts field a to an integer and returns 0 if the conversion fails
coalesce(try(cast("a" as integer)), 0)
You can also do the type conversion above with the try_cast function. try_cast works the same way as the cast function: both convert a value to another type. The difference is that try_cast returns NULL when the type conversion fails, which keeps the query from failing.
coalesce(try_cast("a" as integer), 0)
Date and time functions
Don't add parentheses when you use current_date, current_time, current_timestamp, localtime, and localtimestamp. Trino doesn't support the syntax with parentheses.
Converting between strings and time
You can add the keyword timestamp directly before a time expression in string format, such as timestamp '2020-01-01 00:00:00', to get the corresponding time
date_parse converts a string to time, and date_format converts time to a string. For both, you pass in the field to convert and the corresponding format. The following examples convert the string $part_date to time and the time #event_time to a string, respectively:
date_parse("$part_date", '%Y-%m-%d')
date_format("#event_time", '%Y-%m-%d %T')
The format of the functions above uses the MySQL format. To use the Java format, use the format_datetime and parse_datetime functions
Time calculation functions
The date_add function shifts a time. unit is the unit and value is the offset. If value is negative, the time is shifted backward
date_add(unit, value, timestamp)
The date_diff function calculates the difference between two times as timestamp2 - timestamp1 and returns an integer in units of unit
date_diff(unit, timestamp1, timestamp2)
For the values of unit in the two functions, see the following table
| Unit | Description |
|---|---|
| millisecond | Millisecond |
| second | Second |
| minute | Minute |
| hour | Hour |
| day | Day |
| week | Week |
| month | Month |
| quarter | Quarter |
| year | Year |
Window functions
Trino supports window functions. Many window functions are very useful. For example, first_value and last_value are well suited to getting the value of the first or last time someone did something within a period.
For example, to get the item each user bought the first time they made a purchase:
SELECT user_id,first_purchase_product FROM
(SELECT user_id,first_value(product_name) over(partition by user_id order by time) AS first_purchase_product FROM log.purchase)
GROUP BY user_id,first_purchase_product
first_value and last_value must be used with the over clause. In the over clause, partition by is similar to group by and groups by the given fields, while order by determines the field to sort by.
JSON parsing
If your reported data or imported historical data contains JSON fields, they are converted to text (strings) when ingested. You can extract and use them in queries.
Converting a string to JSON
json_parse converts a string in valid JSON format to JSON data:
json_parse(JSON '{"abc":[1, 2, 3]}')
Converting JSON to other types
You can use CAST to convert JSON to data of other types. For example, convert the string you just converted to JSON into a MAP:
CAST(json_parse('{"abc":[1, 2, 3]}') AS MAP(varchar,array(integer)))
To convert JSON back to a string, use json_format:
json_format(json_parse('{"abc":[1, 2, 3]}'))
Extracting JSON data directly
In many cases, you may need to extract only part of the data in JSON. In that case, use json_extract_scalar and find the content you need with a json_path expression:
json_extract_scalar(json, json_path)
You can also use json_extract_scalar to extract directly from a JSON string without manually converting it to JSON. For example, to extract the first element of abc:
json_extract_scalar('{"abc":[1, 2, 3]}','$.abc[0]')