Neo4j Cypher manual relationship index, APOC trigger and data duplication 2

Question

Continuing with the following question Neo4j Cypher manual relationship index, APOC trigger and data duplication I have created the scenario which reproduces the issue:

CALL apoc.trigger.add('TEST_TRIGGER', "UNWIND keys({assignedRelationshipProperties}) AS key 
UNWIND {assignedRelationshipProperties}[key] AS map 
WITH map 
WHERE type(map.relationship) = 'LIVES_IN' 
CALL apoc.index.addRelationship(map.relationship, keys(map.relationship)) 
RETURN count(*)", {phase:'before'})

CREATE (p:Person) SET p.id = 1 return p
CREATE (p:Person) SET p.id = 2 return p
CREATE (c:City) return c

MATCH (p:Person), (c:City) WHERE p.id = 1 CREATE (p)-[r:LIVES_IN]->(c) SET r.time = 10 RETURN type(r)
MATCH (p:Person), (c:City) WHERE p.id = 2 CREATE (p)-[r:LIVES_IN]->(c) SET r.time = 20 RETURN type(r)

Now, let's try to select the person with r.time = 10:

MATCH (p:Person)-[r:LIVES_IN]->(c:City) 
CALL apoc.index.in(c, 'LIVES_IN', 'time:10') YIELD node AS person 
RETURN person

The query above correctly returns only one node.

Now, let's do the same but return the person count:

MATCH (p:Person)-[r:LIVES_IN]->(c:City) 
CALL apoc.index.in(c, 'LIVES_IN', 'time:10') YIELD node AS person 
RETURN count(person)

The query above returns count = 2.

Why this query returns count = 2 instead of the single node?

Also, the following query:

MATCH (p:Person)-[r:LIVES_IN]->(c:City) 
CALL apoc.index.relationships('LIVES_IN', 'time:10') YIELD rel
RETURN rel

returns 2 relationships:

{
  "time": 10
}
{
  "time": 10
}

but I expect only the single one in the manual index where time = 10.

What am I doing wrong ?

stdob-- stdob-- · Accepted Answer · 2018-05-24T21:40:20

The first query in your example returns also two lines. Apparently you look at the result in the form of a graph. Try or switch to table mode, or change the query for this:

MATCH (p:Person)-[r:LIVES_IN]->(c:City) 
CALL apoc.index.in(c, 'LIVES_IN', 'time:10') YIELD node AS person 
RETURN ID(person)

Two lines are obtained because you have two people living in the same city and for each relationship you do a search on the index. Try this:

MATCH (p:Person)-[r:LIVES_IN]->(c:City)
WITH DISTINCT c
CALL apoc.index.in(c, 'LIVES_IN', 'time:10') YIELD node AS person 
RETURN COUNT(DISTINCT person)

Neo4j Cypher manual relationship index, APOC trigger and data duplication 2

2 Answers