Spring Data Neo4j OGM return apoc.coll.intersection from repository method

Question

I have the following Spring Data Neo4j OGM Repository method:

@Query("MATCH (root:Location) " +
        "WHERE root.id IN $locationIds " +
        "WITH root " +
        "OPTIONAL MATCH (root)-[:CONTAINS*0..]->(descendant:Location) " +
        "OPTIONAL MATCH (ascendant:Location)-[:CONTAINS*0..]->(root) " +
        "WITH COLLECT(root.id) AS listRoot, COLLECT( DISTINCT ascendant.id) AS listAscendant, COLLECT( DISTINCT descendant.id) AS listDescendant WITH apoc.coll.union(listDescendant, apoc.coll.union(listRoot, listAscendant)) AS dadLocationIds " +
        "WITH dadLocationIds " +
        "RETURN apoc.coll.intersection(dadLocationIds, $specifiedLocationIds) as output")
Set<Long> locationsHaveAnyDirectOrAscendantOrDescendantRelationshipsWithSpecifiedLocations(Set<Long> locationIds, Set<Long> specifiedLocationIds);

this method fails with the following exception:

org.springframework.core.convert.ConversionFailedException: Failed to convert from type [java.util.ArrayList<?>] to type [@org.springframework.data.neo4j.annotation.Query java.util.Set<java.lang.Long>] for value '[[5]]'; nested exception is org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [java.util.Collections$SingletonList<?>] to type [@org.springframework.data.neo4j.annotation.Query java.lang.Long]

How to properly return Set of ids for intersection?

UPDATED

MATCH (root:Location) 
            WHERE root.id IN [61]
            WITH root 
            OPTIONAL MATCH (root)-[:CONTAINS*0..]->(descendant:Location) 
            OPTIONAL MATCH (ascendant:Location)-[:CONTAINS*0..]->(root) 
            WITH COLLECT(root.id) AS listRoot, COLLECT(DISTINCT ascendant.id) AS listAscendant, COLLECT(DISTINCT descendant.id) AS listDescendant 
            WITH listDescendant + listRoot + listAscendant AS dadLocationIds
            WITH dadLocationIds 
            WITH apoc.coll.intersection(dadLocationIds, [60, 58]) as output
            RETURN output

and output from Neo4j browser:

╒════════╕
│"output"│
╞════════╡
│[60]    │
└────────┘

UPDATED 1

The query works fine when I change it to the following:

 "WITH apoc.coll.intersection(dadLocationIds, $specifiedLocationIds) as intersectionIds " +
        "MATCH (l:Location) WHERE l.id IN intersectionIds " +
        "RETURN l")
Set<Location> locationsHaveAnyDirectOrAscendantOrDescendantRelationshipsWithSpecifiedLocations(Set<Long> locationIds, Set<Long> specifiedLocationIds);

Answer 1

Your query is returning List<List<Long>> I think, most probably because of the line:

WITH COLLECT(root.id) AS listRoot, COLLECT( DISTINCT ascendant.id) AS listAscendant, COLLECT( DISTINCT descendant.id) AS listDescendant WITH apoc.coll.union(listDescendant, apoc.coll.union(listRoot, listAscendant)) AS dadLocationIds 

This line might be causing multiple lists to get created, for each one the intersection gets performed, and hence the final output becomes List<List<Long>>. Try running this on Neo4j Browser, to see if this is true or not:

 MATCH (root:Location) 
 WHERE root.id IN $locationIds
 WITH root 
 OPTIONAL MATCH (root)-[:CONTAINS*0..]->(descendant:Location)
 OPTIONAL MATCH (ascendant:Location)-[:CONTAINS*0..]->(root)
 WITH COLLECT(root.id) AS listRoot, COLLECT( DISTINCT ascendant.id) AS listAscendant, COLLECT( DISTINCT descendant.id) AS listDescendant
 RETURN apoc.coll.union(listDescendant, apoc.coll.union(listRoot, listAscendant)) AS dadLocationIds 

If there are multiple lists in the output, for the parameters that you tried with. Then the query needs to be fixed.

Update:

I got it. When you specify return type as Set<Long>, Spring is expecting neo4j to return Long values, which it will place in a set. However, when you return apoc.coll.intersection output, it tries to put that List<Long> into a Set<Long>, due to which the error comes up. To fix this, you can simply UNWIND the list from apoc.coll.intersection and return it. Like this:

MATCH (root:Location) 
WHERE root.id IN [61]
WITH root 
OPTIONAL MATCH (root)-[:CONTAINS*0..]->(descendant:Location) 
OPTIONAL MATCH (ascendant:Location)-[:CONTAINS*0..]->(root) 
WITH COLLECT(root.id) AS listRoot, COLLECT(DISTINCT ascendant.id) AS listAscendant, COLLECT(DISTINCT descendant.id) AS listDescendant 
WITH listDescendant + listRoot + listAscendant AS dadLocationIds
WITH dadLocationIds 
WITH apoc.coll.intersection(dadLocationIds, [60, 58]) as output
UNWIND output AS locationId
RETURN locationId