menu
Is this helpful?

# Advanced Functions in Trino SQL

This chapter introduces the usage of advanced functions in Trino SQL. For more information about Trino SQL, please visit Trino documentation (opens new window).

# try and try_cast Function

TRY evaluate an expression and handle certain types of errors by returning NULL.

In cases where it is preferable that queries produce NULL or default values instead of failing when corrupt or invalid data is encountered, the TRY function may be useful. To specify default values, the TRY function can be used in conjunction with the COALESCE function.

The following errors are handled by TRY:

  • Division by zero
  • Invalid cast or function argument
  • Numeric value out of range

For example, you can also use coalesce to replace NULL values with a specific value, such as the following, converting field a to an integer, and converting it to 0 if it fails.

coalesce(try(cast("a" as integer)), 0)

The difference between cast and try_cast is that try_cast returns NULL when the type conversion fails, avoiding the query failure.

coalesce(try_cast("a" as integer), 0)

# Time/Date Functions

When using current_date、current_time、current_timestamp、localtime and localtimestamp, do not add parentheses. Please pay attention when using them.

String and Time Conversion

You can directly add the keyword timestamp before the string format time expression, such as timestamp '2020-01-01 00:00:00', and you can directly get the corresponding time.

date_format formats timestamp as a string using format

date_parse("$part_date", '%Y-%m-%d')

date_parse parses string into a timestamp using format

date_format("#event_time", '%Y-%m-%d %T')

The format of the above functions uses the MySQL format. If you need to use the JAVA format, you can use the functions format_datetime and parse_datetime.

Time Calculation Functions

date_add adds an interval value of type unit to timestamp. Subtraction can be performed by using a negative value:

SELECT date_add('second', 86, TIMESTAMP '2020-03-01 00:00:00');
-- 2020-03-01 00:01:26.000
SELECT date_add('hour', 9, TIMESTAMP '2020-03-01 00:00:00');
-- 2020-03-01 09:00:00.000
SELECT date_add('day', -1, TIMESTAMP '2020-03-01 00:00:00 UTC');
-- 2020-02-29 00:00:00.000 UTC

date_diff returns timestamp2 - timestamp1 expressed in terms of unit:

SELECT date_diff('second', TIMESTAMP '2020-03-01 00:00:00', TIMESTAMP '2020-03-02 00:00:00');
-- 86400
SELECT date_diff('hour', TIMESTAMP '2020-03-01 00:00:00 UTC', TIMESTAMP '2020-03-02 00:00:00 UTC');
-- 24
SELECT date_diff('day', DATE '2020-03-01', DATE '2020-03-02');
-- 1
SELECT date_diff('second', TIMESTAMP '2020-06-01 12:30:45.000000000', TIMESTAMP '2020-06-02 12:30:45.123456789');
-- 86400
SELECT date_diff('millisecond', TIMESTAMP '2020-06-01 12:30:45.000000000', TIMESTAMP '2020-06-02 12:30:45.123456789');
-- 86400123

The functions in this section support the following interval units:

Unit Description
millisecond Milliseconds
second Seconds
minute Minutes
hour Hours
day Days
week Weeks
month Months
quarter Quarters of a year
year Years

# Window functions

Window functions perform calculations across rows of the query result. They run after the HAVING clause but before the ORDER BY clause. Invoking a window function requires special syntax using the OVER clause to specify the window. For example, the following query ranks orders for each clerk by price:

SELECT orderkey, clerk, totalprice,
       rank() OVER (PARTITION BY clerk
                    ORDER BY totalprice DESC) AS rnk
FROM orders
ORDER BY clerk, rnk

# JSON

Cast to JSON

The following types can be cast to JSON:

  • BOOLEAN
  • TINYINT
  • SMALLINT
  • INTEGER
  • BIGINT
  • REAL
  • DOUBLE
  • VARCHAR

Additionally, ARRAY, MAP, and ROW types can be cast to JSON when the following requirements are met:

  • ARRAY types can be cast when the element type of the array is one of the supported types.
  • MAP types can be cast when the key type of the map is VARCHAR and the value type of the map is a supported type,
  • ROW types can be cast when every field type of the row is a supported type.

json_parse Returns the JSON value deserialized from the input JSON text.

SELECT json_parse('[1, 2, 3]');   -- JSON '[1,2,3]'
SELECT json_parse('"abc"');       -- JSON '"abc"'

Cast from JSON

Casting to BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, REAL, DOUBLE or VARCHAR is supported. Casting to ARRAY and MAP is supported when the element type of the array is one of the supported types, or when the key type of the map is VARCHAR and value type of the map is one of the supported types.

json_format Returns the JSON text serialized from the input JSON value.

SELECT json_format(JSON '[1, 2, 3]'); -- '[1,2,3]'
SELECT json_format(JSON '"a"');       -- '"a"'

JSON functions

json_extract evaluates the JSONPath-like expression and returns the result as a JSON string:

SELECT json_extract(json, '$.store.book');
SELECT json_extract(json, '$.store[book]');
SELECT json_extract(json, '$.store["book name"]');

json_extract_scalar like json_extract, but returns the result value as a string (as opposed to being encoded as JSON). The value referenced by json_path must be a scalar (boolean, number or string).

SELECT json_extract_scalar('[1, 2, 3]', '$[2]');
SELECT json_extract_scalar(json, '$.store.book[0].author');

← Visualization Module →