Neo4j and Cypher: Rounding Floating Point Numbers/BigDecimals
I couldn’t figure out a way to solve this issue using pure Cypher, but there tends to be an APOC function to solve every problem... and this was no exception.
Join the DZone community and get the full member experience.
Join For FreeI was doing some data cleaning a few days ago and wanting to multiply a value by one million. My Cypher code to do so looked like this:
with "8.37" as rawNumeric
RETURN toFloat(rawNumeric) * 1000000 AS numeric
╒═════════════════╕
│"numeric" │
╞═════════════════╡
│8369999.999999999│
└─────────────────┘
Unfortunately, that suffers from the classic rounding error when working with floating point numbers. I couldn’t figure out a way to solve it using pure Cypher, but there tends to be an APOC function to solve every problem... and this was no exception.
I’m using Neo4j 3.2.3, so I downloaded the corresponding APOC JAR and put it in a plugins directory:
$ ls -lh plugins/
total 3664
-rw-r--r--@ 1 markneedham staff 1.8M 9 Aug 09:14 apoc-3.2.0.4-all.jar
I’m using Docker, so I needed to tell it where my plugins folder lives:
$ docker run -v $PWD/plugins:/plugins \
-p 7474:7474 \
-p 7687:7687 \
-e NEO4J_AUTH="none" \
neo4j:3.2.3
Now, we’re reading to try out our new function:
with "8.37" as rawNumeric
RETURN apoc.number.exact.mul(rawNumeric,"1000000") AS apocConversion
╒════════════════╕
│"apocConversion"│
╞════════════════╡
│"8370000.00" │
└────────────────┘
That almost does what we want, but the result is a string rather than numeric value. It’s not too difficult to fix, though:
with "8.37" as rawNumeric
RETURN toFloat(apoc.number.exact.mul(rawNumeric,"1000000")) AS apocConversion
╒════════════════╕
│"apocConversion"│
╞════════════════╡
│8370000 │
└────────────────┘
That’s more like it!
Published at DZone with permission of Mark Needham, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments