I have a following Neo4j Cypher query:
MATCH (parentD:Decision)-[:CONTAINS]->(childD:Decision)
WHERE id(parentD) = {parentDecisionId}
WITH childD, parentD
OPTIONAL MATCH (parentD)<-[:DEFINED_BY]-(c:Criterion)<-[:VOTED_ON]-(vg:VoteGroup)-[:VOTED_FOR]->(childD)
OPTIONAL MATCH (parentD)<-[:DEFINED_BY]-(ch:Characteristic)<-[:SET_ON]-(v:Value)-[:SET_FOR]->(childD)
WITH childD, {criterion: c, weight: vg.avgVotesWeight} AS weightedCriterion, {characteristic: ch, value: v.value} AS valuedCharacteristic
RETURN childD AS decision, collect(weightedCriterion) AS weightedCriteria, collect(valuedCharacteristic) AS valuedCharacteristics
As a result in my SDN 4 project I expect to retrive a List<DecisionMatrix>
@QueryResult
public class DecisionMatrix {
private Decision decision;
private List<WeightedCriterion> weightedCriteria;
private List<ValuedCharacteristic> valuedCharacteristics;
}
@QueryResult
public class WeightedCriterion {
private Criterion criterion;
private Double weight;
}
@QueryResult
public class ValuedCharacteristic {
private Characteristic characteristic;
private Object value;
}
Right now this query returns a correct list of criterion but a wrong list with an empty elements of characteristic.
For example I do not have any characteristic that match this query condition but in a result I can see a following structure with a two records:
RDBMS : [{criterion=Node[161], weight=4.333333333333333}, {criterion=Node[160], weight=2.1666666666666665}] : [{characteristic=null, value=null}, {characteristic=null, value=null}]
NoSQL : [{criterion=Node[160], weight=4.333333333333333}, {criterion=Node[161], weight=2.5}, {criterion=Node[162], weight=4.2}] : [{characteristic=null, value=null}, {characteristic=null, value=null}, {characteristic=null, value=null}]
The result contains a correct set of criteria for RDBMS (2 criteria) and for NoSQL (3 criteria) but a wrong set of characteristics. I expect to have an empty lists(0 elements) of characteristics for both of these records(there are no associated characteristics for these nodes).. but in first record I have 2 empty characteristics (the same list length as and criteria) and 3 empty characteristics for the second record.
What am I doing wrong and how to fix it ?
This is a result of explicitly creating a map for values here:
... {characteristic: ch, value: v.value} AS valuedCharacteristic ...
This creates a map, and sets values accordingly, and those values happen to be null because the OPTIONAL MATCH wasn't able to match the pattern. There's nothing wrong with adding null values into a map, and there's nothing that inherently ties the map's existence to whether one (or even all) of its property values is null or not null.
For Neo4j 3.1 and higher, the easiest way to get around this, and ensure the list at the end remains empty when there are no characteristics, is to skip the OPTIONAL MATCH entirely, and use pattern comprehension instead. And we might as well do the same with your other OPTIONAL MATCH with :Criterion and :VoteGroup while we're at it.
MATCH (parentD:Decision)-[:CONTAINS]->(childD:Decision)
WHERE id(parentD) = {parentDecisionId}
RETURN childD AS decision,
[ (parentD)<-[:DEFINED_BY]-(c:Criterion)<-[:VOTED_ON]-(vg:VoteGroup)-[:VOTED_FOR]->(childD)
| {criterion: c, weight: vg.avgVotesWeight} ] AS weightedCriteria,
[ (parentD)<-[:DEFINED_BY]-(ch:Characteristic)<-[:SET_ON]-(v:Value)-[:SET_FOR]->(childD)
| {characteristic: ch, value: v.value} ] AS valuedCharacteristics
If the patterns in your pattern comprehensions do not exist, the resulting lists will be empty.
For Neo4j 3.0 and below, you'll probably have to stick with your original query, but use a CASE statement to conditionally emit null instead of your valuedCharacteristic (and weightedCriterion) map when the corresponding variable is null. A snipped using this would look like:
... CASE WHEN ch IS NULL THEN null ELSE {characteristic: ch, value: v.value} END AS valuedCharacteristic