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 finding
return
(f.closedOn - f.createdOn)/(3600000*24)
as differenceInDays
Math expressions
Below is a table outlining the supported mathematical expression within J1QL:
Function | Description | Example Query |
---|---|---|
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 ^ 2 |
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 |
sqrt | Square 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 |
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 | Round down to next closes whole number. | FIND Risk as r RETURN FLOOR(ABS(r.impact - 5) / r.probability) |
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 | 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 |
concat | Allows math expressions. | `FIND (aws_s3_bucket |