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.

note

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 finding 
return 
  (f.closedOn - f.createdOn)/(3600000*24) 
  as differenceInDays

Math expressions​

Below is a table outlining the supported mathematical expression within J1QL:

FunctionDescriptionExample Query
exponentsA 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 ^ 2
absAbsolute value, the magnitude of a real number without regard to its sign.FIND Risk as r RETURN ABS(r.impact - 5) / r.probability
sqrtSquare root, a number which produces a specified quantity when multiplied by itself.FIND * as any THAT HAS Risk as r RETURN any.displayName, any._class, sqrt((5 - r.impact)^2 + (5 - r.probability)^2)) as score ORDER BY score ASC
ceilRound up to the next closest whole number.FIND DataStore with allocatedStorage > 0 as d RETURN d.displayName, CEIL(d.allocatedStorage/1000) as allocatedMb
floorRound down to next closes whole number.FIND Risk as r RETURN FLOOR(ABS(r.impact - 5) / r.probability)
roundRound 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
coalesceUse 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
concatAllows math expressions.`FIND (aws_s3_bucket

Contents