Number Datatype

Numbers are arbitrary precision, arbitrary scale numbers. They can represent any number exactly, except for numbers whose digits repeat infinitely.

Since Numbers can be either larger or more precise than what JSON parsers allow, many formats, such as JSON, serialize them as strings. For example:

[ {
  "number_column" : "42"
} ]

The following table describes the operators that can be used with Numbers.

Operator Description
< TRUE for numbers less than this one.
<= TRUE for numbers that are less than or equal to this one.
> TRUE for numbers that are greater than this one.
>= TRUE for numbers that are greater than or equal to this one.
!= TRUE for numbers that are not equal to this one.
= TRUE for numbers that are equal to this one.
IS NULL TRUE for numbers that are NULL.
IS NOT NULL TRUE for numbers that are not NULL.
+ Adds two numbers
- Subtracts one number from another
* Multiplies two numbers
/ Divides one number by another
% Returns the modulo of one number divided by another
^ Returns the modulo of one number divided by another

And the following functions can be used with them:

Keyword Name Description Availability
distinct Returns distinct set of records 2.1
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
greatest(...) Returns the largest value among its arguments, ignoring NULLs. 2.1
in(...) Matches values in a given set of options 2.1
least(...) Returns the smallest value among its arguments, ignoring NULLs. 2.1
ln(...) Returns the natural log of a number 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
regr_intercept(...) Returns the y-intercept of the linear least squares fit 2.1
regr_r2(...) Returns the square of the correlation coefficient (r²) 2.1
regr_slope(...) Returns the slope of the linear least squares fit 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 traffic sensors seeing more than 20,000 vehicles per day from the Chicago Average Daily Traffic Counts:

https://data.cityofchicago.org/resource/u77m-8jgp.json?$where=total_passing_vehicle_volume > 20000

You can also aggregate numbers, so you could also get the average daily count per sensor with avg(...):

https://data.cityofchicago.org/resource/u77m-8jgp.json?$select=avg(total_passing_vehicle_volume)