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

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

MongoDB $ifNull conditional with mgo

问题

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

示例数据集 'users' 集合:

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

示例数据集 'newscounter' 集合:

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

在Mongo中的查询如下所示:

  1. db.users.aggregate([
  2.      { $geoNear: { 
  3.          near: { type: "Point", coordinates: [-21.861198,64.120877] },
  4.          distanceField: "distance",
  5.          maxDistance: myDistance * 1000,
  6.          spherical: true }
  7.      },
  8.     {
  9.         $sort: { "distance": 1 }
  10.     },
  11.     {
  12.      $lookup: {
  13.         from: "newscounter",
  14.         localField: "_id",
  15.         foreignField: "_id",
  16.         as: "news_count" }
  17.     },
  18.     {
  19.         $unwind: { path: "$news_count", preserveNullAndEmptyArrays: true }
  20.     },
  21.     {
  22.         $project : { 
  23.             "id": 1,
  24.             "username": 1,
  25.             "distance": 1,
  26.             "news_count": { $ifNull : ["$news_count.count", 0] }
  27.         }
  28.     }
  29. ])

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

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

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

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

我尝试了以下代码：

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

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

非常感谢任何帮助！

编辑 [已解决]：

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

为了完整起见，Go 中的管道如下所示：

  1. p := []bson.M{
  2.     bson.M{
  3.         "$geoNear": bson.M{
  4.             "near":          bson.M{"type": "Point", "coordinates": center},
  5.             "distanceField": "distance",
  6.             "maxDistance":   maxDistance,
  7.             "spherical":     true,
  8.         },
  9.     },
  10.     bson.M{
  11.         "$sort": bson.M{
  12.             "distance": 1,
  13.         },
  14.     },
  15.     bson.M{
  16.         "$lookup": bson.M{
  17.             "from":         "newscount",
  18.             "localField":   "_id",
  19.             "foreignField": "_id",
  20.             "as":           "news_count",
  21.         },
  22.     },
  23.     bson.M{
  24.         "$unwind": bson.M{
  25.             "path":                         "$news_count",
  26.             "preserveNullAndEmptyArrays":   true,
  27.         },
  28.     },
  29.     bson.M{
  30.         "$project": bson.M{
  31.             "_id":        1,
  32.             "username":  1,
  33.             "distance":  1,
  34.             "news_count": bson.M{
  35.                 "$ifNull": []interface{}{"$news_count.count", 0.0},
  36.             },
  37.         },
  38.     },
  39. }

结果的结构体：

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

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:

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

Sample data 'newscounter' collection:

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

The query in mongo looks like this:

  1. db.users.aggregate([
  2.      { $geoNear: { 
  3.          near: { type: "Point", coordinates: [-21.861198,64.120877] },
  4.          distanceField: "distance",
  5.          maxDistance: myDistance * 1000,
  6.          spherical: true }
  7.      },
  8.     {
  9.         $sort: { "distance": 1 }
  10.     },
  11.     {
  12.      $lookup: {
  13.         from: "newscounter",
  14.         localField: "_id",
  15.         foreignField: "_id",
  16.         as: "news_count" }
  17.     },
  18.     {
  19.         $unwind: { path: "$news_count", preserveNullAndEmptyArrays: true }
  20.     },
  21.     {
  22.         $project : { 
  23.             "id": 1,
  24.             "username": 1,
  25.             "distance": 1,
  26.             "news_count": { $ifNull : ["$news_count.count", 0] }
  27.         }
  28.     }
  29. ])

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

  1. { "_id" : ObjectId("592400188d84961b7f34b0cd"), "username" : "randomUser2", "distance" : 123, "news_count" : 14 }
  2. { "_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:

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

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:

  1. p := []bson.M{
  2. 		bson.M{
  3. 			"$geoNear": bson.M{
  4. 				"near":          bson.M{"type": "Point", "coordinates": center},
  5. 				"distanceField": "distance",
  6. 				"maxDistance":   maxDistance,
  7. 				"spherical":     true,
  8. 			},
  9. 		},
  10. 		bson.M{
  11. 			"$sort": bson.M{
  12. 				"distance": 1,
  13. 			},
  14. 		},
  15. 		bson.M{
  16. 			"$lookup": bson.M{
  17. 				"from":         "newscount",
  18. 				"localField":   "_id",
  19. 				"foreignField": "_id",
  20. 				"as":           "news_count",
  21. 			},
  22. 		},
  23. 		bson.M{
  24. 			"$unwind": bson.M{
  25. 				"path": "$news_count",
  26. 				"preserveNullAndEmptyArrays": true,
  27. 			},
  28. 		},
  29. 		bson.M{
  30. 			"$project": bson.M{
  31. 				"_id":      1,
  32. 				"username": 1,
  33. 				"distance": 1,
  34. 				"news_count": bson.M{
  35. 					"$ifNull": []interface{}{"$news_count.count", 0.0},
  36. 				},
  37. 			},
  38. 		},
  39. 	}

The result struct:

  1. type Result struct {
  2.           ID        bson.ObjectId  `json:"id" bson:"_id"`
  3.           Username  string         `json:"username" bson:"username"`
  4.           Distance  int64          `json:"distance" bson:"distance"`
  5.           NewsCount int64          `json:"news_count" bson:"news_count"`
  6.       }

答案1

得分: 0

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

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

  1. cu := sess.DB("").C("users")
  2. cnc := sess.DB("").C("newscounter")
  4. he := func(err error) {
  5. 	if err != nil {
  6. 		panic(err)
  7. 	}
  8. }
  10. he(cu.Insert(
  11. 	bson.M{
  12. 		"_id":      bson.ObjectIdHex("592400188d84961b7f34b0ce"),
  13. 		"username": "randomuser1",
  14. 		"location": bson.M{
  15. 			"type":        "Point",
  16. 			"coordinates": []interface{}{-17.634135, 65.705665},
  17. 		},
  18. 	},
  19. 	bson.M{
  20. 		"_id":      bson.ObjectIdHex("592400188d84961b7f34b0cd"),
  21. 		"username": "randomuser2",
  22. 		"location": bson.M{
  23. 			"type":        "Point",
  24. 			"coordinates": []interface{}{-17.282573, 63.755657},
  25. 		},
  26. 	},
  27. ))
  28. he(cnc.Insert(
  29. 	bson.M{
  30. 		"_id":   bson.ObjectIdHex("592400188d84961b7f34b0cd"),
  31. 		"count": 14,
  32. 	},
  33. ))
  35. pipe := cu.Pipe([]bson.M{
  36. 	{
  37. 		"$geoNear": bson.M{
  38. 			"near": bson.M{
  39. 				"type":        "Point",
  40. 				"coordinates": []interface{}{-21.861198, 64.120877},
  41. 			},
  42. 			"distanceField": "distance",
  43. 			"maxDistance":   123456789,
  44. 			"spherical":     true,
  45. 		},
  46. 	},
  47. 	{
  48. 		"$sort": bson.M{"distance": 1},
  49. 	},
  50. 	{
  51. 		"$lookup": bson.M{
  52. 			"from":         "newscounter",
  53. 			"localField":   "_id",
  54. 			"foreignField": "_id",
  55. 			"as":           "news_count",
  56. 		},
  57. 	},
  58. 	{
  59. 		"$unwind": bson.M{
  60. 			"path":                        "$news_count",
  61. 			"preserveNullAndEmptyArrays": true,
  62. 		},
  63. 	},
  64. 	{
  65. 		"$project": bson.M{
  66. 			"id":         1,
  67. 			"username":   1,
  68. 			"distance":   1,
  69. 			"news_count": bson.M{
  70. 				"$ifNull": []interface{}{"$news_count.count", 0},
  71. 			},
  72. 		},
  73. 	},
  74. })
  76. it := pipe.Iter()
  78. fmt.Println()
  79. m := bson.M{}
  80. for it.Next(&m) {
  81. 	fmt.Println(m)
  82. 	fmt.Println()
  83. }
  84. he(it.Err())

输出：

  1. map[_id:ObjectIdHex("592400188d84961b7f34b0cd") username:randomuser2 distance:227534.08191011765 news_count:14]
  2. 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:

  1. cu := sess.DB("").C("users")
  2. cnc := sess.DB("").C("newscounter")
  3. he := func(err error) {
  4. if err != nil {
  5. panic(err)
  6. }
  7. }
  8. he(cu.Insert(
  9. bson.M{
  10. "_id":      bson.ObjectIdHex("592400188d84961b7f34b0ce"),
  11. "username": "randomuser1",
  12. "location": bson.M{
  13. "type":        "Point",
  14. "coordinates": []interface{}{-17.634135, 65.705665},
  15. },
  16. },
  17. bson.M{
  18. "_id":      bson.ObjectIdHex("592400188d84961b7f34b0cd"),
  19. "username": "randomuser2",
  20. "location": bson.M{
  21. "type":        "Point",
  22. "coordinates": []interface{}{-17.282573, 63.755657},
  23. },
  24. },
  25. ))
  26. he(cnc.Insert(
  27. bson.M{
  28. "_id":   bson.ObjectIdHex("592400188d84961b7f34b0cd"),
  29. "count": 14,
  30. },
  31. ))
  32. pipe := cu.Pipe([]bson.M{
  33. {
  34. "$geoNear": bson.M{
  35. "near": bson.M{
  36. "type":        "Point",
  37. "coordinates": []interface{}{-21.861198, 64.120877},
  38. },
  39. "distanceField": "distance",
  40. "maxDistance":   123456789,
  41. "spherical":     true,
  42. },
  43. },
  44. {
  45. "$sort": bson.M{"distance": 1},
  46. },
  47. {
  48. "$lookup": bson.M{
  49. "from":         "newscounter",
  50. "localField":   "_id",
  51. "foreignField": "_id",
  52. "as":           "news_count",
  53. },
  54. },
  55. {
  56. "$unwind": bson.M{
  57. "path": "$news_count",
  58. "preserveNullAndEmptyArrays": true,
  59. },
  60. },
  61. {
  62. "$project": bson.M{
  63. "id":       1,
  64. "username": 1,
  65. "distance": 1,
  66. "news_count": bson.M{
  67. "$ifNull": []interface{}{"$news_count.count", 0},
  68. },
  69. },
  70. },
  71. })
  72. it := pipe.Iter()
  73. fmt.Println()
  74. m := bson.M{}
  75. for it.Next(&m) {
  76. fmt.Println(m)
  77. fmt.Println()
  78. }
  79. he(it.Err())

Output:

  1. map[_id:ObjectIdHex("592400188d84961b7f34b0cd") username:randomuser2 distance:227534.08191011765 news_count:14]
  2. 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
  • aggregation-framework
  • go
  • mgo
  • mongodb
匿名

发表评论

匿名网友

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

确定