cancel
Showing results for 
Search instead for 
Did you mean: 

Ignore-function for null property values !?

dionisios95
Node Clone

Hello guys, 

my question is if there is a way to ignore a MERGE-line if a variable "if not exists". 

I have many xml-Files with some fields that dont exist in all my files, so I have to think about a solution 

with an option to ignore MERGE-Code. 

Example: 

call apoc.load.xml("file:///NCT0000xxxx/NCT00000102.xml") yield value 
with value._children as root 
...............
with [in title where x._type = 'title'][0] as title,
     [in nctid where x._type = 'nct_id'][0] as nctid, 
     [in mstitle where x._type = 'title'][0] as mstitle, 
     [in participantslist where x._type = 'participants'][0] as participants
MERGE(m:Milestone{title_name:mstitle._text}) 
 
Some files dont have the "mstitle-field", so I got the error: 

Neo.ClientError.Statement.SemanticError

Cannot merge the following node because of null property value for 'title_name': (:Period {title_name: null})
 
16 REPLIES 16

busymo16
Ninja
Ninja

Hi @dionisios95 

Yes, you can either check it with a where condition or by using apoc.do.when like the following:

  1. Where condition
    • WHERE root.title_name IS NOT NULL
      MERGE(m:Milestone{title_name:mstitle._text}) 
  2. With apoc.do.when
    • CALL apoc.do.when( root.title_name IS NOT NULL,
      " MERGE(m:Milestone{title_name:mstitle._text}) 
      RETURN 'created' ",
      " RETURN True ",
      {root : root}
      ) YIELD value

I hope this solves your question.

Thx for your help, but unfortunately it does not work if I have multiple merge. 

To option1: I have to put the where`s in separatly for each merge, but it doenst work with "{}" or "," and with the where -> merge ->where order it also does not work. 

.......

with [in title where x._type = 'title'][0] as title,
     [in nctid where x._type = 'nct_id'][0] as nctid, 
     [in mstitle where x._type = 'title'][0] as mstitle, 
     [in participantslist where x._type = 'participants'][1] as participants
WHERE(title IS NOT NULL)
MERGE(p:Period{title_name: title._text})
WHERE(mstitle IS NOT NULL)  // Invalid input WHERE
To option 2: 
I had to do it with "as" because otherwise I have multiple value-var's. Also it seems a bit too complicated with apoc.do.when, Because I have more merge-lines after the CALL apoc-part I had to merge plane nodes and put the apoc-part as my last lines to avoid issues,  
The Code:
......
CALL apoc.do.when( title IS NOT NULL, "MERGE(p{title_name: title._text}) RETURN 'created' ", " RETURN True ",{}) YIELD value as v1 //True wenn null
CALL apoc.do.when( mstitle IS NOT NULL, "MERGE(m{title_name: mstitle._text}) RETURN 'created' ", "RETURN True",{}) YIELD value as v2  
 
Error: 
Query cannot conclude with CALL together with YIELD (line 76, column 1 (offset: 3360))
"CALL apoc.do.when( mstitle IS NOT NULL, "MERGE(m{title_name: mstitle._text}) RETURN 'created' ", "RETURN True",{}) YIELD value2 as v2"
 
 

  

If you have a sequence of 'merge' statements that you need to individually test for, you can do it by wrapping each merge in a 'call' subquery. The following is an example, where the first merge is not executed, but the second one is. This only works if the subqueries do not return a value. The 'double with' syntax is odd, but it gets around the fact that you can only import simple statements in a 'call' subquery, thus the first 'with' is 'simple' and the second 'with' can have a 'where' clause after it.

 

with null as x, 'a' as y
call {
    with x
    with x
    where not x is null
    merge(a:Test {name:'null'}) 
}
call {
    with y
    with y
    where y = 'a'
    merge(a:Test {name:'a'})
}

Thank you very much, but it seems not allright. I put this subquery-part at the end, because I have more other MERGE-parts. 

My Code: 

.......

MERGE(t:Trial{nct_id:nctid._text}) 
merge(pa:Participants)
merge(p:Period)
merge(m:Milestone)
.........
with null as empty, as t, p as p, pa as pa, m as m, title._text as ttitle, mstitle._text as tmstitle, participants.group_id as tgroupid, participants.count as tcount
call {
    with empty
    with empty
    where not empty is null
    merge({title_name:'null'}) 
    merge(m{title_name: 'null'})
    merge(pa{group_id: 'null', count: 'null'})
}
call {
    with ttitle
    with ttitle
    where ttitle IS NOT NULL
    merge({title_name: ttitle})
    merge (t)-[:CONTAIN]->(p) 
}
.....
The execute is working and the return of my m,p,pa and t after the WITH shows me the rigth nodes, but the merging inside the call seems not working, because it creates new nodes, while I`m trying to use the m,p,pa and t from above. 
 

You need to import existing nodes that you want to use in a subquery. In your second subquery, you refer to ‘t’. Since ‘t’ is not defined in the subquery, it creates a new node and binds it to variable ‘t’. Add ‘t’ to the with statements to import it into the subquery, so it will use it instead of creating a new node. 

There is a t as t  definition in the WITH-part and t is known outside, because I need the t also on the outside of the query for other merge-parts. Unfortunately I will get: 

(new unlabeled Node) -[:CONTAINS]-> (unknown labeled Node with right propertie-key-values)

Although I am getting the right Trial-Node at return after the WITH. 

You just need to import the value of 't' into the 'call' subquery by including it in the subquery's 'with' statements. 

I can help if you provide the entire query.

ameyasoft
Graph Maven

Try this:

[in mstitle where x._type = COALESCE('title'][0], 'NA')  as mstitle

 

I've tried that: 

...

with [in title where x._type = COALESCE('title','NA')] as title,
         [in mstitle where x._type = COALESCE('title','NA')] as mstitle,
MERGE(p:Period{title_name: title[0]._text})  
MERGE(m:Milestone{title_name: mstitle[0]._text}) 
....
Then I imported an xml-file that doesn't have these attributes (no Period, no Milestone) and unfortunately my Error is : 
Cannot merge the following node because of null property value for 'title_name': (:Period {title_name: null})
My goal is to ignore it then. 
 
 

How about ignoreME in CASE WHEN?

FOREACH (ignoreMe in CASE WHEN title IS NOT NULL then [1] ELSE [] END | MERGE (p:Period {title_name:title._text}) SET p.otherproperty=title._othervalue)

This one works on this code without an Error on both files well, but...: 

.....

with [in title where x._type = 'title'][0] as title
MERGE(t:Trial{nct_id:nctid._text}) 
MERGE(p:Period)
FOREACH (ignoreMe in CASE WHEN title IS NOT NULL then [1] ELSE [] END | MERGE (p{title_name:title._text}))
return p 
Output shows, that the FOREACH-Case does not merge the Node-Properties well. I need the "p" for the further use. 
{
  "identity": 362,
  "labels": [
    "Period"
  ],
  "properties": {

  }
}

Try the MERGE (p:Period) INSIDE the FOREACH clause, not before it.
If you need to do some modifications with (p:Period) AFTER the FOREACH clause you could follow-up with an OPTIONAL MATCH (p:Period {title_name:title._text}) then perform additional matches/property modifications..

 

FOREACH (ignoreMe in CASE WHEN title IS NOT NULL then [1] ELSE [] END | MERGE (p:Period {title_name:title._text}) SET p.otherproperty=title._othervalue)
OPTIONAL MATCH (p:Period {title_name:title._text})
... Other MATCH/RELATIONSHIP or PROPERTY work with p:Period goes here

 

THE FOREACH will only run when it is NOT NULL, but it won't stop execution because of NULL.
The OPTIONAL MATCH will MATCH p IF it exists, but again, will continue execution even if it doesn't.
The second MATCH does create some inefficiency (you are doing a MERGE and a MATCH on the same node) - so not sure if volume of data is an issue for you here.

I tried: 

.........

with [in title where x._type = 'title'][0] as title,
         [in nctid where x._type = 'nct_id'][0] as nctid
MERGE(t:Trial{nct_id:nctid._text})
FOREACH(ignoreMe in CASE WHEN title IS NOT NULL then [1] ELSE [] END | MERGE(p:Period{title_name:title._text}))
with title as title, nctid as nctid     // won't work without 
OPTIONAL MATCH (p:Period{title_name:title._text})
MERGE (t)-[:CONTAIN]->(p)    //Error on this line 
This Merge-line is just an example, it won't work with the "p" 

Error-message: 
Failed to create relationship `  UNNAMED0`, node `  p@41` is missing. If you prefer to simply ignore rows where a relationship node is missing, set 'cypher.lenient_create_relationship = true' in neo4j.conf

Because you are using OPTIONAL MATCH you have to protect against merging a null value - so the relationship would have to be:

FOREACH(ignoreMe in CASE WHEN p.title_name IS NOT NULL then [1] ELSE [] END | MERGE (t)-[:CONTAIN]->(p))

Also - unless this doesn't work with your logic, I'd do as much as you can within  the first FOREACH case to keep it clean like this:

with [x in title where x._type = 'title'][0] as title,
         [x in nctid where x._type = 'nct_id'][0] as nctid
MERGE(t:Trial{nct_id:nctid._text})
FOREACH(ignoreMe in CASE WHEN title IS NOT NULL then [1] ELSE [] END | MERGE(p:Period{title_name:title._text}) MERGE (t)-[:CONTAIN]->(p)   )
with t,title, nctid   // You are Correct - I forgot this in my example - MATCH after a MERGE always needs WITH to bring along any variables you need
OPTIONAL MATCH (p:Period{title_name:title._text})
... Now do other stuff

As @pdrangeid suggested, you should do all you can within the call/foreach clause, so you don't have to worry about retrieving the node later and then also checking if returned as 'null'.  

with [x in title where x._type = 'title'][0] as title,
     [x in nctid where x._type = 'nct_id'][0] as nctid
MERGE(t:Trial{nct_id:nctid._text})
call {
    with title, t
    with title, t
    WHEN title IS NOT NULL 
    MERGE(p:Period{title_name:title._text}) 
    MERGE (t)-[:CONTAIN]->(p)
    //finish your use of 'p' within the 'call' clause
}

dionisios95
Node Clone

Ok I think it worked with multiple times of foreach in foreach. I just had to think about the positions of the merge's: 

FOREACH(ignoreMe in CASE WHEN title IS NOT NULL then [1] ELSE [] END |
MERGE(.....)
          FOREACH(ignoreMe in CASE WHEN mstitle IS NOT NULL then [1] ELSE [] END | 
          MERGE(....) 
                  .......................
))