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

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")');

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") } } 
]);

If you add two extra steps to your pipeline, $unwind and $project, you should get the results you want. I would also do the $match before the $lookup, you might as well only lookup the data you need.

I am also a fan of making your pipeline query easy to read by defining separate queries and combining them in the pipeline call. You asked this question as a Go question, so a Go way of doing it is:

match := bson.M{"$match": bson.M{"id_request": bson.ObjectIdHex("5afc034f53c9a77a598c8345")}}
lookup := bson.M{"$lookup": bson.M{"from": "requests", "localField": "id_request", "foreignField": "_id", "as": "results"}}
unwind := bson.M{"$unwind": "$results"}
project := bson.M{"$project": bson.M{"_id": 0, "name": 1, "job": 1, "age": 1, "city": 1}}

iter := theCollection.Pipe([]bson.M{match, lookup, unwind, project}).Iter()
answer := bson.M{}
for iter.Next(&answer) {
    fmt.Println(answer)
}
iter.Close()