neo4j load csv invalid "ON MATCH"

158 views Asked by At

I'm loading a file using the LOAD CSV command,and i have a strange situation, when a new row gets a propery added in the "on match" block.

here is the code:

USING PERIODIC COMMIT 10000 LOAD CSV WITH HEADERS FROM 'file://1.csv'     
AS line 
FIELDTERMINATOR '|'

WITH line, split(line.list_ites, ',') AS items UNWIND items AS _items 

MERGE (session :Session { wz_session:line.session }) 
ON CREATE SET session.created = timestamp(),session.batch_id='60893068766' 
ON MATCH SET session.updated = timestamp() 

MERGE (hit :Hit { id:line.hit_id,date_time:TOINT(line.date_time) }) 
ON CREATE SET hit.created = timestamp() 
ON MATCH SET hit.updated = timestamp()

 FOREACH(q IN (CASE WHEN trim(line.list_ites) <> '&' THEN _items ELSE [] END) |   //cypher doesn't have IF
            MERGE (i:ListItems {key: q,name:split(q,'=')[0],value: split(q,'=')[1]}) 
            CREATE (hit)-[:WITH_QUERY]->(i)
         )

now this line should never happen

ON MATCH SET hit.updated = timestamp()

as the hit_id is unique, and yet- i see nodes from type Hit with updated=xxx (i've verified with the file that ids of these nodes appear once using grep [hit_id] 1.csv)

im pretty sure there is something wrong with this line:

split(line.list_ites, ',') AS items UNWIND items AS _items 

or with the loop

help with we appritiated,

Lior

UPDATE:

I removed this line:

 split(line.list_ites, ',') AS items UNWIND items AS _items 

and also the foreach loop, and indeed i dont see any "updated" field with value. still i must fix it, cause i can't really remove it from the final code

1

There are 1 answers

1
Michael Hunger On BEST ANSWER

this is because it merges not only on hit_id but also on date_time as you specified both in the MERGE clause

you should change it to:

MERGE (hit :Hit { id:line.hit_id }) 
ON CREATE SET hit.created = timestamp(), hit.date_time=TOINT(line.date_time)
ON MATCH SET hit.updated = timestamp()

Update

It does not really make sense what you do with _items. Esp as you pass it to foreach as collection while it can't be a collection.

Perhaps use an inner foreach loop:

USING PERIODIC COMMIT 10000 LOAD CSV WITH HEADERS FROM 'file://1.csv'     
AS line FIELDTERMINATOR '|'

MERGE (session :Session { wz_session:line.session }) 
ON CREATE SET session.created = timestamp(),session.batch_id='60893068766' 
ON MATCH SET session.updated = timestamp() 

MERGE (hit :Hit { id:line.hit_id,date_time:TOINT(line.date_time) }) 
ON CREATE SET hit.created = timestamp() 
ON MATCH SET hit.updated = timestamp()

FOREACH (_items IN split(line.list_ites, ',') |
  FOREACH(q IN (CASE WHEN trim(line.list_ites) <> '&' THEN _items ELSE [] END) |               
    MERGE (i:ListItems {key: q, name:split(q,'=')[0], value: split(q,'=')[1]}) 
    CREATE (hit)-[:WITH_QUERY]->(i)
  )
)

But better would be to move the unwind to the end:

...
WHERE trim(line.list_ites) <> '&'
UNWIND split(line.list_ites, ',') AS _items
UNWIND _items as q
WITH q,split(q,'=') as parts, hit
MERGE (i:ListItems {key: q}) ON CREATE SET i.name=parts[0], i.value=parts[1]
CREATE (hit)-[:WITH_QUERY]->(i)