Heads up! Numbers, Doubles, and Moneys are all numeric types, and can be compared and combined in functions. For example, you could multiply a money value by a number, or compare the values of a number and a double.
Monetary values are fixed precision numbers, as most currencies aren’t divisible beyond their smallest unit (ex. one cent). In most representations, they’re encoded as strings to preserve accuracy. For example:
That also persists into how they must be encoded in queries - monetary values must be quoted to retain their accuracy. For example: $where=salary > '90000'
.
The following table describes the operators that can be used with Money fields.
Operation | Description |
---|---|
< |
TRUE for monetary values less than this one. |
<= |
TRUE for monetary values that are less than or equal to this one. |
> |
TRUE for monetary values that are greater than this one. |
>= |
TRUE for monetary values that are greater than or equal to this one. |
!= |
TRUE for monetary values that are not equal to this one. |
= |
TRUE for monetary values that are equal to this one. |
IS NULL |
TRUE for monetary values that are NULL . |
IS NOT NULL |
TRUE for monetary values that are not NULL . |
+ |
Adds two monetary values |
- |
Subtracts one monetary value from another |
* |
Multiplies two values, can be performed with numbers |
/ |
Divides one value by another, can be performed with numbers |
The following table describes the functions that can be used with money
fields.
Keyword Name | Description | Availability |
---|---|---|
Function Name | Description | Availability |
---|---|---|
avg(...) |
Returns the average of a given set of numbers | 2.0 and 2.1 |
between ... and ... |
Returns TRUE for values in a given range | 2.1 |
case(...) |
Returns different values based on the evaluation of boolean comparisons | 2.1 |
count(...) |
Returns a count of a given set of records | 2.0 and 2.1 |
in(...) |
Matches values in a given set of options | 2.1 |
max(...) |
Returns the maximum of a given set of numbers | 2.1 |
min(...) |
Returns the minimum of a given set of numbers | 2.1 |
not between ... and ... |
Returns TRUE for values not in a given range | 2.1 |
not in(...) |
Matches values not in a given set of options | 2.1 |
stddev_pop(...) |
Returns the population standard deviation of a given set of numbers | 2.1 |
stddev_samp(...) |
Returns a sampled standard deviation of a given set of numbers | 2.1 |
sum(...) |
Returns the sum of a given set of numbers | 2.1 |
For example, to get all of the employees from the City of Chicago that make more than $100,000/year:
https://data.cityofchicago.org/resource/tt4n-kn4t.json?$where=employee_annual_salary > '100000'
You can also aggregate monetary data, so you could also get the total amount of money spent on salaries at the City of Chicago:
https://data.cityofchicago.org/resource/tt4n-kn4t.json?$select=sum(employee_annual_salary)