在shell中使用$lookup查询可以正常工作,但在代码中却不行。

huangapple go评论78阅读模式
英文:

Query using $lookup works in shell, but not in the code

问题

我正在使用golang和mongo db作为后端。我有一个transactions集合,其结构如下:

{ "_id" : ObjectId("63d91a96940d4d147b32182a"), "category" : ObjectId("63caf0bccdb235dda38b4859"), "amount" : "1000", "owner" : ObjectId("63d3c9957bc044d051e01021"), "invdt" : ISODate("2022-10-11T00:00:00Z"), "date" : "2022-10-11" }

我需要根据对应的_id从category集合中插入数据。以下查询在mongo shell中可以正常工作,但在代码中不起作用:

db.transactions.aggregate([{$lookup:{from:"categories",localField:"category",foreignField:"_id",as:"cat"}}])

在代码中,我尝试使用以下代码获取数据:

type Transaction struct {
    ID       primitive.ObjectID `json:"id" bson:"_id"`
    Category primitive.ObjectID `bson:"category,omitempty" json:"category"`
    Amount   string             `json:"amount" binding:"required"`
    Owner    primitive.ObjectID `bson:"owner,omitempty" json:"owner"`
    InvDt    primitive.DateTime `bson:"invdt,omitempty" json:"invdt,omitempty"`
    Date     string             `json:"date" binding:"required"`
}

pipeline := mongo.Pipeline{
    {{"$match", bson.D{
        {"owner", bson.D{
            {"$eq", user.ID},
        }},
    }}},
    {{"$lookup", bson.D{
        {"from", "categories"},
        {"localField", "category"},
        {"foreignField", "_id"},
        {"as", "cat"},
    }}},
}
cur, err := handler.collection.Aggregate(handler.ctx, pipeline)
defer cur.Close(handler.ctx)
transactions := make([]models.Transaction, 0)

for cur.Next(handler.ctx) {
    var transaction models.Transaction
    cur.Decode(&transaction)
    transactions = append(transactions, transaction)
}

c.JSON(http.StatusOK, transactions)

我尝试过去掉$match,但没有什么帮助。由于某种原因$lookup不起作用。

在mongo shell中查询的结果如下:

{ "_id" : ObjectId("63d919f71ee94b0bdfea593a"), "category" : ObjectId("63caf0bccdb235dda38b4859"), "amount" : "1000", "owner" : ObjectId("63d3c9957bc044d051e01021"), "invdt" : ISODate("2022-10-11T00:00:00Z"), "date" : "2022-10-11", "cat" : [ { "_id" : ObjectId("63caf0bccdb235dda38b4859"), "name" : "Food2", "type" : "Expense", "owner" : ObjectId("63caf08ccdb235dda38b4857") } ] }
{ "_id" : ObjectId("63d91a96940d4d147b32182a"), "category" : ObjectId("63caf0bccdb235dda38b4859"), "amount" : "1000", "owner" : ObjectId("63d3c9957bc044d051e01021"), "invdt" : ISODate("2022-10-11T00:00:00Z"), "date" : "2022-10-11", "cat" : [ { "_id" : ObjectId("63caf0bccdb235dda38b4859"), "name" : "Food2", "type" : "Expense", "owner" : ObjectId("63caf08ccdb235dda38b4857") } ] }

这里出现了一个新的字段cat,其中包含了从categories集合中填充的数据。但在Postman请求中,我没有得到这个字段。

英文:

I am using golang and mongo db for my backend.
I have transactions collection, which has the following structure.

{ "_id" : ObjectId("63d91a96940d4d147b32182a"), "category" : ObjectId("63caf0bccdb235dda38b4859"), "amount" : "1000", "owner" : ObjectId("63d3c9957bc044d051e01021"), "invdt" : ISODate("2022-10-11T00:00:00Z"), "date" : "2022-10-11" }

I need to insert data from category collection by corresponding _id.
The following query works properly in the mongo shell, but not in the code itself

db.transactions.aggregate([{$lookup:{from:"categories",localField:"category",foreignField:"_id",as:"cat"}}])

In the code, i try to get data by the following code

type Transaction struct {
	ID       primitive.ObjectID `json:"id" bson:"_id"`
	Category primitive.ObjectID `bson:"category,omitempty" json:"category"`
	Amount   string             `json:"amount" binding:"required"`
	Owner    primitive.ObjectID `bson:"owner,omitempty" json:"owner"`
	InvDt    primitive.DateTime `bson:"invdt,omitempty" json:"invdt,omitempty"`
	Date     string             `json:"date" binding:"required"`
}

pipeline := mongo.Pipeline{
    {{"$match", bson.D{
	  {"owner", bson.D{
	  {"$eq", user.ID},},},
		}}},
		{{"$lookup", bson.D{
			{"from", "categories"},
			{"localField", "category"},
			{"foreignField", "_id"},
			{"as", "cat"},
		}}},
	}
    cur, err := handler.collection.Aggregate(handler.ctx, pipeline)
    defer cur.Close(handler.ctx)
	transactions := make([]models.Transaction, 0)

	for cur.Next(handler.ctx) {
		var transaction models.Transaction
		cur.Decode(&transaction)
		transactions = append(transactions, transaction)
	}


c.JSON(http.StatusOK, transactions)

I've tried without $match, but it doesn't help much.
For some reason $lookup doesn't work

Result from query in the mongo shell

{ "_id" : ObjectId("63d919f71ee94b0bdfea593a"), "category" : ObjectId("63caf0bccdb235dda38b4859"), "amount" : "1000", "owner" : ObjectId("63d3c9957bc044d051e01021"), "invdt" : ISODate("2022-10-11T00:00:00Z"), "date" : "2022-10-11", "cat" : [ { "_id" : ObjectId("63caf0bccdb235dda38b4859"), "name" : "Food2", "type" : "Expense", "owner" : ObjectId("63caf08ccdb235dda38b4857") } ] }
{ "_id" : ObjectId("63d91a96940d4d147b32182a"), "category" : ObjectId("63caf0bccdb235dda38b4859"), "amount" : "1000", "owner" : ObjectId("63d3c9957bc044d051e01021"), "invdt" : ISODate("2022-10-11T00:00:00Z"), "date" : "2022-10-11", "cat" : [ { "_id" : ObjectId("63caf0bccdb235dda38b4859"), "name" : "Food2", "type" : "Expense", "owner" : ObjectId("63caf08ccdb235dda38b4857") } ] }

Here appears a new field cat with populated data from categories collection.
In the request from the postman, I don't get this field

答案1

得分: 1

问题出在结构体Transaction中。
没有相应的字段

Cat []map[string]interface{} `json:"cat" bson:"cat"`

type Transaction struct {
    ID       primitive.ObjectID       `json:"id" bson:"_id"`
    Category primitive.ObjectID       `bson:"category,omitempty" json:"category"`
    Amount   string                   `json:"amount" binding:"required"`
    Owner    primitive.ObjectID       `bson:"owner,omitempty" json:"owner"`
    InvDt    primitive.DateTime       `bson:"invdt,omitempty" json:"invdt,omitempty"`
    Date     string                   `json:"date" binding:"required"`
    Cat      []map[string]interface{} `json:"cat" bson:"cat"`
}
英文:

The problem was in the struct Transaction.
There was no corresponding field

Cat []map[string]interface{} `json:"cat" bson:"cat"`

type Transaction struct {
	ID       primitive.ObjectID       `json:"id" bson:"_id"`
	Category primitive.ObjectID       `bson:"category,omitempty" json:"category"`
	Amount   string                   `json:"amount" binding:"required"`
	Owner    primitive.ObjectID       `bson:"owner,omitempty" json:"owner"`
	InvDt    primitive.DateTime       `bson:"invdt,omitempty" json:"invdt,omitempty"`
	Date     string                   `json:"date" binding:"required"`
	Cat      []map[string]interface{} `json:"cat" bson:"cat"`
}

huangapple
  • 本文由 发表于 2023年1月31日 22:36:46
  • 转载请务必保留本文链接:https://go.coder-hub.com/75298750.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定