在ObjectId字段的两个集合之间进行$ lookup查询

Question

I'am working on an api in Go 1.9.2 with mongoDB 3.4 I am using mgo as a driver.

I have to do an endpoint for getting the results of the requests, after inserting the request and its results in the same database with two collections.

Having two collections:

First Collection : requests

Second Collection : results

The requests collection has the format

{ "_id":ObjectId("5afc034f53c9a77a598c8345")
  "  time ":"2018-05-16 10:08:35.024352907 +0000 UTC m=+23.407317980"
  "param_request":[name:"mike",age:"30",job:"Doctor"]
}

The results collection has the format the id_request field is the _id field for the request document (as the foreign key philosophy in SQL)

{"_id":ObjectId("5afc035b53c9a77a598c8346")
 "id_request":ObjectId("5afc034f53c9a77a598c8345")
 "name":"Mike"
 "age":"30"
 "job":"Doctor"
 "city":"Tokyo"} 

{"_id":ObjectId("5afc035b53c9a77a598c8347")
 "id_request":ObjectId("5afc034f53c9a77a598c8345")
 "name":"Mike"
 "age":"30"
 "job":"Doctor"
 "city":"London"}


 {"_id":ObjectId("5afc035b53c9a77a598c8349")
 "id_request":ObjectId("5afc034f53c9a77a598c8345")
 "name":"Mike"
 "age":"30"
 "job":"Doctor"
 "city":"Berlin"
 } 

I tried to query and I found the lookup from the documentation $lookup Documentation

the result wanted :

 {
 "name":"Mike"
 "age":"30"
 "job":"Doctor"
 "city":"Berlin"

 }

 {
 "name":"Mike"
 "age":"30"
 "job":"Doctor"
 "city":"London"

 }

 {
 "name":"Mike"
 "age":"30"
 "job":"Doctor"
 "city":"Tokyo"

}

Here is what I did:

 db.results.aggregate([

  {$lookup: {from:requests, localField: "id_request",foreignField:"_id",as:”results”}},
  {$match:
         { 
      "id_request": ObjectId("5afc034f53c9a77a598c8345") }}]);

here is the error getting :

2018-05-16T11:31:51.261+0000 E QUERY [thread1] SyntaxError: missing } after property list @(shell):1:131

Here is what I have to get with the SQL philosophy as a query:

  select results .* from results join requests on 

  (results.request_id=requests._id 

               and 

    request_id='ObjectId("5afc034f53c9a77a598c8345")');

Answer 1

The query in your question has multiple issues which may cause parsing errors. The from property in the lookup stage must be a string. Also, the quote character that you used for the as property (”) is not a valid string delimiter.

Try to replace the from: requests with from: "requests" and the ” character with a single or double quote.

db.results.aggregate([
   { $lookup: { from: "requests", localField: "id_request", foreignField: "_id", as: "results" } },
   { $match: { "id_request": ObjectId("5afc034f53c9a77a598c8345") } } 
]);