How return an item which are not detected in a WHERE IN

Neo4j Graph Platform
1

Hi,

How can I also RETURN an item which doesn't exist in a list. In the case below item "2" doesn't exist. But I like still to have added to the output for instance:

{
"statusType": "2",
"nrOfCables": 0
}

Was thinking to have a WHERE clause like below but that doesn't work:
WHERE ((cables.status IN ["0", "1", "2", "3", "4", "5"]) OR (NOT (cables.status IN ["0", "1", "2", "3", "4", "5"]) ))

How to solve ?

Current query:
MATCH (cables:Cables)
WHERE cables.status IN ["0", "1", "2", "3", "4", "5"]
WITH cables.status as statusType,
count(*) as countOfCables
WITH {statusType: statusType,
nrOfCables: countOfCables} as typeData
ORDER BY typeData.statusType
return typeData

The output shows:
typeData
{
"statusType": "1",
"nrOfCables": 4
}
{
"statusType": "3",
"nrOfCables": 4
}
{
"statusType": "4",
"nrOfCables": 2
}
{
"statusType": "5",
"nrOfCables": 2
}

Creating a data set:
// 01. Delete all

MATCH (all) DETACH DELETE all;

CREATE (cable1:Cables {length:0, cableType:"Main", pulled:"Not Pulled", mainCable:"Not Routed", status:"1", connectedFrom:FALSE, connectedTo:FALSE, description:"[Lt-Supply][P]LP7421 Supply 2 Lighting"});
CREATE (cable2:Cables {length:100, cableType:"Main", pulled:"Pulled", mainCable:"Routed", status:"3", connectedFrom:FALSE, connectedTo:FALSE, description:"[Lt-Supply][P]LP7421 Supply 2 Lighting"});
CREATE (cable3:Cables {length:100, cableType:"Main", pulled:"Pulled", mainCable:"Routed", status:"3", connectedFrom:TRUE, connectedTo:FALSE, description:"[Tech-Lt][P]Power Socket"});
CREATE (cable4:Cables {length:100, cableType:"Main", pulled:"Pulled", mainCable:"Routed", status:"4", connectedFrom:TRUE, connectedTo:TRUE, description:"[Tech-Lt][P]Power Socket"});
CREATE (cable5:Cables {length:100, cableType:"Main", pulled:"Pulled", mainCable:"Routed", status:"5", connectedFrom:TRUE, connectedTo:TRUE, description:"[Crew-Lt][P]Art Light (Spare)"});
CREATE (cable6:Cables {length:0, cableType:"Main", pulled:"Not Pulled", mainCable:"Not Routed", status:"1", connectedFrom:FALSE, connectedTo:FALSE, description:"[RZ][1]24VDC GMDSS Light Red (SPARE)"});
CREATE (cable7:Cables {length:0, cableType:"Local", pulled:"Not Pulled", mainCable:"Not Routed", status:"1", connectedFrom:FALSE, connectedTo:FALSE, description:"[Lt-Supply][P]LP7421 Supply 2 Lighting"});
CREATE (cable8:Cables {length:100, cableType:"Local", pulled:"Not Pulled", mainCable:"Local", status:"3", connectedFrom:TRUE, connectedTo:TRUE, description:"[Lt-Supply][P]LP7421 Supply 2 Lighting"});
CREATE (cable9:Cables {length:100, cableType:"Local", pulled:"Pulled", mainCable:"Local", status:"3", connectedFrom:FALSE, connectedTo:TRUE, description:"[Tech-Lt][P]Power Socket"});
CREATE (cable10:Cables {length:100, cableType:"Local", pulled:"Not Pulled", mainCable:"Local", status:"4", connectedFrom:TRUE, connectedTo:FALSE, description:"[Tech-Lt][P]Power Socket"});
CREATE (cable11:Cables {length:100, cableType:"Local", pulled:"Pulled", mainCable:"Local", status:"5", connectedFrom:TRUE, connectedTo:TRUE, description:"[Crew-Lt][P]Art Light (Spare)"});
CREATE (cable12:Cables {length:100, cableType:"Local", pulled:"Pulled", mainCable:"Local", status:"1", connectedFrom:FALSE, connectedTo:FALSE, description:"[RZ][1]24VDC GMDSS Light Red (SPARE)"});

2

try this:

UNWIND ["0", "1", "2", "3", "4", "5"] as statusType
OPTIONAL MATCH (cables:Cables{status: statusType})
WITH statusType, count(cables) as cnt
WITH {statusType: statusType,
nrOfCables: cnt} as typeData
ORDER BY typeData.statusType
return typeData

{
  "statusType": "0",
  "nrOfCables": 0
}

{
  "statusType": "1",
  "nrOfCables": 4
}

{
  "statusType": "2",
  "nrOfCables": 0
}

{
  "statusType": "3",
  "nrOfCables": 4
}

{
  "statusType": "4",
  "nrOfCables": 2
}

{
  "statusType": "5",
  "nrOfCables": 2
}
3

Gary,
The solution is simple ! Simplicity makes things great !

My conclusion, how it works, is as follows:

UNWIND makes a list of items;
OPTIONAL MATCH match in the column "status" the list of items, including items which doesn't exist;
When there is a match it counts the number of cables, WITH statusType, count(cables) as cnt;
The results are store in "records" (typeData) ordered by statusType.

Just to clarify the code for myself and understand what is happening.

Thanks again !!

4

Your understanding is correct. I would change one thing, the unwind converts a list into rows.

One other important thing to know about unwind is that it also appends the other variables in scope to each new unwound row, repeating the values for each new row. There are no other variables in scope in your case, since the unwind happens first. My comment is applicable when an unwind happens after results are generated. Here is the reference on unwind. Unfortunately all the examples in the document are with unwind at the beginning of the query, so there are no examples demonstrating what I described.