Ignore-function for null property values !?

Neo4j Graph Platform
1

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

2

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.

3

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"

4

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

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.

6

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.

7

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.

8

Try this:

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

9

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.

10

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

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": {

}
}

12

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.

13

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.

14

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

15

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
16

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

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

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

))