MongoDB $ifNull条件语句与mgo的使用

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

MongoDB $ifNull conditional with mgo

问题

我正在努力将一个从Mongo控制台转移到我的Go代码中的查询进行移植。
我对MongoDB还不熟悉,所以可能还有其他我没有考虑到的错误。

示例数据集 'users' 集合:

{ "id" : ObjectId("592400188d84961b7f34b0cd"), "username" : "randomUser2", "location" : { "type" : "Point", "coordinates" : [ -17.282573, 63.755657 ] } }
{ "id" : ObjectId("592400188d84961b7f34b0ce"), "username" : "randomUser1", "location" : { "type" : "Point", "coordinates" : [ -17.634135, 65.705665 ] } }

示例数据集 'newscounter' 集合:

{ "id" : ObjectId("592400188d84961b7f34b0cd"), "count" : 14 }

在Mongo中的查询如下所示:

db.users.aggregate([
     { $geoNear: { 
         near: { type: "Point", coordinates: [-21.861198,64.120877] },
         distanceField: "distance",
         maxDistance: myDistance * 1000,
         spherical: true }
     },
    {
        $sort: { "distance": 1 }
    },
    {
     $lookup: {
        from: "newscounter",
        localField: "_id",
        foreignField: "_id",
        as: "news_count" }
    },
    {
        $unwind: { path: "$news_count", preserveNullAndEmptyArrays: true }
    },
    {
        $project : { 
            "id": 1,
            "username": 1,
            "distance": 1,
            "news_count": { $ifNull : ["$news_count.count", 0] }
        }
    }
])

输出结果如下所示(这里我使用了计算得到的距离字段的随机值):

{ "id" : ObjectId("592400188d84961b7f34b0cd"), "username" : "randomUser2", "distance" : 123, "news_count" : 14 }
{ "id" : ObjectId("592400188d84961b7f34b0ce"), "username" : "randomUser1", "distance" : 456, "news_count" : 0 }

我遇到困难的部分是 $project 阶段中的 $ifNull。

如何在使用 mgo 包的 Go 代码中构建 $ifNull 行?

我尝试了以下代码:

"news_count": bson.M{
    "$ifNull": [2]interface{}{"$news_count.count", 0},
}

但它总是返回 news_count 字段的空字符串。

非常感谢任何帮助!

编辑 [已解决]:

问题很愚蠢,我在 Go 的结构体中将 news_count 字段的 type 设置错误。

为了完整起见,Go 中的管道如下所示:

p := []bson.M{
    bson.M{
        "$geoNear": bson.M{
            "near":          bson.M{"type": "Point", "coordinates": center},
            "distanceField": "distance",
            "maxDistance":   maxDistance,
            "spherical":     true,
        },
    },
    bson.M{
        "$sort": bson.M{
            "distance": 1,
        },
    },
    bson.M{
        "$lookup": bson.M{
            "from":         "newscount",
            "localField":   "_id",
            "foreignField": "_id",
            "as":           "news_count",
        },
    },
    bson.M{
        "$unwind": bson.M{
            "path":                         "$news_count",
            "preserveNullAndEmptyArrays":   true,
        },
    },
    bson.M{
        "$project": bson.M{
            "_id":        1,
            "username":  1,
            "distance":  1,
            "news_count": bson.M{
                "$ifNull": []interface{}{"$news_count.count", 0.0},
            },
        },
    },
}

结果的结构体:

type Result struct {
    ID        bson.ObjectId `json:"id" bson:"_id"`
    Username  string        `json:"username" bson:"username"`
    Distance  int64         `json:"distance" bson:"distance"`
    NewsCount int64         `json:"news_count" bson:"news_count"`
}
英文:

I'm struggling porting a query from the mongo console to my Go code.
I'm new to MongoDB so there might be additional mistakes I haven't taken into account.

Sample data 'users' collection:

{ "_id" : ObjectId("592400188d84961b7f34b0cd"), "username" : "randomUser2", "location" : { "type" : "Point", "coordinates" : [ -17.282573, 63.755657 ] } }
{ "_id" : ObjectId("592400188d84961b7f34b0ce"), "username" : "randomUser1", "location" : { "type" : "Point", "coordinates" : [ -17.634135, 65.705665 ] } }

Sample data 'newscounter' collection:

{ "_id" : ObjectId("592400188d84961b7f34b0cd"), "count" : 14 }

The query in mongo looks like this:

db.users.aggregate([
     { $geoNear: { 
         near: { type: "Point", coordinates: [-21.861198,64.120877] },
         distanceField: "distance",
         maxDistance: myDistance * 1000,
         spherical: true }
     },
    {
        $sort: { "distance": 1 }
    },
    {
     $lookup: {
        from: "newscounter",
        localField: "_id",
        foreignField: "_id",
        as: "news_count" }
    },
    {
        $unwind: { path: "$news_count", preserveNullAndEmptyArrays: true }
    },
    {
        $project : { 
            "id": 1,
            "username": 1,
            "distance": 1,
            "news_count": { $ifNull : ["$news_count.count", 0] }
        }
    }
])

The output is (I used random values for the calculated distance field here):

{ "_id" : ObjectId("592400188d84961b7f34b0cd"), "username" : "randomUser2", "distance" : 123, "news_count" : 14 }
{ "_id" : ObjectId("592400188d84961b7f34b0ce"), "username" : "randomUser1", "distance" : 456, "news_count" : 0 }

The part I've trouble with is the $ifNull in the $project stage.

How do I build the $ifNull line in Go using the mgo package?

I tried with:

"news_count": bson.M{
    "$ifNull": [2]interface{}{"$news_count.count", 0},
}

but it returns always an empty string for the news_count field.

Any help is greatly appreciated!

EDIT [solved]:

The issue was silly, I had the wrong type for the news_count field in the Go struct.

For the sake of completeness the pipeline in Go is:

p := []bson.M{
		bson.M{
			"$geoNear": bson.M{
				"near":          bson.M{"type": "Point", "coordinates": center},
				"distanceField": "distance",
				"maxDistance":   maxDistance,
				"spherical":     true,
			},
		},
		bson.M{
			"$sort": bson.M{
				"distance": 1,
			},
		},
		bson.M{
			"$lookup": bson.M{
				"from":         "newscount",
				"localField":   "_id",
				"foreignField": "_id",
				"as":           "news_count",
			},
		},
		bson.M{
			"$unwind": bson.M{
				"path": "$news_count",
				"preserveNullAndEmptyArrays": true,
			},
		},
		bson.M{
			"$project": bson.M{
				"_id":      1,
				"username": 1,
				"distance": 1,
				"news_count": bson.M{
					"$ifNull": []interface{}{"$news_count.count", 0.0},
				},
			},
		},
	}

The result struct:

type Result struct {
          ID        bson.ObjectId  `json:"id" bson:"_id"`
          Username  string         `json:"username" bson:"username"`
          Distance  int64          `json:"distance" bson:"distance"`
          NewsCount int64          `json:"news_count" bson:"news_count"`
      }

答案1

得分: 0

你的news_count投影工作正常,错误可能出现在你没有发布的代码的其他地方。

以下是一个完整的、可工作的示例:

cu := sess.DB("").C("users")
cnc := sess.DB("").C("newscounter")

he := func(err error) {
	if err != nil {
		panic(err)
	}
}

he(cu.Insert(
	bson.M{
		"_id":      bson.ObjectIdHex("592400188d84961b7f34b0ce"),
		"username": "randomuser1",
		"location": bson.M{
			"type":        "Point",
			"coordinates": []interface{}{-17.634135, 65.705665},
		},
	},
	bson.M{
		"_id":      bson.ObjectIdHex("592400188d84961b7f34b0cd"),
		"username": "randomuser2",
		"location": bson.M{
			"type":        "Point",
			"coordinates": []interface{}{-17.282573, 63.755657},
		},
	},
))
he(cnc.Insert(
	bson.M{
		"_id":   bson.ObjectIdHex("592400188d84961b7f34b0cd"),
		"count": 14,
	},
))

pipe := cu.Pipe([]bson.M{
	{
		"$geoNear": bson.M{
			"near": bson.M{
				"type":        "Point",
				"coordinates": []interface{}{-21.861198, 64.120877},
			},
			"distanceField": "distance",
			"maxDistance":   123456789,
			"spherical":     true,
		},
	},
	{
		"$sort": bson.M{"distance": 1},
	},
	{
		"$lookup": bson.M{
			"from":         "newscounter",
			"localField":   "_id",
			"foreignField": "_id",
			"as":           "news_count",
		},
	},
	{
		"$unwind": bson.M{
			"path":                        "$news_count",
			"preserveNullAndEmptyArrays": true,
		},
	},
	{
		"$project": bson.M{
			"id":         1,
			"username":   1,
			"distance":   1,
			"news_count": bson.M{
				"$ifNull": []interface{}{"$news_count.count", 0},
			},
		},
	},
})

it := pipe.Iter()

fmt.Println()
m := bson.M{}
for it.Next(&m) {
	fmt.Println(m)
	fmt.Println()
}
he(it.Err())

输出:

map[_id:ObjectIdHex("592400188d84961b7f34b0cd") username:randomuser2 distance:227534.08191011765 news_count:14]
map[username:randomuser1 distance:266222.98643136176 news_count:0 _id:ObjectIdHex("592400188d84961b7f34b0ce")]

希望对你有帮助!

英文:

Your news_count projection works, the error is somewhere else in the code which you haven't posted.

See this full, working example:

cu := sess.DB("").C("users")
cnc := sess.DB("").C("newscounter")
he := func(err error) {
if err != nil {
panic(err)
}
}
he(cu.Insert(
bson.M{
"_id":      bson.ObjectIdHex("592400188d84961b7f34b0ce"),
"username": "randomuser1",
"location": bson.M{
"type":        "Point",
"coordinates": []interface{}{-17.634135, 65.705665},
},
},
bson.M{
"_id":      bson.ObjectIdHex("592400188d84961b7f34b0cd"),
"username": "randomuser2",
"location": bson.M{
"type":        "Point",
"coordinates": []interface{}{-17.282573, 63.755657},
},
},
))
he(cnc.Insert(
bson.M{
"_id":   bson.ObjectIdHex("592400188d84961b7f34b0cd"),
"count": 14,
},
))
pipe := cu.Pipe([]bson.M{
{
"$geoNear": bson.M{
"near": bson.M{
"type":        "Point",
"coordinates": []interface{}{-21.861198, 64.120877},
},
"distanceField": "distance",
"maxDistance":   123456789,
"spherical":     true,
},
},
{
"$sort": bson.M{"distance": 1},
},
{
"$lookup": bson.M{
"from":         "newscounter",
"localField":   "_id",
"foreignField": "_id",
"as":           "news_count",
},
},
{
"$unwind": bson.M{
"path": "$news_count",
"preserveNullAndEmptyArrays": true,
},
},
{
"$project": bson.M{
"id":       1,
"username": 1,
"distance": 1,
"news_count": bson.M{
"$ifNull": []interface{}{"$news_count.count", 0},
},
},
},
})
it := pipe.Iter()
fmt.Println()
m := bson.M{}
for it.Next(&m) {
fmt.Println(m)
fmt.Println()
}
he(it.Err())

Output:

map[_id:ObjectIdHex("592400188d84961b7f34b0cd") username:randomuser2 distance:227534.08191011765 news_count:14]
map[username:randomuser1 distance:266222.98643136176 news_count:0 _id:ObjectIdHex("592400188d84961b7f34b0ce")]

huangapple
  • 本文由 发表于 2017年5月24日 18:15:33
  • 转载请务必保留本文链接:https://go.coder-hub.com/44155465.html
匿名

发表评论

匿名网友

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

确定