Merge Nodes and Relationship

[rajeev_darla]

Hi All,
I'm new to Neo4j and trying to figure this out. Any help is appreciated:
Problem: Have two tables: 1) Systems 2) Users. Trying to load the two csv files and create relationships.
Systems table: System ID, System name, Owner, etc., (Ex: System1, SomeSystem, 'Jon Snow'
Users/Access table: System ID, Users, No. of users, etc.,(Ex: System1, (user1, user2, user3), 3)
The issue I'm having is when trying to split the users and create user node and connect via a relationships saying (user)-[:HAS_ACCESS)->(system), it's creating duplicate user nodes or relationship nodes:
Here's what I tries

:auto USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM "filepath" as element
MERGE (sys: System {SystemID = element.SystemID})
ON CREATE SET sys += element

LOAD CSV WITH HEADERS FROM "filepath" as usernode
MATCH (sys:System {SystemID:usernode.SystemID})
WITH SPLIT(usernode.UserID,", ") AS users
UNWIND range(0, size(users)-1) as i
MERGE (us:USER {UserID: users[i]})
ON CREATE SET us += usernode
CREATE (us)-[:HAS_ACCESS_TO]->(sys)

Please help

[MuddyBootsCode]

It looks like you're using MERGE correctly. Do you have unique ID constraints placed on your instance?

[rajeev_darla]

Yes I have defined unique constraints on System ID and User ID. I can't use Merge and on create in loading user node, it throws me an error saying usernode is not available, it has something to do with 'WITH' i believe

[MuddyBootsCode]

Ok. If possible, do you think you can copy and paste the complete error it's giving you? Also, are you running both of the upload from CSV operations at once? If so you may benefit from running them in separate operations and verifying that your nodes are being created in the way that you expect them to be.

[rajeev_darla]

I am running them separately and nodes are created. This is the error I'm getting. It has to do with 'WITH' clause, but I couldn't figure out how to resolve

[ameyasoft]

You should add usernode to WITH clause as:

WITH SPLIT(usernode.UserID,", ") AS users, user node

This should work,

[rajeev_darla]

Thank you, tried that as well. It gave very weird output:

[intouch_vivek]

Hi Rajeev,

Kindly correct your syntax as

1. Hope you are giving filepath correctly. Your csv shud be placed in <Neo4j_Home>/import folder and for an example file name is a.csv then
   LOAD CSV WITH HEADERS FROM "file:///a.csv" as element

2. MERGE (sys: System {SystemID = element.SystemID}), instead of equal sign use colon. = sign should be using in set as you have done

3. Also it is not not clear what are you trying with second load

[rajeev_darla]

Thank you Vivek.

1. Yes, I have the file path correct
2. Typed it wrong by mistake, in my code I have a colon :
3. Record 1 in my user file has 3 users (user1,user2,user3) who all are accessing system1, so I'm trying to split that column and build relationship so that each user has access to system1. If Record2 has 2 users (user2,user4), I do not want to create a new node for user2 again hence the merge.

[intouch_vivek]

could you please provide sample data

[rajeev_darla]

Code: step1: :auto USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM "file:///System.csv" as element
MERGE (sys: System {SystemID : element.SystemID})
ON CREATE SET sys += element

Step2: LOAD CSV WITH HEADERS FROM "file:///Users.csv" as usernode
MATCH (sys:System {SystemID:usernode.SystemID})
WITH SPLIT(usernode.UserID,", ") AS users
UNWIND range(0, size(users)-1) as i
MERGE (us:USER {UserID: users[i]})
ON CREATE SET us += usernode
CREATE (us)-[:HAS_ACCESS_TO]->(sys)

Error:

[intouch_vivek]

rajeev.darla:

MATCH (sys:System {SystemID:usernode.SystemID})

try

LOAD CSV WITH HEADERS FROM "file:///System.csv" as element
MERGE (sys: System {id : element.SystemID})





LOAD CSV WITH HEADERS FROM "file:///Users.csv" as usernode
WITH SPLIT(usernode.UserID,"|") AS users,usernode
unwind users as usr
Merge(user:USER{id:usr})
with user,usernode
Match (sys:System {id:usernode.SystemID})
Create(user)-[:HAS_ACCESS_TO]->(sys)

[rajeev_darla]

intouch.vivek:

LOAD CSV WITH HEADERS FROM "file:///Users.csv" as usernode
WITH SPLIT(usernode.UserID,"|") AS users,usernode
unwind users as usr
Merge(user:USER{id:usr})
with user,usernode
Match (sys:System {id:usernode.SystemID})
Create(user)-[:HAS_ACCESS_TO]->(sys)

Thank you Vivke, I tried that, it didn't split the users into different nodes. This is the output:

[ameyasoft]

Try this:

LOAD CSV WITH HEADERS FROM "file:/system.csv" AS row
with row

merge (a:System {systemid: row.SystemID, systemname: row.SystemName, owner: row.Owner})

LOAD CSV WITH HEADERS FROM "file:/users.csv" AS row
with row

with split(row.UserID, ',') as u1, row
UNWIND range(0, size(u1)-1) as i

merge (u:User {id: u1[i], usercount: row.`No. of users`})
with u, row
match (s:System) where s.systemid = row.SystemID
merge (u)-[:HAS_ACCESS_To]->(s)

Result:

[rajeev_darla]

Thank you so much! This is amazing! I tweaked it a little bit to give the exact output I wanted:
//Execure in steps
//Step1
LOAD CSV WITH HEADERS FROM "file:///System.csv" as element
MERGE (sys: System {SystemID : element.SystemID})
ON CREATE SET sys += element

//Step2
LOAD CSV WITH HEADERS FROM "file:///users.csv" AS row
MATCH (sys:System {SystemID:row.SystemID})
with row

with split(row.UserID, ', ') as u1, row
UNWIND range(0, size(u1)-1) as i

merge (u:User {id: u1[i]})
on create set u += row
with u, row
match (sys:System) where sys.SystemID = row.SystemID
merge (u)-[:HAS_ACCESS_To]->(sys)

and Got my desired result:

Appreciate all your help!