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.
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.
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.
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:
Exponents
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
ORDER BY score ASC
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
To Integer (TOINT)
Casts field values to an integer. Note, values that cannot be converted to an integer will return undefined.
FIND (aws_s3_bucket|aws_dynamodb_table) AS store
RETURN TOINT(COALESCE(store.backupSizeBytes, store.bucketSizeBytes, 0)) AS size