加入子数组中的多个记录

Question

我有一个员工记录，其中包含仅包含 id 的 leads 数组，基本上是他领导的其他员工。

我想输出将每个潜在客户与员工集合中的名称连接起来的结果

员工集合

{

    "_id" : ObjectId("5d4dc8635dd32dbcba4ae0ba"),

    "name" : "John"

}

{

    "_id" : ObjectId("5d4dc8635dd32dbcba4ae0bb"),

    "name" : "Jane"

}

{

    "_id" : ObjectId("5d4dc8635dd32dbcba4ae0b4"),

    "name" : "Richard"

}

employee_leads集合

{

    "_id" : ObjectId("5d55ac30e533bc76e4581923"),

    "employee_id" : ObjectId("5d4dc8635dd32dbcba4ae0c5"),

    "leads" : [ 

        ObjectId("5d4dc8635dd32dbcba4ae0ba"), 

        ObjectId("5d4dc8635dd32dbcba4ae0bb")

    ]

}

预期产出

{

    "_id" : ObjectId("5d4dc8635dd32dbcba4ae0c3"),

    "leads" : [ 

        {

            "_id" : ObjectId("5d4dc8635dd32dbcba4ae0ba"),

            "name" : "John"

        }, 

        {

            "_id" : ObjectId("5d4dc8635dd32dbcba4ae0bb"),

            "name" : "Jane"

        }

    ]

}

试图：

Document match = new Document("$match", new BasicDBObject("_id", new ObjectId("5d55ac30e533bc76e4581923")));

Document lookup = new Document("$lookup", new BasicDBObject("from", "employee"))

        .append("localField", "leads")

        .append("foreignField", "_id")

        .append("as", "leads");

// Document unwind = new Document("$unwind", "$leads");

Document project = new Document("$project", new BasicDBObject("name", "$lead.name"));

Document document = database.getCollection("employee_lead")

        .aggregate(Arrays.asList(match, lookup, unwind, project))

        .first();

// TODO: iterate through the lead array and display it

问题是，是否只有一个连接语句，或者我是否必须对数据库进行多次调用（天真方式）？

Answer 1

您不必多次调用数据库，$lookup将完全按照您在聚合框架中的要求进行操作。尝试以下查询：db.getCollection("employee_leads").aggregate([{        $match : {            "_id" : new ObjectId("5d55ac30e533bc76e4581923") // This is in case you want to filter anything.         }},{        $lookup : {            "from": "employee",            "localField": "leads",            "foreignField": "_id",            "as": "leads"        }}])上述查询的 Java 等效代码：示例 1List<Bson> aggregates = Arrays.asList(                Aggregates.match(Filters.eq("_id", new ObjectId("5d55ac30e533bc76e4581923"))),                Aggregates.lookup("employee", "leads", "_id", "leads"));        AggregateIterable<Document> iterable = this.collection.aggregate(aggregates);示例 2List<Document> aggregates = Arrays.asList(        new Document("$match", new Document("_id", new ObjectId("5d55ac30e533bc76e4581923"))),        new Document("$lookup", new Document("from", "employee")        .append("localField", "leads")        .append("foreignField", "_id")        .append("as", "leads")));AggregateIterable<Document> iterable = collection.aggregate(aggregates);for (Document row : iterable) {    System.out.println(row.toJson());}