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)