Neo4j

Zaszigre · ‎02-13-2020

Hello everyone,

Trying to import a CSV file, I had a 'cannot merge .. using null property value' error. I saw it is a quite common error, However, it seems there is no reasons for this exception to be thrown in my case (with my beginner analytical capacities).

I have a file referencing bus line (with no redundancies) of Paris and its surroundings with notable columns 'ID' being unique and 'Geo Shape' being a Json string containing arrays of bus stops' geographic positions.

I already have imported the bus lines without the Geo Shape column with the following query :

LOAD CSV WITH HEADERS FROM 'file:///bus_lignes.csv' AS line FIELDTERMINATOR ';'

MERGE (l:BusLine {
  id_ligne:			coalesce(line.ID, ''),
  nom_ligne:			coalesce(line.nom, ''),
  numero_ligne:		TOINTEGER(coalesce(line.code_lig, '0'))
});

Note (1):
with the following query, we can ensure there are no null value in the ID column

MATCH (l:BusLine) WHERE l.id_ligne = '' RETURN l;

returning the result:

(no changes, no records)

Still, when I try to import, from the exact same CSV the bus stops, and creating the relationships between the bus stops with a busLineId property with the following query, I got the error message quoted above:

LOAD CSV WITH HEADERS FROM "file:///bus_lignes.csv" AS line FIELDTERMINATOR ";"

MATCH (l:BusLine) WHERE ToINTEGER(l.id_ligne) = ToINTEGER(line.ID)

WITH apoc.convert.fromJsonMap(line.`Geo Shape`) AS geoShape
WITH geoShape.coordinates as coordinates, geoShape
WITH  coordinates, geoShape, RANGE(0, SIZE(coordinates)-2) as iterList
UNWIND coordinates as subline//for MultiString
      WITH  coordinates, geoShape, subline, iterList, RANGE(0, SIZE(subline)-2) as jterList

FOREACH(i in iterList |
    FOREACH(trash in CASE WHEN geoShape.type = 'LineString' THEN[1] ELSE[] END |
    MERGE (s0:BusStop:GeographicPoint {
    point : Point({
            longitude : coordinates[i][0],
                latitude : coordinates[i][1]
            })
  })
        MERGE (s1:BusStop:GeographicPoint {
        point : Point({
            longitude : coordinates[i+1][0],
                latitude : coordinates[i+1][1]
            })
        })
        MERGE (s0)-[:SERVED_BY]->(l)
        MERGE (s1)-[:SERVED_BY]->(l)
        MERGE (s0)-[:NEXT_TO {
        	busLineId : l.id_ligne
        }]->(s1)
    ) FOREACH(trash in CASE WHEN geoShape.type = 'MultiString' THEN[1] ELSE[] END |
            FOREACH(i in jterList |
                MERGE (s0:BusStop:GeographicPoint {
                    point : Point({
                        longitude : subline[i][0],
                        latitude : subline[i][1]
                    })
                })
                MERGE (s1:BusStop:GeographicPoint {
                    point : Point({
                        longitude : subline[i+1][0],
                        latitude : subline[i+1][1]
                    })
                })
                MERGE (s0)-[:SERVED_BY]->(l)
                MERGE (s1)-[:SERVED_BY]->(l)
                MERGE (s0)-[:NEXT_TO {
                    busLineId : l.id_ligne
                }]->(s1)
            )
        )
)

returning :

Cannot merge relationship using null property value for busLineId (Failure when processing file '/home/user/.config/Neo4j%20Desktop/Application/neo4jDatabases/database-7d0d1d10-83f9-42ba-964f-682e9c8766a7/installation-4.0.0/import/bus_lignes.csv' on line 5.)

Note (2):
the ID column of the file's line 5 is '45302001'. The following query shows that there is no null value:

MATCH (l:BusLine) WHERE TOINTEGER(l.id_ligne) = TOINTEGER('45302001') RETURN l

resulting 1 record

{
  "numero_ligne": 1,
  "nom_ligne": "QUINCY-SOUS-SENART (GARE RER D)  - QUINCY-SOUS-SENART (GARE RER D)",
  "id_ligne": "045302001"
}

So I don't understand why it prompt this line containing an error

Note (3):
I try to use coalesce to force the creation a the relationships with '0's if null value were found (I'm not showing the query as it is the same as above, replacing busLineId : l.id_ligne by busLineId : coalesce(l.id_ligne, '0').
I used the following query to check the result but I'm not showing the result either because all the relationships had "busLineId" = "0":

MATCH (a:BusStop)-[n:NEXT_TO]->(b:BusStop) RETURN n.busLineId

Note (4):
I already check if there were a misspell but haven't found one (still looking for one, that would be the easiest problem to solve) and I'm using the exact same csv file.
I found a StackOverflow Topic describing a similar issue with no answers marked as solution :

Note (5):
l.ligne_id is different from l.ID because the real 'official' format for those bus line ids is a concatenation of three ids of 3 numbers filled with 0's (see note 3 query and result. the id is '045'+ '302'+ '001') so I just reformated them. That's why I'm using TOINTEGER(..).

Is there anything else than a null value (that i showed it seems unlikely) or a no-match (that it is not likely as l.ligne_id is imported from line.ID) that could throw this exception.

Otherwise, I failed to write a query to prompt all line.ID where there is no TOINTEGER(l.ligne_id) = TOINTEGER(line.ID) match found, in other words, a query that would prompt line.ID's not contained in any (:BusLine) node. Could you help me with this ?

I know it is a lot of information but I want to be sure you have all that you need to help me if you wish.
Thank you so much if you would help me, Have a nice day ! ❤️
Zaszigre.

ganesanmithun32 · ‎02-14-2020

Hi Zaszigre ,
you need to put that 'l' in all the WITH statements .

MERGE (s0)-[:SERVED_BY]->(l)
        MERGE (s1)-[:SERVED_BY]->(l)
        MERGE (s0)-[:NEXT_TO {
        	busLineId : l.id_ligne
        }]->(s1)

in the query here, this 'l' is not the the BusLine node you matched first . Since you didn't put this in the consecutive WITH statements before the FOREACH . the 'l' inside the FOREACH is just some other new node created when you do the MERGE like above. That new node doesn't have the 'id_ligne' property . That's why you get null pointer exception .
your working query will look like following,

LOAD CSV WITH HEADERS FROM "file:///bus_lignes.csv" AS line FIELDTERMINATOR ";"

MATCH (l:BusLine) WHERE ToINTEGER(l.id_ligne) = ToINTEGER(line.ID)

WITH apoc.convert.fromJsonMap(line.`Geo Shape`) AS geoShape,l
WITH geoShape.coordinates as coordinates, geoShape,l
WITH  coordinates, geoShape, RANGE(0, SIZE(coordinates)-2) as iterList,l
UNWIND coordinates as subline
      WITH  coordinates, geoShape, subline, iterList, RANGE(0, SIZE(subline)-2) as jterList,l

FOREACH(i in iterList |
    FOREACH(trash in CASE WHEN geoShape.type = 'LineString' THEN[1] ELSE[] END |
    MERGE (s0:BusStop:GeographicPoint {
    point : Point({
            longitude : coordinates[i][0],
                latitude : coordinates[i][1]
            })
  })
        MERGE (s1:BusStop:GeographicPoint {
        point : Point({
            longitude : coordinates[i+1][0],
                latitude : coordinates[i+1][1]
            })
        })
        MERGE (s0)-[:SERVED_BY]->(l)
        MERGE (s1)-[:SERVED_BY]->(l)
        MERGE (s0)-[:NEXT_TO {
        	busLineId : l.id_ligne
        }]->(s1)
    ) FOREACH(trash in CASE WHEN geoShape.type = 'MultiString' THEN[1] ELSE[] END |
            FOREACH(i in jterList |
                MERGE (s0:BusStop:GeographicPoint {
                    point : Point({
                        longitude : subline[i][0],
                        latitude : subline[i][1]
                    })
                })
                MERGE (s1:BusStop:GeographicPoint {
                    point : Point({
                        longitude : subline[i+1][0],
                        latitude : subline[i+1][1]
                    })
                })
                MERGE (s0)-[:SERVED_BY]->(l)
                MERGE (s1)-[:SERVED_BY]->(l)
                MERGE (s0)-[:NEXT_TO {
                    busLineId : l.id_ligne
                }]->(s1)
            )
        )
)

**check that unwinding coordinates as subline part too .. i dont have clear picture of what you are trying there since i didnt have a proper look into the data
I would suggest you going through the documentation 'WITH' clause tho . please reply here if you have any doubts regarding 'WITH' clause
https://neo4j.com/docs/cypher-manual/current/clauses/with/

Neo4j

'cannot merge .. using null property value' with apparently no null value in CSV