Keep the Pumps That Have No Completed Inspection
Use optional graph patterns without losing the starting population or mistaking an unmatched placeholder for a real inspection.
13 sections · 4 questions · 1 coding challenge
The arrow keys move between sections too.
Absence Became a Result Worth Preserving
BOTIE
That behaviour lets an asset register define the report population. A missing completed inspection is then visible as absence, instead of causing the asset itself to disappear.
Sources for this history
The Asset Most in Need of Attention Has Disappeared
Botie’s report correctly counts completed inspections, but P8 is missing. P8 only has an open inspection. The maintenance team needs to see that zero, because it is precisely the pump that may need attention.
A required pattern keeps only successful matches. We need to retain each asset even when its qualifying inspection pattern has no match.
Invented maintenance records. Python exercises and workshop components expose the mechanism locally. Source-linked Cypher or SPARQL examples require the stated external database setup.
Step 1 of 1
An open inspection does not satisfy the closed-work pattern
Both assets remain in the report, but only one has a qualifying target.
Nodes and properties (4)
- P7 · Asset: count 1
- I1 · closed
- P8 · Asset: count 0
- I2 · open
- P7 to I1: HAS_INSPECTION
- P8 to I2: HAS_INSPECTION
Preserve assets with no qualifying inspection
Optional extension preserves every registered asset. Counting only non-null distinct inspection IDs turns a missing qualifying match into a visible zero.
Preserve assets with no qualifying inspection
The pump with only open work must remain visible as zero.
BOTIE
Result for these records
[
{
"origin": "P7",
"n": 1
},
{
"origin": "P8",
"n": 0
}
]Follow the operations
1. Maintenance graph
Nodes and properties (6)
- P7 · Asset
- P8 · Asset
- I1 · Inspection
- status: closed
- I2 · Inspection
- status: open
- T1 · Technician
- T2 · Technician
- P7 to I1: HAS_INSPECTION
- P8 to I2: HAS_INSPECTION
- I1 to T1: CARRIED_OUT_BY
- I2 to T2: CARRIED_OUT_BY
Inspect the node and relationship records
{ "nodes": [ { "id": "P7", "kind": "Asset" }, { "id": "P8", "kind": "Asset" }, { "id": "I1", "kind": "Inspection", "status": "closed" }, { "id": "I2", "kind": "Inspection", "status": "open" }, { "id": "T1", "kind": "Technician" }, { "id": "T2", "kind": "Technician" } ], "edges": [ { "id": "h1", "source": "P7", "target": "I1", "type": "HAS_INSPECTION" }, { "id": "h2", "source": "P8", "target": "I2", "type": "HAS_INSPECTION" }, { "id": "t1", "source": "I1", "target": "T1", "type": "CARRIED_OUT_BY" }, { "id": "t2", "source": "I2", "target": "T2", "type": "CARRIED_OUT_BY" } ] }2. Read graph nodes
[ { "id": "P7", "kind": "Asset" }, { "id": "P8", "kind": "Asset" }, { "id": "I1", "kind": "Inspection", "status": "closed" }, { "id": "I2", "kind": "Inspection", "status": "open" }, { "id": "T1", "kind": "Technician" }, { "id": "T2", "kind": "Technician" } ]3. Text comparison value
Asset
4. Keep rows passing a comparison
[ { "id": "P7", "kind": "Asset" }, { "id": "P8", "kind": "Asset" } ]5. Collect distinct identifiers
- 0P7
- 1P8
6. Start a binding at each identifier
[ { "origin": "P7", "current": "P7", "path": [ "P7" ], "edge_ids": [] }, { "origin": "P8", "current": "P8", "path": [ "P8" ], "edge_ids": [] } ]7. Read graph relationships
[ { "id": "h1", "source": "P7", "target": "I1", "type": "HAS_INSPECTION" }, { "id": "h2", "source": "P8", "target": "I2", "type": "HAS_INSPECTION" }, { "id": "t1", "source": "I1", "target": "T1", "type": "CARRIED_OUT_BY" }, { "id": "t2", "source": "I2", "target": "T2", "type": "CARRIED_OUT_BY" } ]8. Choose a relationship type
[ { "id": "h1", "source": "P7", "target": "I1", "type": "HAS_INSPECTION" }, { "id": "h2", "source": "P8", "target": "I2", "type": "HAS_INSPECTION" } ]9. Collect distinct identifiers
- 0I1
- 1I2
10. Start a binding at each identifier
[ { "origin": "I1", "current": "I1", "path": [ "I1" ], "edge_ids": [] }, { "origin": "I2", "current": "I2", "path": [ "I2" ], "edge_ids": [] } ]11. Read properties at the bound node
[ { "origin": "I1", "current": "I1", "path": [ "I1" ], "edge_ids": [], "n_status": "closed", "n_kind": "Inspection" }, { "origin": "I2", "current": "I2", "path": [ "I2" ], "edge_ids": [], "n_status": "open", "n_kind": "Inspection" } ]12. Text comparison value
Inspection
13. Keep rows passing a comparison
[ { "origin": "I1", "current": "I1", "path": [ "I1" ], "edge_ids": [], "n_status": "closed", "n_kind": "Inspection" }, { "origin": "I2", "current": "I2", "path": [ "I2" ], "edge_ids": [], "n_status": "open", "n_kind": "Inspection" } ]14. Text comparison value
closed
15. Keep rows passing a comparison
[ { "origin": "I1", "current": "I1", "path": [ "I1" ], "edge_ids": [], "n_status": "closed", "n_kind": "Inspection" } ]16. Collect distinct identifiers
- 0I1
17. Keep relationships with allowed targets
[ { "id": "h1", "source": "P7", "target": "I1", "type": "HAS_INSPECTION" } ]18. Extend each binding by one relationship
[ { "origin": "P7", "current": "I1", "path": [ "P7", "I1" ], "edge_ids": [ "h1" ] }, { "origin": "P8", "current": null, "path": [ "P8" ], "edge_ids": [] } ]19. Choose table columns
[ { "origin": "P7", "current": "I1" }, { "origin": "P8", "current": null } ]20. Keep distinct rows
[ { "origin": "P7", "current": "I1" }, { "origin": "P8", "current": null } ]21. Summarise each group
[ { "origin": "P7", "n": 1 }, { "origin": "P8", "n": 0 } ]22. Order rows by named fields
[ { "origin": "P7", "n": 1 }, { "origin": "P8", "n": 0 } ]23. Result
[ { "origin": "P7", "n": 1 }, { "origin": "P8", "n": 0 } ]
These components operate on the supplied records in your browser. They do not run a graph database or interpret Cypher.
1. Begin with the complete asset population
MATCH (a:Asset) establishes every asset that belongs in the report. The next match is allowed to be absent, but the original a remains bound.
In Python, initialise an empty inspection set for every asset before examining edges. That small ordering decision is what keeps a pump with no relationships at all.
The output population must exist before optional evidence is attached.
Start a count entry for an isolated asset
No inspection edge is required to establish a report row.
nodes=[{'id':'P7','kind':'Asset'},{'id':'P9','kind':'Asset'}]
print({n['id']:set() for n in nodes}){'P7': set(), 'P9': set()}Each empty set can later produce a count of zero.
2. Make the qualifying condition part of the optional pattern
The closed-status condition describes which inspection is an acceptable match. In Cypher, a WHERE associated with OPTIONAL MATCH participates in that pattern’s matching conditions.
Moving the condition to a later WITH/WHERE stage can instead remove a row whose optional inspection is NULL. The resulting query may again omit the asset we meant to preserve.
The position of the predicate expresses whether it qualifies a match or filters an already preserved result.
3. Count matched identities rather than preserved rows
count(i) ignores NULL bindings, while count(*) counts rows. An asset with no qualifying inspection has an optional placeholder row, so count(*) can produce one even though no inspection exists.
Use count(DISTINCT i) when repeated relationships should not multiply the same inspection. The grouping asset remains in the output with zero.
The placeholder preserves the asset. It is not an inspection.
Run in your own environment
Keep zero-completion assets
The status predicate belongs to the optional pattern.
In an empty Neo4j test database, run CREATE (a:Asset {id:'P7'}),(b:Asset {id:'P8'}),(i:Inspection {id:'I1',status:'closed'}),(j:Inspection {id:'I2',status:'open'}),(t:Technician {id:'T1'}),(u:Technician {id:'T2'}),(a)-[:HAS_INSPECTION]->(i),(b)-[:HAS_INSPECTION]->(j),(i)-[:CARRIED_OUT_BY]->(t),(j)-[:CARRIED_OUT_BY]->(u).
Bind status to closed.
MATCH (a:Asset)
OPTIONAL MATCH (a)-[:HAS_INSPECTION]->(i:Inspection)
WHERE i.status=$status
RETURN a.id AS asset, count(DISTINCT i) AS completed
ORDER BY assetP7 appears with 1 and P8 with 0.
Official documentation. Documentation checked .
Check yourself
Questions on Parts 1 to 3
Question 1 of 2
What should P8’s completed count be?
Use the information below.
P8 has an open inspection but no closed inspection. The report starts from every Asset and optionally matches closed inspections.4. Distinguish no inspection from an inspection with unknown properties
An actual inspection node may lack its completion date or status. That is different from having no inspection relationship. Inspect the node identity when diagnosing missing values.
A report can preserve both facts by returning inspection IDs and their properties separately. Do not use a nullable descriptive field as proof that no node matched.
NULL in one property is not evidence that the whole related entity is absent.
5. Keep independent optional branches from multiplying each other
Suppose an asset has two inspections and three manuals. Matching both branches can produce six combinations. Counting inspections after that multiplication requires an explicit distinct policy or separate aggregation of each branch.
OPTIONAL MATCH preserves absence; it does not eliminate fan-out. Work through one small asset with multiple matches on both sides before trusting a dashboard total.
The workshop has one optional branch so we can observe its preserved row directly.
6. Build the qualifying edge set before optional expansion
Our components first identify closed inspection targets and retain only relationships pointing to those targets. Optional expansion then preserves a binding whose source has no qualifying relationship.
The resulting NULL current value is excluded by count values. The distinct origin/current pair prevents repeated links from adding another inspection.
This record mechanism mirrors the question’s matching boundary without pretending to parse OPTIONAL MATCH syntax.
Check yourself
Questions on Parts 1 to 6
Question 1 of 2
What establishes that an inspection actually matched?
Use the information below.
An inspection node exists with a missing note property.Try it with Botie
Build with Botie
Write and run Python