ON MATCH and ON CREATE only creating 1 node

[mbandor]

I have some Cypher code (test code) that uses APOC to load an Excel file and then process each row. What I want to do is this:

call apoc.load.xls('Test File.xlsx', 'Company',
// the file has a header row
{header:true})
yield map as row

with row
MERGE (c:Company)
ON CREATE
SET
c.name = coalesce(ltrim(rtrim(toString(row.Company))), "UKNOWN"),
c.created = datetime()
ON MATCH
SET c.lastUpdated = datetime()
RETURN c

The problem is I only get 1 node, but have 5 distinct rows of data. It seems to be only processing the first row in the Excel file. Any ideas?

[Cobra]

Hello @mbandor

It is normal because you didn't specify an id property in the MERGE clause so your query always MERGE on the same node. Do you have a unique id for each row?

Regards,
Cobra

[mbandor]

No. Each row may or may not have the same information I'm looking for, hence the need to split out the ON MATCH vs. ON CREATE function. If I don't use those, the script will iterate through each row just fine so I'm a little confused about how to proceed.

[Cobra]

Share you data please

[mbandor]

Company
Lockheed
Raytheon
Microsoft
Red Hat
Symantec

[Cobra]

MERGE (c:Company {name: coalesce(ltrim(rtrim(toString(row.`Company`))), "UKNOWN"))
ON CREATE
SET c.created = datetime()
ON MATCH
SET c.lastUpdated = datetime()
RETURN c

[mbandor]

Ah, so the name property (id) still needs to be in the merge statement. That is where I messed up. Thanks for clarifying.

[mbandor]

One follow up question. For another node, there are multiple properties but the name property has a constraint. Should I only use the name property as the id and then set the others using the ON MATCH and ON CREATE?

[Cobra · Accepted solution]

Yes, exactly, you should only put in the MERGE clause the property that carry the constraint or the properties if you have a composite constraint.