· neo4j cypher strava datetime

Neo4j 3.4: Syntax Error - Text cannot be parsed to a Duration (aka dealing with empty durations)

As I continued with my travels with Neo4j 3.4’s temporal data type I came across some fun edge cases when dealing with empty durations while importing data.

Imagine we’re trying to create 3 nodes from the following array of input data. Two of the rows have invalid durations!

UNWIND [
 {id: 12345, duration: "PT2M20S"},
 {id: 12346, duration: ""},
 {id: 12347, duration: null}
] AS row

MERGE (run:Run {id: row.id})
SET run.duration = duration(row.duration)

If we run that query we’ll get this error message:

Neo.ClientError.Statement.SyntaxError: Text cannot be parsed to a Duration
""
 ^

It doesn’t like the empty string. Let’s see how we can detect that:

UNWIND [
 {id: 12345, duration: "PT2M20S"},
 {id: 12346, duration: ""},
 {id: 12347, duration: null}
] AS row

return row.duration AS value,
       trim(row.duration) = "" AS isEmpty
+---------------------+
| value     | isEmpty |
+---------------------+
| "PT2M20S" | FALSE   |
| ""        | TRUE    |
| NULL      | NULL    |
+---------------------+

Now let’s use our conditional statement in the import query:

UNWIND [
 {id: 12345, duration: "PT2M20S"},
 {id: 12346, duration: ""},
 {id: 12347, duration: null}
] AS row

MERGE (run:Run {id: row.id})
SET run.duration = CASE WHEN trim(row.duration) = ""   THEN null
                        ELSE duration(row.duration) END
Invalid call signature

Hmmm still not happy. This time the null is the issue - passing a null value to the duration function returns an Invalid call signature response.

Let’s update our conditonal check to detect null values as well:

UNWIND [
 {id: 12345, duration: "PT2M20S"},
 {id: 12346, duration: ""},
 {id: 12347, duration: null}
] AS row

return row.duration AS value,
       exists(row.duration) AS isNotNull,
       trim(row.duration) = "" AS isEmpty
+---------------------------------+
| value     | isNotNull | isEmpty |
+---------------------------------+
| "PT2M20S" | TRUE      | FALSE   |
| ""        | TRUE      | TRUE    |
| NULL      | FALSE     | NULL    |
+---------------------------------+

Now let’s use those conditional checks in our first query:

UNWIND [
 {id: 12345, duration: "PT2M20S"},
 {id: 12346, duration: ""},
 {id: 12347, duration: null}
] AS row

MERGE (run:Run {id: row.id})
SET run.duration = CASE WHEN not(exists(row.duration)) THEN null
                        WHEN trim(row.duration) = ""   THEN null
                        ELSE duration(row.duration) END
Added 3 nodes, Set 4 properties, Added 3 labels

Looks happy. Let’s find our newly created nodes:

MATCH (run:Run)
WHERE run.id IN range(12345,12347)
RETURN run
+------------------------------------------+
| run                                      |
+------------------------------------------+
| (:Run {duration: P0M0DT140S, id: 12345}) |
| (:Run {id: 12346})                       |
| (:Run {id: 12347})                       |
+------------------------------------------+

As expected two of them don’t have a duration since none was provided.

  • LinkedIn
  • Tumblr
  • Reddit
  • Google+
  • Pinterest
  • Pocket