Skip to main content

Math operations and expressions

J1QL provides robust mathematical capabilities for performing calculations and transformations on returned values, including standard operations like addition, subtraction, multiplication, and division. By leveraging parentheses, you have control over the order of operations to achieve precise results. Additionally, J1QL offers a range of mathematical expressions as functions, empowering advanced calculations and analysis to extract valuable insights from your data. These mathematical features enhance the flexibility and analytical power of J1QL in querying and manipulating numerical data.

Math operations

J1QL provides support for fundamental mathematical operations on returned values, including addition (+), subtraction (-), division (/), multiplication (*), and the use of parentheses () to control the order of operations. The evaluation follows the standard order of operations: parentheses, multiplication or division, and then addition or subtraction.


These operations are designed for numerical values and do not work with strings or string representations of numbers.

Math operations example query
FIND (aws_db_cluster_snapshot|aws_db_snapshot) AS snapshot
RETURN snapshot.displayName, snapshot.allocatedStorage * 0.02 AS Cost

You can also combine math operations with aggregate functions. You can learn more about aggregate functions here.

Combining a math operation with an aggregate function
FIND (aws_db_cluster_snapshot|aws_db_snapshot) AS snapshot
RETURN snapshot.tag.AccountName as Account, sum(snapshot.allocatedStorage) * 0.02 AS EstimatedCost

Calculations can also be made with date time fields. When calculating the difference between two dates in days, the date field will automatically be converted to the millisecond epoch. In order to return this difference in hours it will require dividing by 3600000 (number of milliseconds in an hour). In order to convert that hour into days, multiply by 24.

Calculations with date time fields
FIND Finding AS f 
RETURN f.displayName, (f.closedOn - f.createdOn) / (3600000 * 24) AS differenceInDays

Math expressions

Below is an outline of the supported mathematical expression within J1QL:


A quantity representing the power to which a given number or expression is to be raised, usually expressed as a raised symbol beside the number or expression (e.g. 3 in 23 = 2 × 2 × 2).

FIND Risk AS r
RETURN r.probability, r.probability ^ 2

Absolute (ABS)

Absolute value, the magnitude of a real number without regard to its sign.

FIND Risk AS r
RETURN ABS(r.impact - 5) / r.probability

Square Root (SQRT)

Square root, a number which produces a specified quantity when multiplied by itself.

FIND Risk AS r
RETURN r.displayName, SQRT((5 - r.impact) ^ 2 + (5 - r.probability) ^ 2) AS score

Ceiling (CEIL)

Round up to the next closest whole number.

FIND DataStore WITH allocatedStorage > 0 AS d
RETURN d.displayName, CEIL(d.allocatedStorage / 1000) AS allocatedMb

Floor (FLOOR)

Round down to next closes whole number.

FIND Risk AS r
RETURN FLOOR(ABS(r.impact - 5) / r.probability)

Round (ROUND)

Round up or down to the next closes whole number.

FIND DataStore WITH backupSizeBytes > 0 AS d
RETURN d.displayName, ROUND(d.backupSizeBytes / d.backupsCount) AS averageBackupSize

Coalesce (COALESCE)

Use the first found value. Provide a list of values and the first value to not be undefined/null will be used.

FIND (aws_s3_bucket|aws_dynamodb_table) AS store
RETURN store._type, store.displayName, COALESCE(store.backupSizeBytes, store.bucketSizeBytes, 0) / 1000 AS kb

Concatenate (CONCAT)

Concatenates field values into a single field, allows math expressions.

FIND (aws_s3_bucket|aws_dynamodb_table) AS store
RETURN store._type, store.displayName, CONCAT(COALESCE(store.backupSizeBytes, store.bucketSizeBytes, 0) / 1000, ' kb') AS size