求助：编写MongoDB跨集合查A有B无数据的聚合查询报$ne错误

错误原因分析 #

• 语法错误：$match阶段直接使用数组格式的$ne属于聚合表达式用法，必须套在$expr运算符内才能生效，否则Mongo会把$ne识别为字段查询运算符，格式不匹配就抛出未知运算符错误
• 逻辑错误：
  • 未在$lookup阶段过滤B集合x='123'的条件，会拉取B集合全量关联数据，既不符合需求也会大幅降低查询效率
  • 要实现NOT EXISTS的效果，直接判断关联返回的数组为空即可，不需要额外比较手机号
  • 原查询中all_profiles.col3: "xxxx"的条件未出现在你给出的等价SQL逻辑中，若无特殊业务要求可以直接删除

正确的聚合查询写法 #

db.A.aggregate([
    // 过滤A集合中dob存在的记录，对应SQL的col1 is not null
    { $match: { "dob": { $exists: true } } },
    // 关联B集合，仅关联B中x='123'的对应手机号记录
    {
        $lookup: {
            from: "B",
            localField: "phone_number",
            foreignField: "phone_number",
            pipeline: [{ $match: { x: "123" } }],
            as: "matched_b_records"
        }
    },
    // 过滤B中无匹配的记录，对应SQL的NOT EXISTS
    { $match: { matched_b_records: { $size: 0 } } },
    // 返回指定字段
    {
        $project: {
            first_name: 1,
            last_name: 1,
            dob: 1,
            phone_number: 1
        }
    }
])

如果你的MongoDB版本低于5.0不支持lookup直接带pipeline，可以用如下兼容写法：

db.A.aggregate([
    { $match: { "dob": { $exists: true } } },
    {
        $lookup: {
            from: "B",
            let: { a_phone: "$phone_number" },
            pipeline: [
                {
                    $match: {
                        $expr: { $eq: ["$phone_number", "$$a_phone"] },
                        x: "123"
                    }
                }
            ],
            as: "matched_b_records"
        }
    },
    { $match: { matched_b_records: { $size: 0 } } },
    { $project: { first_name: 1, last_name: 1, dob: 1, phone_number: 1 } }
])

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