ADQL Reference:

Overview

ADQL uses two primary categories of expressions in ADQL queries:  

  • field expressions used in the SELECT clause
  • condition expressions used in the WHERE clause

To build an expression, you can use field names, functions, numbers, and operators as indicated in the syntax described below.

 

Syntax of field_expression (SELECT clause)

  • {field_name | function | numeric_literal}
  • (expression) - surround the expression with parentheses to denote order of operation in the query
  • math expressions such as:
    • unary_operator expression
    • expression binary_operator expression
  • A condition_expression such as [NOT] expression COMPARISON_OPERATOR value can also be an alias used in a field_expression in the SELECT clause. 

    For example: SELECT field_name AS myField... WHERE myField > 2

Syntax of condition_expression (WHERE clause)

 

The field expression syntax in the SELECT clause can also contain functions. The syntax of a function_expression is: 

function(field_name, arguments, ...) 
Syntax elementDescription
field_name or numeric_field_name

The name of a field in the specified event type. Field names require back quotes when the name contains spaces or special characters. For example: the field name, `full name`, is surrounded by back quotes because it contains a space. See the section "Special Characters" for the full list.

Use the Events Service internal names in your ADQL queries. The list of default fields and their internal names are described under ADQL Data.

functionSee Analytics Functions.
numeric_literal

A sequence of digits, optionally including a decimal point ". " or sign {+ | -}.  Example: 2, -4, 3.14

unary_operatorThe - unary operator negates the value of the operand.
binary_operatorArithmetic operators: {*, /, %, +, -}. See Math Expressions.
COMPARISON_OPERATORA set of operators that compare values. See Comparison Operators for the full list.
LOGICAL_OPERATORSee Logical Operators.
value

The syntax for a value =  {numeric_literal | string | boolean}. Values are text, numbers, dates or boolean values used to compare with the value in the specified field_name. The data type of the value must match the type of the specified field.

Text strings require quotes. Numbers, dates, boolean values (true or false) and null do not need quotes. Quotes can be either single or double quotes.

Handling Fields Containing Spaces or Special Characters

If the Events Service internal name contains a special character, then the name must be surrounded by back quotes when it is used in a field expression in the WHERE clause.

For a field name such as segments.httpData.headers.User-Agent, the ADQL query must use segments.httpData.headers.`User-Agent`. For example:

SELECT segments.httpData.headers.`User-Agent`, count(segments.httpData.headers.`User-Agent`) FROM transactions WHERE segments.httpData.url = 'http://localhost:29990/packages'

Field names added using data collectors are the most likely instances where spaces or other special characters might be used in naming.

 

Special Characters

Special characters in ADQL are the following:

  • equals : '='
  • not equals : '<>' or '!='
  • less than : '<'
  • less than or equals : '<='
  • greater than : '>'
  • greater than or equals : '>='
  • plus: '+'
  • minus: '-'
  • asterisk: '*'
  • slash: '/'
  • percent: '%'
  • concatenation: '||'
  • question mark: '?'
  • open parens: '('
  • open square bracket/brace: '['
  • close parens: ')'
  • close square bracket/brace: ']'