mongodb aggregation lookup using ObjectId

Question

I am struggling with what seems to a basic lookup in mongodb. I can't figure out what is going wrong after trying multiple combinations. I see that there are many questions on SO addressing it, but nothing works after trying all the answers.

Here's my users collection

Here's my items collection

Each item has a single owner which maps to the user's _id in users collection. For my query, I am trying to fetch items with their corresponding users. This is the query

   db.items.aggregate([
    {
        "$lookup" : {
          localField : "owner",
          from : "users",
          foreignField : "_id",
          as : "users"
        }
    }
])

It returns this -

I have tried different variations - such as

db.items.aggregate([
    {
        "$lookup" : {
          localField : "owner.str",
          from : "users",
          foreignField : "_id.str",
          as : "users"
        }
    }
])

which results in the array of users getting populated incorrectly (with all users!).

What am I doing wrong?

EDIT

here's the items collection

{
  "_id": {
    "$oid": "5b268395c176db1548bd92c2"
  },
  "title": "Item #1",
  "description": "Item 1 Description",
  "categories": [
    {
      "$ref": "categories",
      "$id": {
        "$oid": "5b268248c176db1548bd92af"
      }
    }
  ],
  "status": "borrowed",
  "image": "http://cdn.site.com/testimage.png",
  "borrower": "5b2684a0c176db1548bd92d5",
  "owner": {
    "$ref": "users",
    "$id": {
      "$oid": "5aecc8012d3d947ba130800d"
    }
  }
}

{
  "_id": {
    "$oid": "5b2c2c4d3c70af2da07d1266"
  },
  "title": "Item 2",
  "description": "Item 2 Description",
  "categories": [
    {
      "$ref": "categories",
      "$id": {
        "$oid": "5b268248c176db1548bd92af"
      }
    }
  ],
  "status": "Available",
  "image": "http://cdn.site.com/testimage1.png",
  "borrower": null,
  "owner": {
    "$ref": "users",
    "$id": {
      "$oid": "5b2684a0c176db1548bd92d5"
    }
  }
}

Here's the users collection :

{
  "_id": {
    "$oid": "5aecc8012d3d947ba130800d"
  },
  "email": "n.y@gmail.com",
  "registeredOn": "20/10/2018 12:12:29 AM",
  "avatarURL": "http://gravtar.com/12334",
  "friends": []
}

{
  "_id": {
    "$oid": "5b2684a0c176db1548bd92d5"
  },
  "email": "j.v@gmail.com",
  "registeredOn": "20/10/2018 12:12:29 AM",
  "avatarURL": "http://gravtar.com/12334",
  "friends": [],
  "wishlist": [
    {
      "$ref": "items",
      "$id": {
        "$oid": "5b2831543c70af2da07d11da"
      }
    }
  ]
}

Dear Jay, you've been here for a while already, so you must be familiar with https://meta.stackoverflow.com/questions/303812/discourage-screenshots-of-code-and-or-errors and https://stackoverflow.com/help/mcve. Please post example **JSON** documents from both collections instead of screenshots. Ideally if you strip out non-essential fields. — Alex Blex, Jun 25 '18 at 16:07
@AlexBlex thanks - I presumed that I must be making a basic mistake with querying, and that someone familiar with mongo will be able to easily tell just by looking at the tree structure. Anyway, updating post with json examples. Now, stop stalking my profile ;-) — Jay, Jun 25 '18 at 17:06
There is nothing wrong with the query per se, but since you are not getting expected results, it's likely related to the data, particularly `owner` field. The `localField` and `foreignField` should agree with data type, and on your screenshots one is Reference and another is ObjectId. — Alex Blex, Jun 25 '18 at 17:13
@AlexBlex nice observation. I assumed that reference is of type ObjectId. My bad. Do I need to change owner field to type "ObjectId"? Or is there a way to compare reference and ObjectId types? — Jay, Jun 25 '18 at 17:18
If you can, yes. There are too many issues with DBRef comparing to advantages of using it: e.g. https://jira.mongodb.org/browse/SERVER-14466, and a dupe question here on SO: https://stackoverflow.com/questions/40622714 — Alex Blex, Jun 25 '18 at 17:22
@AlexBlex thank you, I just changed the type in items to "ObjectId" and the query worked just fine! Appreciate your help. Please post it as answer, I will accept it. — Jay, Jun 25 '18 at 17:23

score 5 · Accepted Answer · answered Jun 25 '18 at 17:30

5

The localField and foreignField should be of the same data type. Your are DBRef and ObjectId respectively.

answered Jun 25 '18 at 17:30

Alex Blex

34,704
7
48
75

2

Thank you, you saved my hair.. 2 more days of pulling my hair, I would have gone bald! – Jay Jun 25 '18 at 17:37

score 0 · Answer 2 · answered Jun 25 '18 at 16:08

If you want to populate a items record with the corresponding user, I think you can use the populate option in mongoose find API

Sample code:

this.read = function(query, populate, onSuccess, onFailure, forcePull, sortQuery, ttl, limitValue, selectQuery) {
        var scope = this;
        logger.info(this.TAG, "Read operation execution started on ORM Object: "
                    + modelName + " with query: " + JSON.stringify(query));
        if (query) {

            var queryObject = this._model.find(query);
            if (populate) {
              queryObject = queryObject.populate(populate);
            }
            if (selectQuery) {
              queryObject = queryObject.select(selectQuery);
            }
            if (sortQuery) {
              queryObject = queryObject.sort(sortQuery);
            }
            if (limitValue) {
              queryObject = queryObject.limit(limitValue);
            }
            queryObject.exec(function(error, records) {
                if (error) {
                    logger.error(scope.TAG, "Error while ORM read: " + JSON.stringify(error));
                    onFailure(error);
                    return;
                }
                logger.info(scope.TAG, "Record read successfully: " + JSON.stringify(records));
                onSuccess(records);
            });
          }
     }

You can pass populate as an array of strings like ['owner'] in your case

I am not using mongoose - I will be using java driver. Trying to get the query correct in mongo first using mongo shell and then move it to Java. — Jay, Jun 25 '18 at 17:04

mongodb aggregation lookup using ObjectId

2 Answers2