如何用Cypher查询所有关系均含deleted_at属性的节点？

一、精准筛选目标Actor节点 #

要找出所有CURRENT_FILMING关系全为软删除状态（即所有该类型关系都带有deleted_at属性）的Actor节点，最简洁高效的查询是利用NOT EXISTS谓词：

MATCH (a:Actor)
// 排除没有任何CURRENT_FILMING关系的Actor，只保留有该关系但全为删除状态的
WHERE EXISTS( (a)-[:CURRENT_FILMING]->() )
  AND NOT EXISTS( (a)-[:CURRENT_FILMING {deleted_at: NULL}]->() )
RETURN a.id AS actor_id, count( (a)-[:CURRENT_FILMING]->() ) AS total_deleted_relations

二、优化你之前的查询问题 #

1. 第一个冗余查询的优化

你之前的查询需要保留collect(r.deleted_at) AS deleted，是因为Cypher的分组逻辑：当WITH子句中包含聚合函数（如collect）时，分组会自动按非聚合变量进行。你之前的写法没有明确按a分组，导致必须保留这个聚合变量才能实现按Actor分组。优化后无需冗余变量：

MATCH (a:Actor)-[r:CURRENT_FILMING]->(m:Movie)
WITH a, count(r) AS movie_count, sum(CASE WHEN r.deleted_at IS NOT NULL THEN 1 ELSE 0 END) AS deleted_count
RETURN a.id, movie_count, deleted_count
ORDER BY (movie_count - deleted_count) ASC

2. 谓词函数查询的修正

你之前的谓词查询错误在于没有按Actor节点分组，导致所有关系被聚合到一个列表中，而非每个Actor单独处理。修正后：

MATCH (a:Actor)-[r:CURRENT_FILMING]->(m:Movie)
WITH a, collect(r) AS projects
// 检查当前Actor的所有CURRENT_FILMING关系都带有deleted_at
WHERE none(x IN projects WHERE x.deleted_at IS NULL)
RETURN a.id, projects

3. 无效查询的问题

第三个查询中变量c未定义（应为a），且未按Actor分组，导致统计逻辑完全错误，修正思路同上述优化方案。

三、直接批量修复无需手动导出ID #

无需手动导出CSV整理ID列表，可直接关联查询完成修复。如果需要确保每个Actor仅保留一个活跃CURRENT_FILMING关系，可使用以下查询：

MATCH (a:Actor)-[r:CURRENT_FILMING]->(m:Movie)
WHERE EXISTS( (a)-[:CURRENT_FILMING]->() )
  AND NOT EXISTS( (a)-[:CURRENT_FILMING {deleted_at: NULL}]->() )
WITH a, collect(r) AS relations
// 仅移除第一个关系的deleted_at，确保每个Actor有且仅有一个活跃关系
FOREACH (idx IN range(0, size(relations)-1) |
  FOREACH (rel IN [relations[idx]] |
    REMOVE rel.deleted_at WHERE idx = 0
  )
)
RETURN a.id AS actor_id, size(relations) AS total_relations, 1 AS active_relations_restored

如果需要将所有该Actor的CURRENT_FILMING关系都恢复为活跃状态，可简化为：

MATCH (a:Actor)-[r:CURRENT_FILMING]->(m:Movie)
WHERE EXISTS( (a)-[:CURRENT_FILMING]->() )
  AND NOT EXISTS( (a)-[:CURRENT_FILMING {deleted_at: NULL}]->() )
REMOVE r.deleted_at
RETURN a.id AS actor_id, count(r) AS relations_restored

内容的提问来源于stack exchange，提问作者Sam Wynne