How Many Inspections Were Actually Completed?

Filter bound properties and aggregate at the intended grain, distinguishing paths, relationships and distinct inspection identities.

12 sections · 4 questions · 1 coding challenge

1 / 12

The arrow keys move between sections too.

Graph Matches Still Produce Rows That Need a Grain

BOTIE

Pattern matching can produce many binding rows from one starting node. Cypher combines those rows using aggregate functions and explicit intermediate stages such as WITH. The result depends on what is counted and which bindings remain as grouping keys.

Our duplicate link shows the practical consequence. The graph can contain two matched relationships while representing one inspection entity. Counting the entity once requires an identity-based duplicate policy.

Sources for this history

A Repeated Link Has Doubled the Report

P7 has one completed inspection. An import has accidentally recorded two relationships to that same inspection. Botie’s first count says two. The query counted matching relationships, while the maintenance team asked for distinct completed inspections.

Before adding numbers, we need to decide what a matched row represents. We can inspect status on the bound inspection, then remove repeated asset/inspection pairs before counting them.

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 2

Two relationship records point to one inspection

The duplicate edge IDs are separate records, but the target inspection identity is the same.

Nodes and properties (3)

P7 · Asset
I1 · closed
I2 · open

P7 to I1: HAS_INSPECTION h1
P7 to I1: HAS_INSPECTION h2
P7 to I2: HAS_INSPECTION h3

The completed-inspection count is one. Counting relationship matches would answer a different question.

Step 2 of 2

Follow the row meaning through the count

These are the duplicate-link records drawn above, inspected stage by stage.

Removing duplicates before deciding which fields define the entity can change the answer.
Stage	Visible rows	Meaning
Matched bindings	P7,h1,I1,closed; P7,h2,I1,closed; P7,h3,I2,open	One row per matched relationship
Closed Inspection filter	P7,h1,I1; P7,h2,I1	Two routes still refer to one inspection
Distinct asset/inspection pairs	P7,I1	Relationship ID is no longer part of the counted grain
Group by asset	P7: 1	One distinct completed inspection

Matched bindings
Visible rows
P7,h1,I1,closed; P7,h2,I1,closed; P7,h3,I2,open
Meaning
One row per matched relationship
Closed Inspection filter
Visible rows
P7,h1,I1; P7,h2,I1
Meaning
Two routes still refer to one inspection
Distinct asset/inspection pairs
Visible rows
P7,I1
Meaning
Relationship ID is no longer part of the counted grain
Group by asset
Visible rows
P7: 1
Meaning
One distinct completed inspection

Removing duplicates before deciding which fields define the entity can change the answer.

Count completed inspection identities

The result counts distinct closed Inspection identities within each asset, so repeated relationships cannot inflate the count.

Count completed inspection identities

An import can repeat a relationship without creating another inspection.

BOTIE

An import can repeat a relationship without creating another inspection.

Choose an example

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

Edit the node and relationship records

nodes

kind

status

kind

status

kind

edges

source

target

type

source

target

type

source

target

type

source

target

type

Result for these records

[
  {
    "origin": "P7",
    "n": 1
  }
]

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
1. 0P7
2. 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. Extend each binding by one relationship

[
  {
    "origin": "P7",
    "current": "I1",
    "path": [
      "P7",
      "I1"
    ],
    "edge_ids": [
      "h1"
    ]
  },
  {
    "origin": "P8",
    "current": "I2",
    "path": [
      "P8",
      "I2"
    ],
    "edge_ids": [
      "h2"
    ]
  }
]

10. Read properties at the bound node

[
  {
    "origin": "P7",
    "current": "I1",
    "path": [
      "P7",
      "I1"
    ],
    "edge_ids": [
      "h1"
    ],
    "n_status": "closed",
    "n_kind": "Inspection"
  },
  {
    "origin": "P8",
    "current": "I2",
    "path": [
      "P8",
      "I2"
    ],
    "edge_ids": [
      "h2"
    ],
    "n_status": "open",
    "n_kind": "Inspection"
  }
]

11. Text comparison value
```
Inspection
```

12. Keep rows passing a comparison

[
  {
    "origin": "P7",
    "current": "I1",
    "path": [
      "P7",
      "I1"
    ],
    "edge_ids": [
      "h1"
    ],
    "n_status": "closed",
    "n_kind": "Inspection"
  },
  {
    "origin": "P8",
    "current": "I2",
    "path": [
      "P8",
      "I2"
    ],
    "edge_ids": [
      "h2"
    ],
    "n_status": "open",
    "n_kind": "Inspection"
  }
]

13. Text comparison value
```
closed
```

14. Keep rows passing a comparison

[
  {
    "origin": "P7",
    "current": "I1",
    "path": [
      "P7",
      "I1"
    ],
    "edge_ids": [
      "h1"
    ],
    "n_status": "closed",
    "n_kind": "Inspection"
  }
]

15. Choose table columns

[
  {
    "origin": "P7",
    "current": "I1"
  }
]

16. Keep distinct rows

[
  {
    "origin": "P7",
    "current": "I1"
  }
]

17. Summarise each group

[
  {
    "origin": "P7",
    "n": 1
  }
]

18. Order rows by named fields

[
  {
    "origin": "P7",
    "n": 1
  }
]

19. Result

[
  {
    "origin": "P7",
    "n": 1
  }
]

These components operate on the supplied records in your browser. They do not run a graph database or interpret Cypher.

1. Filter the property of the bound inspection

The status belongs to the inspection node. Require the endpoint to have kind Inspection as well as status closed. A mistyped HAS_INSPECTION edge to a technician is a data-quality failure, not completed inspection work.

Inspect the node reached by each binding, then require status closed. An absent status does not pass that equality condition. Decide separately whether missing statuses should trigger a data-quality report.

Relationship meaning and node state are different facts.

2. Write down what one matched row represents

After expansion there is a row for each matched asset/relationship/inspection combination. Two edges to I1 therefore produce two rows. If the desired count is distinct inspections, reduce each row to the asset ID and inspection ID before deduplication.

Deduplicating only the inspection ID globally would lose which asset the count belongs to. Some domains legitimately associate the same inspection with several assets.

The distinct pair preserves the grouping identity and the counted entity.

Collapse repeated asset/inspection pairs

This small program demonstrates the grain change before aggregation.

Python

pairs=[('P7','I1'),('P7','I1'),('P8','I2')]
print(sorted(set(pairs)))

Output

[('P7', 'I1'), ('P8', 'I2')]

The two P7 matches become one pair while P8 keeps its own association.

3. Keep the grouping variable beside the aggregate

In Cypher, non-aggregate returned expressions define grouping keys. Returning a.id with count(DISTINCT i) gives one count per asset. WITH can name that count for a later condition.

If a later relationship match multiplies the rows, an already computed count can also be repeated. Inspect intermediate cardinality before summing a summary again.

The group boundary deserves the same attention in Cypher as in SQL.

Run in your own environment

Count inspection entities rather than matched links

Only assets with at least one completed match appear in this required-pattern query.

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.

cypher

MATCH (a:Asset)-[:HAS_INSPECTION]->(i:Inspection)
WHERE i.status=$status
WITH a, count(DISTINCT i) AS completed
RETURN a.id AS asset, completed
ORDER BY asset

P7 has one completed inspection. P8 is absent because its inspection is open.

Official documentation. Documentation checked 2026-10-16.

4. Do not manufacture zero rows after a required match

If no completed inspection matches an asset, that asset has no binding row in this query. Grouping cannot produce a group for an entity that vanished earlier.

If the report must show zero, preserve the asset population before matching optional inspections. That will be the next lesson’s problem. Replacing a missing number at the end cannot recreate a missing asset row.

Absent groups and groups with zero counts are different query results.

5. Filter before aggregation only when the question calls for it

Closed status qualifies individual inspections, so it belongs before the count. A requirement such as at least two completed inspections belongs after that grouped count has been calculated.

Counting all inspections and then asking whether any status is closed mixes two different questions. Keep row-level conditions and group-level conditions on the stages where their inputs exist.

Read the count as a statement about a specific population of distinct inspection identities.

How Many Inspections Were Actually Completed?

Graph Matches Still Produce Rows That Need a Grain

A Repeated Link Has Doubled the Report

Two relationship records point to one inspection

Follow the row meaning through the count

Count completed inspection identities

Count completed inspection identities

Result for these records

1. Filter the property of the bound inspection

2. Write down what one matched row represents

Collapse repeated asset/inspection pairs

3. Keep the grouping variable beside the aggregate

Count inspection entities rather than matched links

Questions on Parts 1 to 3

4. Do not manufacture zero rows after a required match

5. Filter before aggregation only when the question calls for it

Questions on Parts 1 to 5

Build with Botie

Practice. Count distinct completed inspections per asset