BQLFunction

Usage

Using BQL, function calls can have different formats.
For a function with only one argument, it can be called as:

{"function_name": "field_name"}

For functions with more than one argument, the "function" and "args"
keywords must be used:

{
    "function": "function_name",
    "args": [
        "field_name_1",
        "field_name_2"
    ]
}

Handling literal values

Function arguments are generally taken as field names, with few exceptions.
Literal numerical values, boolean values, and NULL are recognized. Literal
string can be specified with:

{"literal": "a string"}

Functions

Mathematical Functions

The Mathematical Functions output a numerical value or null.

• add
  • Input: any number of numerical values
• sub
  • Input: any number of numerical values
• mul
  • Input: any number of numerical values
• div
  • Input: 2 numerical values

Example:

{
    "function": "sub",
    "args": [
        "search_console.period_1.count_clicks",
        "search_console.period_0.count_clicks"
    ]
}

Date Functions

All BQL Date Functions take one input parameter.

• first_day_of_week
  • Input: Date or Datetime
  • Output: Date
• first_day_of_month
  • Input: Date or Datetime
  • Output: Date
• date
  • Input: Datetime
  • Output: Date
• year
  • Input: Date or Datetime
  • Output: Integer
• month
  • Input: Date or Datetime
  • Output: Integer
• year_month
  • Input: Date or Datetime
  • Output: String
• year_week_number_starting_monday
  • Input: Date or Datetime
  • Output: String
• year_week_number_starting_sunday
  • Input: Date or Datetime
  • Output: String

Example:

{"first_day_of_week": "search_console.period_0.date"}

Count Functions

• count
• count_true
• count_false
• count_null
• count_distinct
• count_distinct_approx

Conditional counts: these are shortcuts to applying count_true to the corresponding boolean function.

• count_eq
• count_gt
• count_gte
• count_lt
• count_lte

HTTP Code Functions

• http_code_family
  • Input: Integer
  • Output: String formatted as “2xx”, “3xx”, … or “exx”
• http_code_quality
  • Input: Integer
  • Output: String “good” or “bad”

JSON Functions

BQL supports retrieving any kind of field as a JSON-formatted string.

• to_json_string
  • Input: 1 argument of any type
  • Output: String

Multiple Functions

• first
  • Input: 1 multiple value
  • Output: Type of first value

Boolean Functions

• eq
• ne
• lt
• lte
• gt
• gte
• and
• or
• not
• exists
• not_exists

Logical Functions

• if: returns one of two values according to a boolean condition.

  • input: Boolean, Any, Any
  • output: Any

  The two possible return values must be the same type.

  Example:

{
    "function": "if",
    "args": [
        {
            "function": "exists",
            "args": [
                "segments.pagetype.depth_2"
            ]
        },
        "segments.pagetype.depth_2",
        {"literal": "#empty"}
    ]
}

• if_not_exists: returns the first field’s value if it exists, the second field’s value otherwise.

  • input: Any, Any
  • output Any

  The two possible return values must be the same type.

  This is a shortcut for the previous example:

{
    "function": "if_not_exists",
    "args": [
        "segments.pagetype.depth_2",
        {"literal": "#empty"}
    ]
}

Miscellaneous Functions

• concat: concatenates strings. Any non-existing value results in NULL.

  Example:
  JSON { "function": "concat", "args": [ "segments.pagetype.depth_1", {"literal": "/"}, "segments.pagetype.depth_2" ] }