Ignore-function for null property values !?

[dionisios95]

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 [x in title where x._type = 'title'][0] as title,

     [x in nctid where x._type = 'nct_id'][0] as nctid, 

     [x in mstitle where x._type = 'title'][0] as mstitle, 

     [x 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})

[busymo16]

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.

[dionisios95]

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 [x in title where x._type = 'title'][0] as title,

     [x in nctid where x._type = 'nct_id'][0] as nctid, 

     [x in mstitle where x._type = 'title'][0] as mstitle, 

     [x 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"

[glilienfield]

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'})
}

[dionisios95]

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, t 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(p {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(p {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. 

[glilienfield]

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. 

[dionisios95]

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. 

[glilienfield]

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]

Try this:

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

[dionisios95]

I've tried that: 

...

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

         [x 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. 

 

[pdrangeid]

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)

[dionisios95]

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

.....

with [x 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": {

  }
}

[pdrangeid]

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.

[dionisios95]

I tried: 

.........

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}))

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

[pdrangeid]

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

[glilienfield]

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]

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(....) 

                  .......................

))