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.

Answer 1

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)