解决MongoDB Java聚合查询报错'must specify pipeline when from is empty'

错误信息 #

com.mongodb.MongoCommandException: Command failed with error 9 (FailedToParse): 'must specify 'pipeline' when 'from' is empty' on server localhost:27017. The full response is {"ok": 0.0, "errmsg": "must specify 'pipeline' when 'from' is empty", "code": 9, "codeName": "FailedToParse"}

可正常运行的Mongo Shell聚合代码 #

db.borrower_details.aggregate([
    {
        $lookup:{
            from: "student_details",   
            localField: "student_id",  
            foreignField: "student_id",
            as: "borrow_std"     
        }
    },
    {
        $lookup:{
            from: "book_details", 
            localField: "book_id", 
            foreignField: "book_id",
            as: "borrow_std_book"
        }
    },
    {   
        $project:{
            student_id : 1,
            borrowed_from : 1,
            book_title : "$borrow_std_book.Book_Title",
        } 
    }
]);

报错的Java代码 #

MongoCollection<Document> borrower_details = db.getCollection("borrower_details");

BasicDBObject joinFields1 = new BasicDBObject();
joinFields1.append("from", "student_details");
joinFields1.append("localField", "student_id");
joinFields1.append("foreignField", "student_id");
joinFields1.append("as", "borrow_std");
BasicDBObject lookup1 = new BasicDBObject();
lookup1.append("$lookup", joinFields1);

BasicDBObject joinFields2 = new BasicDBObject();
joinFields1.append("from", "book_details");
joinFields1.append("localField", "book_id");
joinFields1.append("foreignField", "book_id");
joinFields1.append("as", "borrow_std_book");
BasicDBObject lookup2 = new BasicDBObject();
lookup2.append("$lookup", joinFields2);

BasicDBObject retriveFields = new BasicDBObject();
retriveFields.append("student_id", 1);
retriveFields.append("borrowed_from", 1);
retriveFields.append("book_title", "$borrow_std_book.book_title");

BasicDBObject project = new BasicDBObject();
project.append("$project", retriveFields);

List<BasicDBObject> queryList = new ArrayList<>();
queryList.add(lookup1);
queryList.add(lookup2);
queryList.add(project);

MongoCursor<Document> cursor = borrower_details.aggregate(queryList).iterator();

问题排查与修复 #

核心问题 #

构建第二个$lookup的joinFields2时，代码错误地继续操作joinFields1对象，而非joinFields2，导致：

1. joinFields1的内容被覆盖为第二个lookup的参数，第一个lookup的配置被篡改
2. joinFields2始终是空对象，没有设置任何参数，MongoDB解析第二个$lookup时发现from为空，因此抛出错误

修正后的Java代码 #

MongoCollection<Document> borrower_details = db.getCollection("borrower_details");

// 第一个lookup：关联student_details
BasicDBObject joinFields1 = new BasicDBObject();
joinFields1.append("from", "student_details");
joinFields1.append("localField", "student_id");
joinFields1.append("foreignField", "student_id");
joinFields1.append("as", "borrow_std");
BasicDBObject lookup1 = new BasicDBObject("$lookup", joinFields1);

// 第二个lookup：关联book_details，修正为操作joinFields2
BasicDBObject joinFields2 = new BasicDBObject();
joinFields2.append("from", "book_details");
joinFields2.append("localField", "book_id");
joinFields2.append("foreignField", "book_id");
joinFields2.append("as", "borrow_std_book");
BasicDBObject lookup2 = new BasicDBObject("$lookup", joinFields2);

// 投影字段：保持与Mongo Shell中字段大小写一致
BasicDBObject retriveFields = new BasicDBObject();
retriveFields.append("student_id", 1);
retriveFields.append("borrowed_from", 1);
retriveFields.append("book_title", "$borrow_std_book.Book_Title");

BasicDBObject project = new BasicDBObject("$project", retriveFields);

List<BasicDBObject> queryList = new ArrayList<>();
queryList.add(lookup1);
queryList.add(lookup2);
queryList.add(project);

MongoCursor<Document> cursor = borrower_details.aggregate(queryList).iterator();

额外注意点 #

• 投影中的book_title字段值需与Mongo Shell一致，原Java代码写成小写book_title，会导致返回null，需修正为$borrow_std_book.Book_Title
• 可简化BasicDBObject的构建，比如new BasicDBObject("$lookup", new BasicDBObject("from", "student_details").append("localField", "student_id")...)，减少代码冗余

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