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);

is this the value of $specifiedLocationIds = '[[5]]'? Can you use List instead of Set? — jose_bacoy, Aug 22 '22 at 13:56
this is just a plain Java Set. Also changed to the List.. unfortunately with the same result ( — brunoid, Aug 22 '22 at 14:00
'[[5]]' is the result from intersection. Also, when I change the query to the `RETURN (10) as output` it works fine. Hot to convert [5] to (5) ? — brunoid, Aug 22 '22 at 14:04
Is the error coming for the same set of parameters as the ones provided in the sample neo4j browser query? — Charchit Kapoor, Aug 22 '22 at 14:30
Okk, interesting there is no value 5 in the parameters and in the result — Charchit Kapoor, Aug 22 '22 at 14:33
oh.. no.. each time I run the test, I have the different IDs generated — brunoid, Aug 22 '22 at 14:33
Please see my updated question. If I change the return type to the Set - everything works fine.. — brunoid, Aug 22 '22 at 14:36

Charchit Kapoor · Accepted Answer · 2022-08-22T14:42:20.230

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

Ohk, then the query is good. What is the output of the whole query on Neo4j Browser (With intersection) and make sure that the params are same in both places. — Charchit Kapoor, Aug 22 '22 at 14:17

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

1 Answers1