Neo4J Cypher query creates more links than expected

Question

In the following query (which I modified from one I found on the net). I am using APOC to create 14 nodes, and what I though would be 12 edges (connecting each of the nodes in two rows). I.e. if you take 14 nodes, and put then in two columns of 7 items each, you need only 6 lines to connect them together.

Anyway, the code is using APOC to read the JSON file, and it is creating the nodes exactly as expected. But I am getting 79 edges. There must be something about either CYPHER or UNWIND I don't understand.

You can clearly see that in the JSON file, I only have 12 objects in the pipes array.

CALL apoc.load.json("file:///G:/wwwroot/DataFlow/graph.json") YIELD value AS row
WITH row, row.graph.nodes AS nodes
UNWIND nodes AS node
CALL apoc.create.node(node.labels, node.properties) YIELD node AS n
WITH row
UNWIND row.graph.pipes AS rel
MATCH (a) WHERE a.key = rel.start
MATCH (b) WHERE b.key = rel.end
CALL apoc.create.relationship(a, rel.type, rel.properties, b) YIELD rel AS r
RETURN *

{
    "graph" : {
        "nodes" : [{
                "id" : "HW.SCIM",
                "labels" : [
                    "Actor"
                ],
                "properties" : {
                    "key" : "HW.SCIM",
                    "partition" : "Hardware",
                    "CSC" : "HW",
                    "name" : "SCIM",
                    "rate" : 10,
                    "caption" : "SCIM"
                }
            }, {
                "id" : "HW.GPS",
                "labels" : [
                    "Actor"
                ],
                "properties" : {
                    "key" : "HW.GPS",
                    "partition" : "Hardware",
                    "CSC" : "HW",
                    "name" : "GPS",
                    "rate" : 50,
                    "caption" : "GPS"
                }
            }, {
                "id" : "HW.SCIM-Channel",
                "labels" : [
                    "Channel"
                ],
                "properties" : {
                    "key" : "HW.SCIM-Channel",
                    "wordLength" : 300,
                    "channelType" : "Partition",
                    "timeStamp" : "",
                    "writer" : ""
                }
            }, {
                "id" : "HW.GPS-Channel",
                "labels" : [
                    "Channel"
                ],
                "properties" : {
                    "key" : "HW.GPS-Channel",
                    "wordLength" : 200,
                    "channelType" : "Partition",
                    "timeStamp" : "",
                    "writer" : ""
                }
            }, {
                "id" : "Platform.SCIM",
                "labels" : [
                    "Actor"
                ],
                "properties" : {
                    "key" : "Platform.SCIM",
                    "partition" : "Platform",
                    "CSC" : "Platform",
                    "name" : "SCIM",
                    "rate" : 10,
                    "caption" : "Platform SCIM"
                }
            }, {
                "id" : "Platform.GPS",
                "labels" : [
                    "Actor"
                ],
                "properties" : {
                    "key" : "Platform.GPS",
                    "partition" : "Platform",
                    "CSC" : "Platform",
                    "name" : "GPS",
                    "rate" : 50,
                    "caption" : "Platform GPS"
                }
            }, {
                "id" : "Platform.SCIM-Channel",
                "labels" : [
                    "Channel"
                ],
                "properties" : {
                    "key" : "Platform.SCIM-Channel",
                    "wordLength" : 300,
                    "channelType" : "Partition",
                    "timeStamp" : "",
                    "writer" : ""
                }
            }, {
                "id" : "Platform.GPS-Channel",
                "labels" : [
                    "Channel"
                ],
                "properties" : {
                    "key" : "Platform.GPS-Channel",
                    "wordLength" : 200,
                    "channelType" : "Partition",
                    "timeStamp" : "",
                    "writer" : ""
                }
            }, {
                "id" : "ALFSW.SCIM",
                "labels" : [
                    "Actor"
                ],
                "properties" : {
                    "key" : "ALFSW.SCIM",
                    "partition" : "Application",
                    "CSC" : "Application",
                    "name" : "SCIM",
                    "rate" : 10,
                    "caption" : "App SCIM"
                }
            }, {
                "id" : "ALFSW.GPS",
                "labels" : [
                    "Actor"
                ],
                "properties" : {
                    "key" : "ALFSW.GPS",
                    "partition" : "Application",
                    "CSC" : "Application",
                    "name" : "GPS",
                    "rate" : 50,
                    "caption" : "App GPS"
                }
            }, {
                "id" : "ALFSW.SCIM-Channel",
                "labels" : [
                    "Channel"
                ],
                "properties" : {
                    "key" : "ALFSW.SCIM-Channel",
                    "wordLength" : 300,
                    "channelType" : "Partition",
                    "timeStamp" : "",
                    "writer" : ""
                }
            }, {
                "id" : "ALFSW.GPS-Channel",
                "labels" : [
                    "Channel"
                ],
                "properties" : {
                    "key" : "ALFSW.GPS-Channel",
                    "wordLength" : 200,
                    "channelType" : "Partition",
                    "timeStamp" : "",
                    "writer" : ""
                }
            }, {
                "id" : "GNC.SCIM",
                "labels" : [
                    "Actor"
                ],
                "properties" : {
                    "key" : "GNC.SCIM",
                    "partition" : "GNC",
                    "CSC" : "MediumRate",
                    "name" : "SCIM",
                    "rate" : 10,
                    "caption" : "GNC Medium Rate"
                }
            }, {
                "id" : "GNC.GPS",
                "labels" : [
                    "Actor"
                ],
                "properties" : {
                    "key" : "GNC.GPS",
                    "partition" : "GNC",
                    "CSC" : "HighRate",
                    "name" : "GPS",
                    "rate" : 50,
                    "caption" : "GNC High Rate"
                }
            }
        ],
        "pipes" : [{
                "type" : "Pipe",
                "start" : "HW.SCIM",
                "end" : "HW.SCIM-Channel",
                "properties" : {
                    "flow" : "OUT"
                }
            }, {
                "type" : "Pipe",
                "start" : "HW.GPS",
                "end" : "HW.GPS-Channel",
                "properties" : {
                    "flow" : "OUT"
                }
            }, {
                "type" : "Pipe",
                "start" : "Platform.SCIM",
                "end" :  "HW.SCIM-Channel",
                "properties" : {
                    "flow" : "IN"
                }
            }, {
                "type" : "Pipe",
                "start" : "Platform.GPS",
                "end" : "HW.GPS-Channel",
                "properties" : {
                    "flow" : "IN"
                }
            }, {
                "type" : "Pipe",
                "start" : "Platform.SCIM",
                "end" : "Platform.SCIM-Channel",
                "properties" : {
                    "flow" : "OUT"
                }
            }, {
                "type" : "Pipe",
                "start" : "Platform.GPS",
                "end" : "Platform.GPS-Channel",
                "properties" : {
                    "flow" : "OUT"
                }
            }, {
                "type" : "Pipe",
                "start" : "ALFSW.SCIM",
                "end" :  "Platform.SCIM-Channel",
                "properties" : {
                    "flow" : "IN"
                }
            }, {
                "type" : "Pipe",
                "start" : "ALFSW.GPS",
                "end" :  "Platform.GPS-Channel",
                "properties" : {
                    "flow" : "IN"
                }
            }, {
                "type" : "Pipe",
                "start" : "ALFSW.SCIM",
                "end" : "ALFSW.SCIM-Channel",
                "properties" : {
                    "flow" : "OUT"
                }
            }, {
                "type" : "Pipe",
                "start" : "ALFSW.GPS",
                "end" : "ALFSW.GPS-Channel",
                "properties" : {
                    "flow" : "OUT"
                }
            }, {
                "type" : "Pipe",
                "start" : "GNC.SCIM", 
                "end" : "ALFSW.SCIM-Channel",
                "properties" : {
                    "flow" : "IN"
                }
            }, {
                "type" : "Pipe",
                "start" : "GNC.GPS",
                "end" :  "ALFSW.GPS-Channel",
                "properties" : {
                    "flow" : "IN"
                }
            }
        ]
    }
}

score 3 · Accepted Answer · answered Feb 28 '17 at 23:11

3

CALL apoc.load.json("file:///G:/wwwroot/DataFlow/graph.json") YIELD value AS row
UNWIND row.graph.nodes AS node
CALL apoc.create.node(node.labels, node.properties) YIELD node AS n
// aggregation or distinct reduces cardinality to 1 per row again
WITH row, count(*) 
UNWIND row.graph.pipes AS rel
// you should use labels here, otherwise you get no lookup benefits 
// from indexes, even if you just use a generic :Node label
MATCH (a) WHERE a.key = rel.start
MATCH (b) WHERE b.key = rel.end
CALL apoc.create.relationship(a, rel.type, rel.properties, b) YIELD rel AS r
RETURN *

answered Feb 28 '17 at 23:11

Michael Hunger

41,339
3
57
80

FYI: Expression in WITH must be aliased (use AS) (line 4, column 11 (offset: 190)) "WITH row, count(*) " – Dr.YSG Mar 01 '17 at 14:20
I think I understand this, But let me see if I understand your comments by restating them the way I hear them:. 1. by adding the count(*) your are in effect doing a distinct and ending the unwind as in: http://stackoverflow.com/questions/30744331/end-unwind-statement-in-a-cypher-query – Dr.YSG Mar 01 '17 at 14:22
2, You are suggesting adding a Label to both the :Actor and :Pipe Labels so that they are both :Actor:Node and :Pipe:Node and this will automaticaly add an index for the key property, or does that still need to be added? – Dr.YSG Mar 01 '17 at 14:24

Tomaž Bratanič · Answer 2 · 2017-02-28T22:36:34.313

I think that is because you have UNWIND inside of UNWIND so it goes through second unwind x times where x is value of size of first array. Like having a FOR loop inside of a FOR loop.Try splitting it up into two queries.

CALL apoc.load.json("file:///G:/wwwroot/DataFlow/graph.json") YIELD value AS row
WITH row, row.graph.nodes AS nodes
UNWIND nodes AS node
CALL apoc.create.node(node.labels, node.properties) YIELD node AS n
RETURN *



CALL apoc.load.json("file:///G:/wwwroot/DataFlow/graph.json") YIELD value AS row
UNWIND row.graph.pipes AS rel
MATCH (a) WHERE a.key = rel.start
MATCH (b) WHERE b.key = rel.end
CALL apoc.create.relationship(a, rel.type, rel.properties, b) YIELD rel AS r
RETURN *

edit: in one step // not working

CALL apoc.load.json("file:///G:/wwwroot/DataFlow/graph.json") YIELD value AS row
WITH row, row.graph.nodes AS nodes,row.graph.pipes AS rel
FOREACH  (node in nodes |
CALL apoc.create.node(node.labels, node.properties) YIELD node AS n)
WITH row,rel
UNWIND row.graph.pipes AS rel
MATCH (a) WHERE a.key = rel.start
MATCH (b) WHERE b.key = rel.end
CALL apoc.create.relationship(a, rel.type, rel.properties, b) YIELD rel AS r
RETURN *

Yes, that works. But is there a CYPHER wizard that can tell me how to do this in one step? — Dr.YSG, Feb 28 '17 at 21:02
Invalid use of CALL inside FOREACH (line 4, column 1 (offset: 165)) "CALL apoc.create.node(node.labels, node.properties) YIELD node AS n)" ^ — Dr.YSG, Feb 28 '17 at 21:13

Neo4J Cypher query creates more links than expected

2 Answers2