Issue
My db contains:
- 1 Game (g)
- 2 Expansions (e)
- 2 Categories (c)
When executing the below query in the browser client, I get back exactly what I'd expect. However, using the Java bolt driver, I get odd results.
final var query = """
MATCH (g:Game {name:$name})-[:HAS]->(e:Game),
(g)-[:HAS]->(c:Category)
RETURN DISTINCT g,
COLLECT(e) AS expansions,
COLLECT(c) AS categories
""";
try (var session = driver.session()) {
return session.readTransaction(tx -> {
final var result = tx.run(query, parameters("name", name));
final var maybeGame = result.stream().findFirst();
if (maybeGame.isPresent()) {
final var gameRecord = maybeGame.get();
final var gameMap = gameRecord.get("g").asMap();
final var expansionMaps = gameRecord.get("expansions").asList(MapAccessor::asMap);
final var categoryMaps = gameRecord.get("categories").asList(MapAccessor::asMap);
return Optional.of(Game.convert(gameMap, expansionMaps));
}
return Optional.empty();
});
}
gameMap contains a single game. expansionsMap contains 4, duplicating the 2 I'd expect. Same with categoriesMap; getting 4, with the 2 I'd expect duplicated.
I can obviously parse that out in my converter, but I don't understand why I'm not getting the correct number of maps. Is there something I can adjust on the query, or the code?
Solution
Your query is not correct.
It works in your Neo4j browser probably in Graph View ( because a visualisation doesn't show the same node twice ), switch to table view or text view to view that your query has 4 results for expansion and categories.
Taking this stub data
MERGE (g:Game {id: 1})
MERGE (e1:Game {id: 2})
MERGE (e2:Game {id: 3})
MERGE (c1:Category {id: 4})
MERGE (c2:Category {id: 5})
MERGE (g)-[:HAS]->(e1)
MERGE (g)-[:HAS]->(e2)
MERGE (g)-[:HAS]->(c1)
MERGE (g)-[:HAS]->(c2)
MATCH (g:Game {id: 1})-[:HAS]->(e:Game),
(g)-[:HAS]->(c:Category)
RETURN DISTINCT g,
COLLECT(e) AS expansions,
COLLECT(c) AS categories
╒════════╤═════════════════════════════════════╤═════════════════════════════════════╕
│"g" │"expansions" │"categories" │
╞════════╪═════════════════════════════════════╪═════════════════════════════════════╡
│{"id":1}│[{"id":3},{"id":2},{"id":3},{"id":2}]│[{"id":5},{"id":5},{"id":4},{"id":4}]│
└────────┴─────────────────────────────────────┴─────────────────────────────────────┘
Modified query making it working
MATCH (g:Game {id: 1})-[:HAS]->(e:Game),
(g)-[:HAS]->(c:Category)
RETURN g,
COLLECT(distinct e) AS expansions,
COLLECT(distinct c) AS categories
╒════════╤═══════════════════╤═══════════════════╕
│"g" │"expansions" │"categories" │
╞════════╪═══════════════════╪═══════════════════╡
│{"id":1}│[{"id":3},{"id":2}]│[{"id":5},{"id":4}]│
└────────┴───────────────────┴───────────────────┘
Small explanations, there is no need for distinct on g since it's a non aggregation function and so will be used as implicit groupBy
https://neo4j.com/docs/cypher-manual/current/functions/aggregating/
Answered By - Christophe Willemsen
Answer Checked By - Terry (JavaFixing Volunteer)
