Skip to main content

AQL Cheatsheet - Operators

Text Operators​

Description​

A text value, represented as a string of characters. The "text" type can include any characters, including letters, numbers, symbols, and whitespace.

Tags​

Operator, Condition

Operators​

== ; is​

Equal to

Check if product name is Dandelion
products.name == 'Dandelion'
products.name is 'Dandelion'

!= ; is not​

Not equal to

Check if product name is not Rock
products.name != 'Rock'
products.name is not 'Rock'

like​

Match the pattern specified

Check if product name ends with Dan
products.name like '%Dan'

not like​

Not match the pattern specified

Check if product name does not end with Dan
products.name not like '%Dan'

ilike​

Match the pattern specified, case insensitive

Check if product name ends with dan, case insensitive
products.name ilike '%dan'

not ilike​

Match the pattern specified, case insensitive

Check if product name does not end with dan, case insensitive
products.name not ilike '%dan'

is null​

Include if the value is null

Check if product name is null
products.name is null

is not null​

Include if the value is not null

Check if product name is not null
products.name is not null

Learn more​

Text Operators (AQL Doc)

List Operators​

Description​

Check if the field value is in the list.

Tags​

Operator, Condition

Operators​

in​

Include if the value is in the list

Check if product name is in the list
products.name in ['Dandelion', 'Rock']

not in​

Include if the value is not in the list

Check if product name is not in the list
products.name not in ['Dandelion', 'Rock']

Learn more​

List Operators (AQL Doc)

Truefalse Operators​

Description​

A boolean value, represented as either "true" or "false". This is commonly used to represent logical values. For example, it can be used to represent the result of a comparison, the status of a switch, or the answer to a yes/no question.

Tags​

Operator, Condition

Operators​

is​

Equal to

Check if an order is paid
orders.is_paid is true

is not​

Not equal to

Check if an order is not paid
orders.is_paid is not true

is null​

Include if null

Check if the order payment status is unknown
orders.is_paid is null

is not null​

Include if not null

Check if the order payment status is known
orders.is_paid is not null

Learn more​

Truefalse Operators (AQL Doc)

Number Operators​

Description​

A numeric value, represented as an numeric, integer, float, or double. It can be used as dimension or measure type. The number type can include positive and negative values, as well as decimal points.

Tags​

Operator, Condition

Operators​

== ; is​

Equal to

Check if an order item discount is 50%
order_items.discount == 0.5
order_items.discount is 0.5

!= ; is not​

Not equal to

Check if an order item discount is not 100%
order_items.discount != 1
order_items.discount is not 1

>

Greater than

Check if an order item discount is greater than 50%
order_items.discount > 0.5

>=​

Greater than or equal to

Check if an order item discount is at least 50%
order_items.discount >= 0.5

<​

Less than

Check if an order item discount is less than 50%
order_items.discount < 0.5

<=​

Less than or equal to

Check if an order item discount is at most 50%
order_items.discount <= 0.5

is null​

Include if null

Check if an order item discount is unknown
order_items.discount is null

is not null​

Include if not null

Check if an order item discount is known
order_items.discount is not null

+​

Add two numeric values

Add product cost and profit
products.cost + products.profit

-​

Subtract two numeric values

Subtract product cost from revenue
products.revenue - products.cost

*​

Multiply two numeric values

Multiply order quantity and product price
orders.quantity * products.price

/​

Divide two numeric values

Divide product profit by product cost
products.profit / products.cost

Learn more​

Number Operators (AQL Doc)

Datetime Operators​

Description​

Right hand side of datetime operator takes a datetime scalar type as input and always starts with @ token. Datetimes can be expressed in a fully supported format as @YYYY-MM-DD HH:MM:SS, in shorter variations like @YYYY-MM, or a relative datetime (relative to the current real world time) like @(last 7 days).

Tags​

Operator, Condition

Operators​

==​

Include data that equal to an absolute timestamp

Check if an order is created at 2022-01-01 00:00:00
orders.created_at == @2022
Check if an order is created at the first timestamp of the last 7 days
orders.created_at == @(last 7 days)

is ; match ; matches​

Include data that are in a time period

Check if an order is created in the period of the year 2022
orders.created_at is @2022
Check if an order is created in the period of the last 7 days
orders.created_at match @(last 7 days)

!=​

Include data that do not equal to an absolute timestamp

Check if an order is not created at 2022-01-01 00:00:00
orders.created_at != @2022-01

is not​

Include data that are not in a time period

Check if an order is not created in the period of 2022-01
orders.created_at is not @2022-01

<​

Include data that are before a specific time period

Check if an order is created before the period of the year 2022
orders.created_at < @2022

<=​

Include data that are before or in a specific time period

Check if an order is created before or in the period of the year 2022
orders.created_at <= @2022

>​

Include data that are after a specific time period

Check if an order is created after yesterday
orders.created_at > @(yesterday)

>=​

Include data that are after or in a specific time period

Check if an order is created after or on yesterday
orders.created_at >= @(yesterday)

is null​

Include if the value is null

Check if an order creation day is unknown
orders.created_at is null

is not null​

Include if the value is not null

Check if an order creation day is known
orders.created_at is not null

+​

Add an interval to a datetime

Add 3 months to the creation day
orders.created_at + interval(3 months)
Substract 1 month to the creation day
orders.created_at + interval(-1 month)

-​

Subtract an interval to a datetime

Subtract 3 months to the creation day
orders.created_at - interval(3 months)
Add 1 month to the creation day
orders.created_at - interval(-1 month)

Learn more​

Datetime Operators (AQL Doc)

Let us know what you think about this document :)