Link Search Menu Expand Document

SQL functions

Use the alphabetical list in the navigation pane to find the syntax for commands that you already know.

Use the functional list below to find commands for a specific task area that you’re working in.

  • Aggregate array functions
    These functions work on array-typed columns, but instead of being applied row by row, they combine the results of all the arrays belonging to each of the groups defined by the GROUP BY clause.

  • Aggregation functions
    These functions perform a calculation across a set of rows, returning a single value.

  • Array functions
    Used for the manipulation and querying of ARRAY-typed columns, such as transforming and filtering. Includes Lambda functions.

  • Conditional and miscellaneous functions
    These functions include various methods for modifying data types and applying conditional operations.

  • Date and time functions
    Functions for manipulating date and time data types.

  • JSON functions
    These functions extract and transform JSON into Firebolt native types, or JSON sub-objects. They are used either during the ELT process or applied to columns storing JSON objects as plain TEXT.

  • Numeric functions
    Functions for manipulating data types including INT, LONG, DOUBLE, and other numeric types.

  • String functions
    Functions for manipulating string data types

  • Window functions
    These functions perform a calculation across a specified set of table rows.

Aggregate array functions

Aggregate semi-structured functions work globally on all the arrays in a given column expression, instead of a row-by-row application.

At their simplest form (without a GROUP BY clause) - they will provide the result of globally applying the function on all of the elements of the arrays in the column expression specified as their argument. For example, ARRAY_SUM_GLOBAL will return the sum of all the elements in all the array of the given column. ARRAY_MAX_GLOBAL will return the maximum element among all of the elements in all of the arrays in the given column expression.

When combined with a GROUP BY clause, these operations will be performed on all of the arrays in each group.

Aggregation functions

Array functions

Lambda functions

For more information about using Lambda functions, see Manipulating arrays with Lambda functions.

Conditional and miscellaneous functions

Date and time functions

JSON functions

The reference for each JSON function uses common syntax and conventions as outlined below.

JSON pointer expression syntax

The placeholder <json_pointer_expression> indicates where you should use a JSON pointer, which is a way to access specific elements in a JSON document. For a formal specification, see RFC6901.

A JSON pointer starts with a forward slash (/), which denotes the root of the JSON document. This is followed by a sequence of property (key) names or zero-based ordinal numbers separated by slashes. You can specify property names or use ordinal numbers to specify the Nth property or the Nth element of an array.

The tilde (~) and forward slash (/) characters have special meanings and need to be escaped according to the guidelines below:

  • To specify a literal tilde (~), use ~0
  • To specify a literal slash (/), use ~1

For example, consider the JSON document below.

{
    "key": 123,
    "key~with~tilde": 2,
    "key/with/slash": 3,
    "value": {
      "dyid": 987,
      "keywords" : ["insanely","fast","analytics"]
    }
}

With this JSON document, the JSON pointer expressions below evaluate to the results shown.

Pointer Result Notes
/ {
"key": 123,
"key~with~tilde": 2,
"key/with/slash": 3,
"value": {
"dyid": 987,
"keywords" : ["insanely","fast","analytics"]
}
Returns the whole document.
/key 123  
/key~0with~0tilde 2 Indicates the value associated with the key~with~tilde property name.
/key~1with~1slash 3 Indicates the value associated with the key/with/slash property name.
/0 123 Uses an ordinal to indicate the value associated with the key property name. The key property is in the first 0-based position.
/value/keywords/2 analytics Indicates the element “analytics”, which is in the third 0-based position of the array value associated with they keywords property.

Supported type parameters

Some functions accept a data type parameter, indicated in this reference with the <expected_type> placeholder. This parameter specifies the expected type as indicated by <json_pointer_expression>. The <expected_type> is specified using a string literal that corresponds to supported Firebolt SQL data types. The type parameter does not accept all SQL types because the JSON type system has fewer types than SQL and must be one of the following:

  • INT – used for integers as well as JSON boolean.
  • DOUBLE – used for real numbers. It also works with integers. For performance reasons, favor using INT when the values in the JSON document are known integers.
  • TEXT – used for strings.
  • ARRAY(<type>) – indicates an array where <type> is one of INT, DOUBLE, or TEXT.

The following data types are not supported: DATE, DATETIME, FLOAT (for real numbers, use DOUBLE).

JSON common example

Usage examples for JSON functions in this reference are based on the JSON document below, which is indicated using the <json_common_example> placeholder.

{
    "key": 123,
    "value": {
      "dyid": 987,
      "uid": "987654",
      "keywords" : ["insanely","fast","analytics"],
      "tagIdToHits": {
        "map": {
          "1737729": 32,
          "1775582": 35
        }
      },
      "events":[
        {
            "EventId": 547,
            "EventProperties" :
            {
                "UserName":"John Doe",
                "Successful": true
            }
        },
        {
            "EventId": 548,
            "EventProperties" :
            {
                "ProductID":"xy123",
                "items": 2
            }
        }
    ]
    }
}

Numeric functions

String functions

Window functions