2

I am using the following Cypher Query in "Pentaho Execute SQL step" to load data into Neo4j Database from the "Excel Input" but while loading there were null/empty values in some columns in Excel.while executing the Query ,Please help me out with the issue

Query:

create(i:incident{number_1:{3} })
merge(c:company{sys_domain_1: {1} })
merge(d:parent{domain : {6} })
merge(a:alert {number: {2} })
merge(s:shelf {u_shelve: {5} }) 
merge(ci:config{cmdb_ci_1:{4} })
merge (d)-[:has_Company] -> (c)
merge (c)-[:has_CI] -> (ci)
merge (ci)-[:has_Incident] -> (i)
merge (i)-[:has_alert] -> (a)
merge (i)-[:has_shelf] -> (s);

I need to create a node without null values ,For example If I am merging "alert number " has null it is creating null value as node in Neo4j so I need to omit the null values from "number column"

merge(a:alert {number: {2} })

Thanks in Advance.

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
Haritha Saravanan
  • 29
  • 2

1 Answers1

1

There are two possibilities depending on what you want.

Option 1 is to use coalesce to replace null with some default value. For example:

MERGE (a:alert { number: coalesce({2}, 1) })

will use the value 1 if the inputted parameter is null.

The other option is to use a CASE expression documentation here. This will allow you to not do the merge at all unless the value is not null.

FrobberOfBits
  • 17,634
  • 4
  • 52
  • 86
  • Thanks , I tried with your Idea but while using "coalesce" it is replacing the null nodes as "1" in Neo4j. whereas I need to remove all the null nodes along with the child node ,while executing the above mentioned Query ,which is coming from the Input parameter.For example in this cypher Query "merge(a:alert {number: {2} })" , it has null values in input parameter so I need to remove the nulls along with its child . – Haritha Saravanan Mar 28 '18 at 06:19
  • Yes, that's what coalesce would do. Then in this case you can either use the `CASE` expression I linked and not create the node in the first place, or alternatively you could coalesce to some known default value like '1' and then later come back and match all those nodes with the default value and delete them. – FrobberOfBits Mar 28 '18 at 14:16
  • Thanks for your previous clarification. From the above cypher Query , which is executed into the "Pentaho Execute SQL script".I have used the CASE expression as below Query : "WITH CASE WHEN {4} IS NULL then [1] else [] end as dd merge(ci:config{cmdb_ci_1:{4} }) DETACH DELETE dd" In the mentioned line, I need to remove the null values in the column cmdb_ci_1 but instead of removing nulls it is creating undefined nodes with id and linking together. Could you please explain me how to use the case Statement to remove null values for column cmdb_ci_1 through cypher in Pentaho Step. – Haritha Saravanan Mar 29 '18 at 11:30
  • when I use coalesce to some default value like '1' and later match those nodes with default value and delete them ,the child nodes with respect to nodes with default values are remained alone as such in Graph DB.so please suggest a way where I can omit/avoid the null values from the input parameter through cypher Query in "Pentaho Execute SQL script".By suggesting this way I can filter the null values of multiple columns from the input Parameter ,I can load nodes without the null values in Database.Thanks in advance. – Haritha Saravanan Mar 30 '18 at 06:18
  • I am using the below Query in Pentaho Execute SQL Step to load data into Neo4j Graph Database , (i.e) I need a cypher Query to get/fetch the Top CI's with help of Alerts "merge(c:company{sys_domain_1: {1} }) merge(a:alert { number: coalesce({2}, 1) }) merge(d:domain {domain:coalesce({6},1) }) merge(i:incident{number_1: {3} }) merge(ci:config{cmdb_ci_1: {4} }) merge(s:shelf{u_shelve:coalesce({5}, 1)}) merge (d)-[:has_company] -> (c) merge (c)-[:has_CI] -> (ci) merge (ci)-[:has_alert] -> (a) merge (a)-[:has_incident] -> (i) merge (i)-[:has_shelf] -> (s) Please help me how to Query – Haritha Saravanan Apr 23 '18 at 12:15