Numeric functions#

Arithmetic, logarithmic and trigonometric functions. Most have a jsonb overload that takes a property directly, and a double precision overload from PostgreSQL. Either way, a Cypher RETURN prints the result as a jsonb number.

Angles are in radians unless the name ends in d.

Examples run against the example dataset.

abs#

abs ( x jsonb ) -> jsonb
abs ( x numeric ) -> numeric

Returns the absolute value of a number.

MATCH (m:movie {title:'Inception'}) RETURN abs(m.year - 2000) AS gap;

 gap
-----
 10

acos#

acos ( x jsonb ) -> jsonb
acos ( x double precision ) -> double precision

Returns the arc cosine of x, in radians. x must be between −1 and 1.

asin#

asin ( x jsonb ) -> jsonb
asin ( x double precision ) -> double precision

Returns the arc sine of x, in radians. x must be between −1 and 1.

atan#

atan ( x jsonb ) -> jsonb
atan ( x double precision ) -> double precision

Returns the arc tangent of x, in radians.

atan2#

atan2 ( y jsonb, x jsonb ) -> jsonb
atan2 ( y double precision, x double precision ) -> double precision

Returns the arc tangent of y / x, in radians, using the signs of both arguments to place the result in the correct quadrant.

Considerations

  • Prefer this to atan(y / x), which cannot tell −15/3 from 15/−3 and so loses the quadrant.

RETURN cot(1.5) AS cot, asin(1) AS asin, acos(0.5) AS acos, atan(-1) AS atan, atan2(-1.5, 1.3) AS atan2;

         cot         |        asin        |        acos        |        atan         |        atan2
---------------------+--------------------+--------------------+---------------------+---------------------
 0.07091484430265245 | 1.5707963267948966 | 1.0471975511965979 | -0.7853981633974483 | -0.8567056281827387

ceil#

ceil ( x jsonb ) -> jsonb
ceil ( x numeric ) -> numeric

Returns the smallest integer that is not less than x.

RETURN ceil(3.1) AS up, floor(3.1) AS down, round(3.6) AS nearest, sign(-19.93) AS sign;

 up | down | nearest | sign
----+------+---------+------
 4  | 3    | 4       | -1

cos#

cos ( x jsonb ) -> jsonb
cos ( x double precision ) -> double precision

Returns the cosine of an angle given in radians.

cosd#

cosd ( x double precision ) -> double precision

Returns the cosine of an angle given in degrees.

Considerations

  • Exact at the quarter turns, where converting the angle first is not: cosd(90) is 0 while cos(radians(90)) is 0.00000000000000006123233995736766.

  • The degree forms have no jsonb overload, so a property coerces to double precision.

RETURN sind(30) AS sind, cosd(60) AS cosd, tand(45) AS tand;

 sind | cosd | tand
------+------+------
 0.5  | 0.5  | 1

cot#

cot ( x jsonb ) -> jsonb
cot ( x double precision ) -> double precision

Returns the cotangent of an angle given in radians: the reciprocal of the tangent. See atan2 for a worked example.

degrees#

degrees ( x jsonb ) -> jsonb
degrees ( x double precision ) -> double precision

Converts radians to degrees.

RETURN degrees(pi()) AS half_turn, radians(180) AS in_radians;

 half_turn |    in_radians
-----------+-------------------
 180       | 3.141592653589793

e#

e ( ) -> numeric

Returns Euler’s number as a numeric.

Considerations

  • The result is numeric, so it carries more digits than exp(1), which is double precision: 2.7182818284590452 against 2.718281828459045.

RETURN e() AS e, exp(1) AS exp_one;

         e          |      exp_one
--------------------+-------------------
 2.7182818284590452 | 2.718281828459045

exp#

exp ( x jsonb ) -> jsonb
exp ( x numeric ) -> numeric

Returns e raised to the power x.

floor#

floor ( x jsonb ) -> jsonb
floor ( x numeric ) -> numeric

Returns the largest integer that is not greater than x. See ceil for a worked example.

ln#

ln ( x double precision ) -> double precision
ln ( x numeric ) -> numeric

Returns the natural logarithm of x. This is the function log resolves to in Cypher, and it can also be called under this name.

log#

log ( x jsonb ) -> jsonb

Returns the natural logarithm of x.

Considerations

  • In a Cypher query the parser rewrites log to ln, so the result column is named ln and there is no two-argument form:

RETURN log(2, 8);

ERROR:  function ln(integer, integer) does not exist
LINE 1: RETURN log(2, 8);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

  • This is the opposite of PostgreSQL’s log, which is base 10. For base 10 in Cypher, use log10.

RETURN log(27) AS natural, log10(27) AS base_ten;

      natural      |      base_ten
-------------------+--------------------
 3.295836866004329 | 1.4313637641589874

log10#

log10 ( x double precision ) -> double precision
log10 ( x numeric ) -> numeric

Returns the base-10 logarithm of x. Not rewritten, so this name means what it says.

pi#

pi ( ) -> double precision

Returns π. See degrees for a worked example.

radians#

radians ( x jsonb ) -> jsonb
radians ( x double precision ) -> double precision

Converts degrees to radians.

rand#

rand ( ) -> jsonb

Returns a number in the half-open range 0 to 1.

Considerations

  • rand() is marked immutable, so the planner folds it to a single constant. Every row of a query, and every execution of a prepared statement, sees the same number. Call PostgreSQL’s random() instead, which is volatile and works in Cypher.

FOR x IN [1,2,3] RETURN x, rand(), random();

 x |       rand        |       random
---+-------------------+---------------------
 1 | 0.533950385997692 | 0.33269661597409694
 2 | 0.533950385997692 | 0.10517878758331145
 3 | 0.533950385997692 | 0.3874591104544447

round#

round ( x jsonb ) -> jsonb
round ( x numeric [, scale integer ] ) -> numeric

Rounds x to the nearest integer, halves away from zero.

Considerations

  • The jsonb form rounds to an integer only. To keep decimal places, cast and use the numeric form: round(x::numeric, 2).

sign#

sign ( x jsonb ) -> jsonb
sign ( x numeric ) -> numeric

Returns −1, 0 or 1 according to the sign of x. See ceil for a worked example.

sin#

sin ( x double precision ) -> double precision

Returns the sine of an angle given in radians.

Considerations

  • Of the trigonometric functions, sine alone has no jsonb overload under its own name: the jsonb sine is catalogued as jsonb_sin. Nothing is lost. A property coerces to double precision and sin() resolves to the PostgreSQL function. But the result type differs from its neighbours over a jsonb argument.

RETURN sin(0.5) AS sin, cos(0) AS cos, tan(0) AS tan;

        sin        | cos | tan
-------------------+-----+-----
 0.479425538604203 | 1   | 0

MATCH (m:movie {title:'The Matrix'})
RETURN pg_typeof(cos(m.embedding[0])) AS cos_t, pg_typeof(sin(m.embedding[0])) AS sin_t,
       pg_typeof(tan(m.embedding[0])) AS tan_t;

  cos_t  |       sin_t        |  tan_t
---------+--------------------+---------
 "jsonb" | "double precision" | "jsonb"

sind#

sind ( x double precision ) -> double precision

Returns the sine of an angle given in degrees. See cosd for a worked example.

sqrt#

sqrt ( x jsonb ) -> jsonb
sqrt ( x numeric ) -> numeric

Returns the square root of x.

Considerations

  • A negative argument is an error rather than null:

RETURN sqrt(-16);

ERROR:  cannot take square root of a negative number

MATCH (m:movie {title:'The Matrix'}) RETURN sqrt(m.year) AS root;

        root
--------------------
 44.710177812216314

tan#

tan ( x jsonb ) -> jsonb
tan ( x double precision ) -> double precision

Returns the tangent of an angle given in radians. See sin for a worked example.

tand#

tand ( x double precision ) -> double precision

Returns the tangent of an angle given in degrees. See cosd for a worked example.

Arithmetic on properties#

The operators +, -, *, /, % and ^ work on properties, but on jsonb operands they follow jsonb’s own rules, not SQL’s: two integers divide as integers.

RETURN 7 % 3 AS modulo, 2 ^ 10 AS power, 7 / 2 AS integer_division, 7.0 / 2 AS real_division;

 modulo | power | integer_division |   real_division
--------+-------+------------------+--------------------
 1      | 1024  | 3                | 3.5000000000000000

Each is implemented by a backtick-named operator over jsonb; see arithmetic operators on jsonb.

Compatibility#

openCypher#

log() is base e here and base 10 in PostgreSQL. openCypher spells the natural logarithm log too, so a Cypher query is portable and a SQL one is not. openCypher’s e() and rand() exist; rand() deviates by being folded to a constant. haversin() is not supported.

See also#