MongoDB $lookup query returning empty field

Question

There are two collections in my database, Projects and Tasks. Each project can have a number of tasks associated to it. I am trying to create a MongoDB query in Java that will return a Task and also embed the Project that is linked to it, however the code I have completed so far only returns an empty array (I have named this something which appears as [] below).

Unfortunately I have not found many examples on how to properly use $lookup in Java. What changes do I need to perform on the code so the something field comes back with a project? Should I be using $lookup or another aggregation operator?

The Java method I am using with mongo-java-driver version 3.5.0:

public static String readTask()
{
    MongoClient mongoClient = new MongoClient();
    MongoDatabase database = mongoClient.getDatabase("exampleDatabase");

    Bson lookup = new Document("$lookup",
            new Document("from", "Project")
                    .append("localField", "project._id")
                    .append("foreignField", "_id")
                    .append("as", "something"));

    List<Bson> filters = new ArrayList<>();
    filters.add(lookup);

    AggregateIterable<Document> it = database.getCollection("Task").aggregate(filters);
    System.out.println("First Document: " + it.first().toString());

    return it.first().toString();
}

This method currently returns the following:

{
    _id = 599a62cac29d9a2684c64012,
    constructionRoomNumber = 15,
    type = Example,
    summary = Summary Text,
    description = Description Text,
    status = Open,
    project = {
        "$ref": "Project",
        "$id": "5996582a0983347784fb2ff4"
    },
    something = []
}

Expected Result:

{
    _id: ObjectId('599a62cac29d9a2684c64012')
    constructionRoomNumber: "15"
    type: "Example"
    summary: "Summary Text"
    description: "Description Text"
    status: "Open"
    project: {
        _id: ObjectId('5996582a0983347784fb2ff4')
        projectCode: "V1000"
        projectName: "Example Project"
        projectLocation: "1 Somewhere Street, Some City"
        clientName: "Whatever Client"
        isActive: true
    }
}

Here is an example of what the data stored in MongoDB looks like:

Example Project:

_id: ObjectId('5996582a0983347784fb2ff4')
projectCode: "V1000"
projectName: "Example Project"
projectLocation: "1 Somewhere Street, Some City"
clientName: "Whatever Client"
isActive: true

Example Task:

_id: ObjectId('599a62cac29d9a2684c64012')
constructionRoomNumber: "15"
type: "Example"
summary: "Summary Text"
description: "Description Text"
status: "Open"
project: DBRef(Project, 5996582a0983347784fb2ff4, undefined)

According to https://stackoverflow.com/a/41677055/7717350 DBRef doesn't supported in the `$lookup` phase. — mgyongyosi, Aug 22 '17 at 11:16
This is not entirely accurate, as there is way to get ObjectIds from DBRefs objects,see my answer — Olivier Maurel, Apr 10 '18 at 14:10

score 0 · Answer 1 · answered Apr 10 '18 at 14:09

0

You are trying a lookup on a DBRef field, which is not supported.

However, there is a workaround to transform DBRefs into simple ObjectId that I have detailed on a different answer, you can adapt it in Java or use it in a native query.

answered Apr 10 '18 at 14:09

Olivier Maurel

476
4
16

MongoDB $lookup query returning empty field

1 Answers1