基于ObjectId字段在MongoDB两集合间执行$lookup查询的问题

Hey there, let's work through this issue step by step to fix your syntax error and get the query working as expected with your Go stack.

一、语法错误的核心原因 #

Your aggregation statement has 3 clear syntax issues that caused the SyntaxError: missing } after property list error:

• The collection name requests in from:requests needs to be wrapped in English double quotes — MongoDB requires the from value to be a string
• The quote in as:”results” is a full-width Chinese quote; replace it with a standard half-width English quote ". Also, it's better to rename the alias to something like request_details to avoid confusion with the results collection itself
• While not a syntax error, for better performance, you should filter the target data with $match first before running $lookup — this reduces the amount of data that needs to be joined

二、修正后的Mongo Shell聚合语句 #

Matching your SQL logic, here's the corrected aggregation query:

db.results.aggregate([
  // First filter results to only the target request ID (boosts performance)
  {$match: { "id_request": ObjectId("5afc034f53c9a77a598c8345") }},
  // Join with the requests collection
  {$lookup: {
      from: "requests",
      localField: "id_request",
      foreignField: "_id",
      as: "request_details"
  }}
]);

This query first fetches all results documents linked to your specified request ID, then joins them with the corresponding requests documents. Each result will include a request_details array (even if only one request is linked, $lookup returns an array).

三、Go mgo Driver Implementation #

Here's how to implement this query with Go 1.9.2 and the mgo driver, including struct definitions for your collections:

package main

import (
	"fmt"
	"gopkg.in/mgo.v2"
	"gopkg.in/mgo.v2/bson"
)

// Results maps to the documents in your results collection
type Results struct {
	ID              bson.ObjectId `bson:"_id"`
	IDRequest       bson.ObjectId `bson:"id_request"`
	// Add your other results fields here...
	RequestDetails  []Request     `bson:"request_details"` // Holds joined request data
}

// Request maps to the documents in your requests collection
type Request struct {
	ID bson.ObjectId `bson:"_id"`
	// Add your other requests fields here...
}

func main() {
	// Connect to your MongoDB instance
	session, err := mgo.Dial("mongodb://localhost:27017/your_database_name")
	if err != nil {
		panic(fmt.Sprintf("Failed to connect to MongoDB: %v", err))
	}
	defer session.Close()

	// Enforce strong consistency mode
	session.SetMode(mgo.Strong, true)

	// Get a handle to the results collection
	resultsCol := session.DB("your_database_name").C("results")

	// Convert the target request ID string to bson.ObjectId
	targetRequestID := bson.ObjectIdHex("5afc034f53c9a77a598c8345")

	var matchedResults []Results
	// Build the aggregation pipeline
	pipeline := []bson.M{
		bson.M{"$match": bson.M{"id_request": targetRequestID}},
		bson.M{
			"$lookup": bson.M{
				"from":         "requests",
				"localField":   "id_request",
				"foreignField": "_id",
				"as":           "request_details",
			},
		},
	}

	// Execute the aggregation query
	err = resultsCol.Pipe(pipeline).All(&matchedResults)
	if err != nil {
		panic(fmt.Sprintf("Aggregation query failed: %v", err))
	}

	// Print the results
	for _, res := range matchedResults {
		fmt.Printf("Result ID: %s | Linked Request ID: %s\n", res.ID.Hex(), res.IDRequest.Hex())
		fmt.Printf("Request Details: %+v\n\n", res.RequestDetails)
	}
}

四、Quick Note #

If you only need the results.* data (matching your original SQL's select results.*), you can skip the $lookup entirely and just run a simple $match query on the results collection. Use the join only when you need to fetch data from the requests collection alongside results.

内容的提问来源于stack exchange，提问作者Medo