Create Relationship between each Node in Array while using LOAD CSV

Question

I have a given CSV file:

1,2016-10-09T21:14:44Z,Anna Long,"[{"_id": "1", "objectType": "primaryTag"}]"
2,2016-10-10T14:03:51Z,Jonathan Collier,"[{"_id": "2", "objectType": "primaryTag"}, {"_id": "3", "objectType": "secondaryTag"}]"
3,2016-10-09T21:42:35Z,Michelle Wheeler,"[{"_id": "4", "objectType": "primaryTag"}, {"_id": "5", "objectType": "secondaryTag"}, {"_id": "6", "objectType": "secondaryTag"}]"

Now I try to create Nodes and Relationships from the JSON array within the CSV file. I can import the CSV file with this command:

LOAD CSV FROM file:///<my_file> AS row

Also, I am able to get the JSON array with this query:

WITH apoc.convert.fromJsonList(row[3]) AS tags

Now I want to create a relationship between each tag in a JSON array for each one of the rows in the CSV file respectively.

So as an example, let us take the third line from the CSV file. We have 3 objects in the JSON array, therefore I would like to create 3 relationships. A relationship between the tags with the id 4&5, 4&6, and 5&6.

Sadly I am completely stuck here and not sure if I have to work with UNWIND or FOREACH.

EDIT

I solved the problem as followed (all relevant nodes are already imported in the database):

LOAD CSV FROM file:///<my_file> AS row 
MATCH (q:Question {id: row[0]})--(t1:Tag)
WITH COLLECT(t1) AS tags, row[0] AS question_id
FOREACH (i IN range(0, size(tags) - 2) |
    FOREACH (node1 IN [tags[i]] | 
        FOREACH (j IN range(i+1, size(tags) - 1) | 
            FOREACH (node2 IN [tags[j]] | 
                MERGE (node1)-[c:CONNECTED_TO]-(node2)
            )
        )
    )
)

The code works but it has 4 FOREACH loops. I still believe that there is a more beautiful approach. So feel free to answer my question.

InverseFalcon · Accepted Answer · 2020-06-08T21:14:57.907

The first thing to realize is that you do not yet have nodes to work with, you only have objects. You need to convert (MATCH, MERGE, or CREATE) nodes associated with these.

Let's take the simplest case, creating new nodes every time, and setting all the properties

LOAD CSV FROM file:///<my_file> AS row 
WITH apoc.convert.fromJsonList(row[3]) AS tags
UNWIND tags as tag
CREATE (t:Tag)
SET t = tag // assigns map properties to node properties
WITH row, collect(t) as tagNodes
CALL apoc.nodes.link(tagNodes, 'REL')
RETURN count(*)

I'm using apoc.nodes.link() from APOC Procedures to easily create relationships between all the nodes in the list. But if you don't have APOC or can't use it, you will have to use some alternate approach.

Please see this knowledge base article on creating and working with linked lists in Cypher

EDIT

So I missed the part about you wanting to create relationships between all combinations of nodes, not just a linked list through them all.

We can use apoc.coll.combinations() to get every combination of 2 nodes in the list, and create relationships between them:

LOAD CSV FROM 'file:///<my_file>' as row
WITH row, apoc.convert.fromJsonList(row[3]) AS tags
UNWIND tags as tag
CREATE (t:Tag)
SET t = tag // assigns map properties to node properties
WITH row, collect(t) as tagNodes
UNWIND apoc.coll.combinations(tagNodes, 2) as pair
WITH pair[0] as start, pair[1] as end
CREATE (start)-[:REL]->(end)

Thank you for your fast answer. I really appreciate it. Sadly this approach only creates linked lists. To refer to my example in my question, it links 4&5 and 5&6 but not 4&6. — Moritz Kampfinger, Jun 08 '20 at 09:28
So the idea is to get all combinations of the nodes and create relationships between them? — InverseFalcon, Jun 08 '20 at 15:15
To get all combinations of every node for each row in the CSV file and create relationships between them. I wrote a crazy looking cypher query and it is working but it looks like hell (see my edit in the question). Btw, your link was helping me a lot to solve this, but I still believe that there is a more elegant way. — Moritz Kampfinger, Jun 08 '20 at 15:24
Got it, updated my answer with an alternate approach using `apoc.coll.combinations()` — InverseFalcon, Jun 08 '20 at 21:15

Create Relationship between each Node in Array while using LOAD CSV

1 Answers1