How to avoid creating nodes that have null values?

Question

My CSV data looks like this:

device_id,device_type,connected_from,connected_to
200,Thermostat,,
201,Smoke Detector,,
202,Light Sensor,,
203,Security Camera,,
204,Smart Lock,,
,,200,201
,,200,202
,,200,203
,,201,203
,,202,204

My Cypher query is:

LOAD CSV FROM "/path-to/iot_devices_and_connections.csv" WITH HEADER AS row
WITH row WHERE row.device_id IS NOT NULL AND row.device_type IS NOT NULL
CREATE (d:Device {id: row.device_id, type: row.device_type});

The problem is that when I run the query rows with null values also get created as nodes. I've set in the code row.device_id IS NOT NULL AND row.device_type IS NOT NULL but it doesn't seem to work.

How can I create only nodes that have device_id and device_type?

Answer 1

Try to modify your query so that it includes NULLIF. To quote the documentation: "This option enables you to specify a sequence of characters that will be parsed as null. By default, all empty columns in Memgraph are treated as empty strings, so if this option is not used, no values will be treated as null."

You still need the IS NOT NULL check in your query. The NULLIF option in the LOAD CSV clause only specifies how to treat a certain sequence of characters as null when loading the CSV file. It does not automatically filter out null values:

LOAD CSV FROM "/path-to/iot_devices_and_connections.csv" WITH HEADER NULLIF '' AS row
WITH row WHERE row.device_id IS NOT NULL AND row.device_type IS NOT NULL
CREATE (d:Device {id: row.device_id, type: row.device_type});