2

We have two collection one is status collection and another is users collection i am trying to perform lookup query on those collection but it does not return any result

status collection record: 

"status" : {
    "status" : "success",
    "start_dt2" : ISODate("2018-07-31T19:00:54.774Z"),
    "start_dt" : "2018-07-31 19:00:54",
    "reason" : "here is my report",
    "approved_by" : "5af203e4a19f2d1d854c2be7",
    "end_dt2" : ISODate("2018-08-02T19:26:05.891Z"),
    "end_dt" : "2018-08-02 19:26:05"
}

user collection record is following. 

{
  "_id" : ObjectId("5af203e4a19f2d1d854c2be7"),
  "username" : "administrator",
  "status" : 1,
  "failed_login_attempts" : 0,
  "user_password" : "",
  "user_role" : "Administrator",
  "force_change" : 0,
  "full_name" : "Administrator",
  "authentication_type" : "local",
  "email" : "",
  "last_login_dt" : ISODate("2019-05-07T13:30:50.926Z"),
  "contributors" : [],
  "expiration_dt" : null,
  "tags" : [],
  "is_system_administrator" : true,
  "is_shared_asset_manager" : true

}

Query:

db.status.aggregate([
{ "$match": { "type": 'manual_activity' } },
{ "$group": {"_id": "$status", "status": {$push: "$status"}}},
{ "$project": {"status": "$status", _id: 0}},   
{"$lookup": {"from": "users", "localField": "status.approved_by", 
"foreignField": "_id", "as": "username"}},
{ $sort: {count: 1} }
])

I want get username from user table instead of id in status collection.

Chirag Patel
  • 31
  • 3
  • What o/p you are getting, please add that also. – Abhishek Mani May 08 '19 at 04:52
  • which version of mongodb are you using? i am asking this because there is way in mongodb-4.0 where you can transform objectID to string and then perform $lookup. – Vikash_Singh May 08 '19 at 05:30
  • I am using mongodb 3.4.14 – Chirag Patel May 08 '19 at 14:33
  • Hi Abhishek here is output { "status" : [ { "status" : "success", "start_dt2" : ISODate("2018-09-05T19:29:51.199Z"), "end_dt2" : ISODate("2018-09-05T19:30:06.255Z"), "reason" : "approved", "approved_by" : "5af203e4a19f2d1d854c2be7", "start_dt" : "2018-09-05 19:29:51", "end_dt" : "2018-09-05 19:30:06" } ], "username" : [] } – Chirag Patel May 08 '19 at 14:36

0 Answers0