Handling null values in Cypher Queries

Question

I am using Neo4j java driver for adding nodes from my mysql database to Neo4j.

My problem is that some column value in a record can be null. how can I handle them in cypher queries because for the null values from mysql table it is not creating any node.

Here's my code for creating node using neo4j java driver

public void readObsTableAndCreateNode() throws Exception {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connect = DriverManager.getConnection("jdbc:mysql://localhost/openmrsnew?user=root&password=qwas");
            statement = connect.createStatement();
            resultSet = statement.executeQuery("select obs_id, person_id, concept_id, encounter_id, order_id, obs_datetime, value_text, date_created, creator, uuid, voided from obs where `obs`.`date_created` > '2018-09-20 00:00:00' and concept_id != 33334002 limit 2");

            List<HashMap<String, Object>> cypherQueriesList = new ArrayList<HashMap<String, Object>>();

            while (resultSet.next()) {

                HashMap<String, Object> cipherQueryParams = new HashMap<String, Object>();
                Integer obsId = Integer.parseInt(resultSet.getString("obs_id"));
                Integer personId = Integer.parseInt(resultSet.getString("person_id"));
                Integer conceptId = Integer.parseInt(resultSet.getString("concept_id"));
                Integer creator = Integer.parseInt(resultSet.getString("creator"));
                Integer voided = Integer.parseInt(resultSet.getString("voided"));
                Integer encounterId = encounterId = Integer.parseInt(resultSet.getString("encounter_id"));
                String date_created = resultSet.getString("date_created");
                String value_text= resultSet.getString("value_text");
                String uuid = resultSet.getString("uuid");           
                String obs_datetime= resultSet.getString("obs_datetime");

                ((HashMap<String, Object>) cipherQueryParams).put("creator", creator);
                ((HashMap<String, Object>) cipherQueryParams).put("valueText", value_text);
                ((HashMap<String, Object>) cipherQueryParams).put("uuid", uuid);
                ((HashMap<String, Object>) cipherQueryParams).put("dateCreated", date_created);
                ((HashMap<String, Object>) cipherQueryParams).put("obsDatetime", obs_datetime); 
                ((HashMap<String, Object>) cipherQueryParams).put("obsId", obsId);
                ((HashMap<String, Object>) cipherQueryParams).put("encounterId", encounterId);
                ((HashMap<String, Object>) cipherQueryParams).put("conceptId", conceptId);
                ((HashMap<String, Object>) cipherQueryParams).put("personId", personId);
                ((HashMap<String, Object>) cipherQueryParams).put("voided", voided);

                cypherQueriesList.add(cipherQueryParams);
            }

            HashMap<String, Object> cypherQuerybatch = new HashMap<String, Object>();
            cypherQuerybatch.put("batch", cypherQueriesList);

            // I am using merge because I dont want duplicated enteries

            String cypherQuery = "UNWIND {batch} as row MERGE (m:temp { obsId: row.obsId ,valueText: row.valueText, uuid: row.uuid, dateCreated: row.dateCreated , obsDatetime: row.obsDatetime })"
                    + " WITH m, row MATCH (c:Concept {conceptId: row.conceptId }) MERGE (m)-[:CONCEPT]->(c) "
                    + " WITH m, row MATCH (e:Encounter {encounterId: row.encounterId }) MERGE (m)-[:ENCOUNTER]->(e) "           
                    + " WITH m, row MATCH (u:Users {userId: row.creator }) MERGE (m)-[:USERS]->(u) "
                    + " WITH m, row MATCH (p:Person {personId: row.personId}) MERGE (m)-[:PERSON]->(p) "
                    + " RETURN m";

            runCypherWithParams(cypherQuery, cypherQuerybatch);

        } catch (Exception e) {
            throw e;
        } finally {
            close();
        }

    }



private void runCypherWithParams(String cypherQuery,  Map<String, Object> parameters) throws Exception{
        System.out.println("Posting Data to Neo4J browser...");
        try {
            connNeo4j = new ConNeo4j("bolt://localhost:7687", "neo4j", "qwas");
            connNeo4j.execQueryBySession(cypherQuery, parameters);                
        } catch (Exception e) {
            e.printStackTrace();
        }finally {

        }

    }



public void execQueryBySession(String query, Map<String, Object> parameters) {
        try {
            Session session = driver.session();
            StatementResult result = session.run(query, parameters);

            System.out.println("Success:- " + result);
        } catch (Exception e) {
            System.out.println("[WARNING] Null Row" + e.getMessage());
        }
    }

In neo4j browser console when I run below cypher query no node is formed.

MERGE (m:Temp { obsId: 742241 ,valueText: NULL}) return m;

UPDATE: I am able to handle the null values at Java Side. But for another table I am getting some strange behaviour.

In Neo4j browser console. I am setting the params as

:param batch: [{deathdateEstimated: 0, birthdateEstimated: 1, creator: 6, birthdate: "2018-01-01", dateCreated: "2018-09-20 04:08:0", gender: "M", personId: 1, voided: 0, uuid: "d7132677-6f7b-aeb8-af5f-850d621b6bbb"}, {deathdateEstimated: 0, birthdateEstimated: 1, creator: 1, birthdate: "2018-01-01", dateCreated: "2018-09-20", gender: "M", personId: 2, voided: 0}]

When I am running below cypher query only one node get created instead of two.

UNWIND {batch} as row MERGE (m:tempPerson) ON CREATE SET m+=row WITH m,row MATCH (u:Users {userId: row.creator }) MERGE (m)-[:USERS]->(u) return m;

Himanshu Jain Himanshu Jain · Accepted Answer · 2018-09-24T18:34:17

From Neo4j docs:

While you can perform a MATCH where a node variable in the match is null (such as from a failed OPTIONAL MATCH), you cannot CREATE or MERGE a pattern with a null node variable.

Another option that you might be able to use is ON CREATE and ON MATCH options provided in Merge. This query, however, is dependent on the fact that your node can be uniquely identified using "obsId" field.

MERGE (m:Temp { obsId: 742241}) 
ON CREATE SET m.valueText = NULL
ON MATCH SET m.valueText = NULL
return m;

Handling null values in Cypher Queries

2 Answers