System (Built-in) Functions #

Returns a string from string1 which extracted with a specified regular expression string2 and a regex match group index integer.

The regex match group index starts from 1 and 0 means matching the whole regex. In addition, the regex match group index should not exceed the number of the defined groups.

E.g. REGEXP_EXTRACT(‘foothebar’, ‘foo(.*?)(bar)’, 2)" returns “bar”.

Returns the specified part from the URL. Valid values for string2 include ‘HOST’, ‘PATH’, ‘QUERY’, ‘REF’, ‘PROTOCOL’, ‘AUTHORITY’, ‘FILE’, and ‘USERINFO’. Returns NULL if any of arguments is NULL.

E.g., parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', ‘HOST’), returns ‘facebook.com’.

Also a value of a particular key in QUERY can be extracted by providing the key as the third argument string3.

E.g., parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', ‘QUERY’, ‘k1’) returns ‘v1’.

Parses an interval string in the form “dd hh:mm:ss.fff” for SQL intervals of milliseconds or “yyyy-mm” for SQL intervals of months. An interval range might be DAY, MINUTE, DAY TO HOUR, or DAY TO SECOND for intervals of milliseconds; YEAR or YEAR TO MONTH for intervals of months.

E.g., INTERVAL ‘10 00:00:00.004’ DAY TO SECOND, INTERVAL ‘10’ DAY, or INTERVAL ‘2-10’ YEAR TO MONTH return intervals.

Returns the current watermark for the given rowtime attribute, or NULL if no common watermark of all upstream operations is available at the current operation in the pipeline. The return type of the function is inferred to match that of the provided rowtime attribute, but with an adjusted precision of 3. For example, if the rowtime attribute is TIMESTAMP_LTZ(9), the function will return TIMESTAMP_LTZ(3).

Note that this function can return NULL, and you may have to consider this case. For example, if you want to filter out late data you can use:

WHERE
  CURRENT_WATERMARK(ts) IS NULL
  OR ts > CURRENT_WATERMARK(ts)

Returns null_replacement if input is NULL; otherwise input is returned.

Compared to COALESCE or CASE WHEN, this function returns a data type that is very specific in terms of nullability. The returned type is the common type of both arguments but only nullable if the null_replacement is nullable.

The function allows to pass nullable columns into a function or table that is declared with a NOT NULL constraint.

E.g., IFNULL(nullable_column, 5) returns never NULL.

Determines whether a JSON string satisfies a given path search criterion.

If the error behavior is omitted, FALSE ON ERROR is assumed as the default.

// TRUE
SELECT JSON_EXISTS('{"a": true}', '$.a');
// FALSE
SELECT JSON_EXISTS('{"a": true}', '$.b');
// TRUE
SELECT JSON_EXISTS('{"a": [{ "b": 1 }]}',
  '$.a[0].b');

// TRUE
SELECT JSON_EXISTS('{"a": true}',
  'strict $.b' TRUE ON ERROR);
// FALSE
SELECT JSON_EXISTS('{"a": true}',
  'strict $.b' FALSE ON ERROR);

Extracts a scalar from a JSON string.

This method searches a JSON string for a given path expression and returns the value if the value at that path is scalar. Non-scalar values cannot be returned. By default, the value is returned as STRING. Using returningType a different type can be chosen, with the following types being supported:

• VARCHAR / STRING
• BOOLEAN
• INTEGER
• DOUBLE

For empty path expressions or errors a behavior can be defined to either return null, raise an error or return a defined default value instead. When omitted, the default is NULL ON EMPTY or NULL ON ERROR, respectively. The default value may be a literal or an expression. If the default value itself raises an error, it falls through to the error behavior for ON EMPTY, and raises an error for ON ERROR.

// STRING: "true"
JSON_VALUE('{"a": true}', '$.a')

// BOOLEAN: true
JSON_VALUE('{"a": true}', '$.a' RETURNING BOOLEAN)

// STRING: "false"
JSON_VALUE('{"a": true}', 'lax $.b'
    DEFAULT FALSE ON EMPTY)

// STRING: "false"
JSON_VALUE('{"a": true}', 'strict $.b'
    DEFAULT FALSE ON ERROR)

Returns a row created from a list of values (value1, value2,…).

The implicit row constructor supports arbitrary expressions as fields but requires at least two fields. The explicit row constructor can deal with an arbitrary number of fields but does not support all kinds of field expressions well currently.