27 Oct 2016

Neo4j: Dynamically add property/Set dynamic property

I’ve been playing around with a dataset which has the timetable for the national rail in the UK and they give you departure and arrival times of each train in a textual format.

For example, the node to represent a stop could be created like this:

CREATE (stop:Stop {arrival: "0802", departure: "0803H"})

That time format isn’t particular amenable to querying so I wanted to add another property which indicated the number of seconds since the start of the day.

So we want to add 'arrivalSecondsSinceStartOfDay' and 'departureSecondsSinceStartOfDay' properties to our node. I wrote the following query to calculate the values for those properties.

MATCH (stop:Stop)
UNWIND ["arrival", "departure"] AS key

WITH key,
     toInteger(substring(stop[key], 0, 2)) AS hours,
     toInteger(substring(stop[key], 2, 2)) AS minutes,
     CASE WHEN substring(stop[key], 4,1) = "H" THEN 30 ELSE 0 END AS seconds

WITH key, (hours * 60 * 60) + (minutes * 60) + seconds AS secondsSinceStartOfDay

RETURN key + "SecondsSinceStartOfDay" AS newKey, secondsSinceStartOfDay

╒═══════════════════════════════╤══════════════════════╕
│newKey                         │secondsSinceStartOfDay│
╞═══════════════════════════════╪══════════════════════╡
│arrivalSecondsSinceStartOfDay  │28920                 │
├───────────────────────────────┼──────────────────────┤
│departureSecondsSinceStartOfDay│29010                 │
└───────────────────────────────┴──────────────────────┘

Now we’re ready to set those properties on the 'stop' node.

MATCH (stop:Stop2)
UNWIND ["arrival", "departure"] AS key

WITH stop,
     key,
     toInteger(substring(stop[key], 0, 2)) AS hours,
     toInteger(substring(stop[key], 2, 2)) AS minutes,
     CASE WHEN substring(stop[key], 4,1) = "H" THEN 30 ELSE 0 END AS seconds

WITH stop, key, (hours * 60 * 60) + (minutes * 60) + seconds AS secondsSinceStartOfDay
WITH stop, key + "SecondsSinceStartOfDay" AS newKey, secondsSinceStartOfDay
SET stop[newKey] = secondsSinceStartOfDay

Invalid input '[': expected an identifier character, whitespace, '{', node labels, a property map, a relationship pattern, '.', '(', '=' or "+=" (line 12, column 9 (offset: 447))
"SET stop[newKey] = secondsSinceStartOfDay"
         ^

Hmmm that didn’t work as expected! It doesn’t look like we can set dynamic properties using Cypher just yet.

Luckily my colleague Michael Hunger and the Neo4j community have been curating the APOC procedures library and it has just the procedure to help us out.

You’ll need to download the jar for your version of Neo4j and then place it in the plugins directory. I’m using Neo4j 3.1 Beta1 so this is what it looks like for me:

$ tree neo4j-enterprise-3.1.0-BETA1/plugins/

neo4j-enterprise-3.1.0-BETA1/plugins/
└── apoc-3.1.0.1-all.jar

0 directories, 1 file

After you’ve done that you’ll need to restart Neo4j so that it can pick up the new procedures that we’ve added. Once you’ve done that execute the following query to check they’ve installed correctly:

call dbms.procedures()
YIELD name
WITH name
WHERE name STARTS WITH "apoc"
RETURN COUNT(*)

╒════════╕
│COUNT(*)│
╞════════╡
│183     │
└────────┘

We’re now ready to dynamically set properties in the graph. The procedure that we’ll use is apoc.create.setProperty and it’s easy to update our query to use it:

MATCH (stop:Stop)
UNWIND ["arrival", "departure"] AS key

WITH stop,
     key,
     toInteger(substring(stop[key], 0, 2)) AS hours,
     toInteger(substring(stop[key], 2, 2)) AS minutes,
     CASE WHEN substring(stop[key], 4,1) = "H" THEN 30 ELSE 0 END AS seconds

WITH stop, key, (hours * 60 * 60) + (minutes * 60) + seconds AS secondsSinceStartOfDay
WITH stop, key + "SecondsSinceStartOfDay" AS newKey, secondsSinceStartOfDay
CALL apoc.create.setProperty(stop, newKey, secondsSinceStartOfDay)

Query cannot conclude with CALL (must be RETURN or an update clause) (line 12, column 1 (offset: 439))
"CALL apoc.create.setProperty(stop, newKey, secondsSinceStartOfDay)"
 ^

Oops I spoke too soon! We need to yield the return column of the procedure and return it or just return a count to work around this:

MATCH (stop:Stop)
UNWIND ["arrival", "departure"] AS key

WITH stop,
     key,
     toInteger(substring(stop[key], 0, 2)) AS hours,
     toInteger(substring(stop[key], 2, 2)) AS minutes,
     CASE WHEN substring(stop[key], 4,1) = "H" THEN 30 ELSE 0 END AS seconds

WITH stop, key, (hours * 60 * 60) + (minutes * 60) + seconds AS secondsSinceStartOfDay
WITH stop, key + "SecondsSinceStartOfDay" AS newKey, secondsSinceStartOfDay
CALL apoc.create.setProperty(stop, newKey, secondsSinceStartOfDay)
YIELD node
RETURN COUNT(*)

╒════════╕
│COUNT(*)│
╞════════╡
│2       │
└────────┘

And that’s it, we can now dynamically set properties in our queries.

About the author

I'm currently working on short form content at ClickHouse. I publish short 5 minute videos showing how to solve data problems on YouTube @LearnDataWithMark. I previously worked on graph analytics at Neo4j, where I also co-authored the O'Reilly Graph Algorithms Book with Amy Hodler.