Money Datatype

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:

[ {
  "money_column": "10000.00",
} ]

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.
+ 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.

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)